7 ms·
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 ev
by 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 agoBTW, 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.
- jwilliams 18y agoYou 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 agoYou 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