Jack, I use the following in the spreadsheet I keep my Checking Acct info in. The following cell formula is replicated in my column J which I use for a "Cover" amount: *=IF(ISBLANK($B3),"",IF($D3=$D2,J2,0)+$E3)*
Col B is a Payee Number. If B is blank, no entry yet, Cover is blank. Col D is the Date. If the date of the current row matches that of the previous row, I will add the prior row's cover amount, else 0. Col E is the amount of the check. This value is added to either the cover amount of the previous row, or to 0, based on the date comparison. Col J is the running total for all checks written on the same date. I prettied it up a little with conditional formatting. I really didn't want to see the cover amount for every check row -- only the last check written on a particular date. In addition to the above formula, I have the following conditional format replicated down the Cover Column: Formula is: $D4=$D3. Cell Style WhiteOnWhite Here I look to see if the next row's date matches the current row's date and if it does, I make the text of the current cell white numbers on a white background (looks empty, but the number is available for my cover calculation). If the next row is either not yet filled in, or has a date different than the current row, the normal formatting for the cell (black on white) is used and the value is visible. In my spreadsheet, I add my next check in the next row, so I don't have embedded blank rows. I don't know if you are leaving blank rows on purpose, but if you are my formulas won't quite work for you. If, on the other hand, you're adding rows one at a time with no gaps, the test I use to see if a column that must be filled in is blank and using that test to either make the cell a blank or an average should work nicely for you I hope this gives you some ideas. Wes On Feb 19, 2013 9:47 AM, "Jack Chastain" <[email protected]> wrote: > > 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 > >
_______________________________________________ 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
