On Tue, Feb 19, 2013 at 11:00 AM, Wesley Peterson < [email protected]> wrote:
> 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 > > -- Eschew obfuscation and pompous prolixity. Light a man a fire, he is warm for the night. Light a man afire, he is warm for the rest of his life.
_______________________________________________ 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
