On Feb 18, 2013 7:38 PM, "Chris Knadle" <[email protected]> wrote: >
>> - what I am trying to do is get an average of > > the data in a column above the row the data is on. The average bit is easy: > > "-=AVERAGE($D$3:D3)" - That will look odd on the first line, but when you > > drag it down - say 13 rows, it becomes "=AVERAGE($D$3:D13)" - which is > > exactly right for row 13 - but if the data in rows 4-13 isn't yet present > > (this is a mileage comparator/calculator, so until time passes, it won't > > be), I want to really say "If there is information provided in cell D2, > > then give me the average, otherwise give me 0" > > I've got an idea of how to deal with this. AVG = SUM / COUNT, so... instead > of using the AVERAGE function, use SUM(range) / COUNT(range) -- because the > COUNT function skips over empty cells. There's also a function to count the > number of empty cells, if you need it. > > -- Chris Chris, this nearly works, but has the minor flaw that if there is no data in the source rows (no mileage entered yet), the calculation continues with the earlier values - correct as far as it goes, but ugly. It is livable, but brings us back to the "multiple statement" issue, which would now be something like "if the source reference cell is empty, return blank, else return sum/count" - but at least this is a slightly better result than a continually decreasing calculation! By the way - if anyone wants a spreadsheet that can tell you at what mileage level one type of fuel is a match - or better than - another type of fuel (I am using diesel against regular, but that is flexible), I have the sheet for it now. Yeah - I am an idiot, but there you go ;-) JC
_______________________________________________ Mid-Hudson Valley Linux Users Group http://mhvlug.org http://mhvlug.org/cgi-bin/mailman/listinfo/mhvlug Upcoming Meetings (6pm - 8pm) Vassar College Feb 6 - Raspberry Pi Mar 6 - 10th Anniversary Meeting - Linux where you least expect it Apr 3 - Typography: Physical Art to Digital Art
