4 ms·

Queries. Excel isn't a database but a lot of people use it like one anyways.

by rms 18y ago

pingswept 18y ago
Just the ability to represent one-to-many relationships in a native way (without resorting to hiding rows or the like) would be a huge step in that direction. The goal could be: make Excel handle more of the borderline cases where you really should be using a simple database, but all you know is Excel.
notauser 18y ago
You can do a lot of stuff that overlaps with queries with just array formulas, and a lot of the rest with pivot tables. For example, getting the sum bill of every person who lives in England looks like this in one workbook I have: {SUM(IF($C1:$C$10000 = "England",$E$1:$E10000,0))} (If I want to see, say, the top 10 outstanding bills in England I have to use a pivot table). Excels biggest problem is that the workbooks produced with it are really hard to maintain. Looking at the code above, for example... going back to that (which is only a trivial example) in a month is going to be pure pain. Updating data requires cutting and pasting, which can be error prone. Unit testing is only possible with sample known-good data sets, and copying in new data tends to make it less than certain that the version you are using is the same as the one you tested. Oh, and sharing workbooks between users is really tough. I can usually figure out other people's Java - but I have yet to be able to reverse engineer a non-trivial Excel work flow.
- audentis 18y ago
  BTW, you might be able to throw =large() around that formula to get your top 10 directly
- ph0rque 18y ago
  > SUM(IF($C1:$C$10000 = "England",$E$1:$E10000,0)) Slightly offtopic, but I just wanted to point out that excel syntax looks a lot like lisp, with biggest different being the first item is outside the parens.
  - herge 18y ago
    Excel: The world's most popular functional programming language.
    - khafra 18y ago
      After searching for a decent reverse polish calculator for the iPhone for months, I finally realized that a spreadsheet app fits the bill there as well.
- jwilliams 18y ago
  You can do a lot of stuff that overlaps with queries with just array formulas, and a lot of the rest with pivot tables. This is true, you can do a lot with this. And if you're prepared to make a lot of columns with the various flags you need, you can build up quite complex queries - having an easy way to do this would be nice though. I've spent a lot of time in Financial Services and it's surprising how much of the industry is run on spreadsheets, especially investment banking - and I mean online - they'll have Excel running all day, receiving real time price feeds, running a calculation and republishing. Excel is pretty much the glue that holds the whole industry together. So I also agree on the maintenance issue - These sheets can get quite complex and it's almost impossible for someone to understand coming in cold. It would be very useful to have a workflow model on top - which I guess is really adding the algorithm aspect - but I've never seen a speadsheet metaphor that does this well.
- erso 18y ago
  You might find dynamic named ranges to help keep things orderly. That way you can refer to the ranges by name, and also not have to worry about your $E$1:$E10000 not being up to date when there's 15000 rows of data instead of 10000. You could do something like this: {SUM(IF(country_name = "England",bill_amt,0))} http://www.ozgrid.com/Excel/DynamicRanges.htm http://www.ozgrid.com/Excel/DynamicRanges.htm
cabalamat 18y ago
> Excel isn't a database but a lot of people use it like one anyways. Then why not build database-like functionality into the system?
- nailer 18y ago
  I'd like to be able to open a database as as spreadsheet, with fields turned into column names, and tables on worksheets. I'd like v and hlookups to be turned into SQL (and vice versa) with animations explaining what's happening in each.
kirubakaran 18y ago
How about this: http://www.bygsoftware.com/Excel/SQL/UsingSql.html http://www.bygsoftware.com/Excel/SQL/UsingSql.html Or: http://www.exceluser.com/explore/msquery1_1.htm http://www.exceluser.com/explore/msquery1_1.htm
est 18y ago
did you mean SQL? You can connect to a .xls via ODBC

Queries. Excel isn't a database but a lot of people use it like one anyways.

by rms 18y ago

Queries. Excel isn't a database but a lot of people use it like one anyways.

Just the ability to represent one-to-many relationships in a native way (without resorting to hiding rows or the like) would be a huge step in that direction. The goal could be: make Excel handle more of the borderline cases where you really should be using a simple database, but all you know is Excel.

notauser 18y ago

You can do a lot of stuff that overlaps with queries with just array formulas, and a lot of the rest with pivot tables. For example, getting the sum bill of every person who lives in England looks like this in one workbook I have: {SUM(IF($C1:$C$10000 = "England",$E$1:$E10000,0))} (If I want to see, say, the top 10 outstanding bills in England I have to use a pivot table). Excels biggest problem is that the workbooks produced with it are really hard to maintain. Looking at the code above, for example... going back to that (which is only a trivial example) in a month is going to be pure pain. Updating data requires cutting and pasting, which can be error prone. Unit testing is only possible with sample known-good data sets, and copying in new data tends to make it less than certain that the version you are using is the same as the one you tested. Oh, and sharing workbooks between users is really tough. I can usually figure out other people's Java - but I have yet to be able to reverse engineer a non-trivial Excel work flow.

audentis 18y ago
BTW, you might be able to throw =large() around that formula to get your top 10 directly
ph0rque 18y ago
> SUM(IF($C1:$C$10000 = "England",$E$1:$E10000,0)) Slightly offtopic, but I just wanted to point out that excel syntax looks a lot like lisp, with biggest different being the first item is outside the parens.
- herge 18y ago
  Excel: The world's most popular functional programming language.
  - khafra 18y ago
    After searching for a decent reverse polish calculator for the iPhone for months, I finally realized that a spreadsheet app fits the bill there as well.
jwilliams 18y ago
You can do a lot of stuff that overlaps with queries with just array formulas, and a lot of the rest with pivot tables. This is true, you can do a lot with this. And if you're prepared to make a lot of columns with the various flags you need, you can build up quite complex queries - having an easy way to do this would be nice though. I've spent a lot of time in Financial Services and it's surprising how much of the industry is run on spreadsheets, especially investment banking - and I mean online - they'll have Excel running all day, receiving real time price feeds, running a calculation and republishing. Excel is pretty much the glue that holds the whole industry together. So I also agree on the maintenance issue - These sheets can get quite complex and it's almost impossible for someone to understand coming in cold. It would be very useful to have a workflow model on top - which I guess is really adding the algorithm aspect - but I've never seen a speadsheet metaphor that does this well.
erso 18y ago
You might find dynamic named ranges to help keep things orderly. That way you can refer to the ranges by name, and also not have to worry about your $E$1:$E10000 not being up to date when there's 15000 rows of data instead of 10000. You could do something like this: {SUM(IF(country_name = "England",bill_amt,0))} http://www.ozgrid.com/Excel/DynamicRanges.htm http://www.ozgrid.com/Excel/DynamicRanges.htm

cabalamat 18y ago

> Excel isn't a database but a lot of people use it like one anyways. Then why not build database-like functionality into the system?

nailer 18y ago
I'd like to be able to open a database as as spreadsheet, with fields turned into column names, and tables on worksheets. I'd like v and hlookups to be turned into SQL (and vice versa) with animations explaining what's happening in each.

kirubakaran 18y ago

How about this: http://www.bygsoftware.com/Excel/SQL/UsingSql.html http://www.bygsoftware.com/Excel/SQL/UsingSql.html Or: http://www.exceluser.com/explore/msquery1_1.htm http://www.exceluser.com/explore/msquery1_1.htm

est 18y ago

did you mean SQL? You can connect to a .xls via ODBC