Making a dumb spreadsheet smart

January 14, 2011 1:55 PM

I'm having trouble solving a problem in Google Spreadsheet.

Check out this screenshot.

Basically, this is a calorie counter. In column A, I put the date, in column B, a list of all the things I ate, in column C, their caloric values.

Column D and E are formulas based off C. Column D adds up all the calories for the day, while column E takes the total from column D and calculates caloric deficit.

As of now, I manually enter in formulas into D and E to get the daily sum and deficit. I'd prefer this to be automatic. How can I make the spreadsheet understand when I've entered a new date, and begin calculating the sum from the first row of the new date?

posted by downing street memo to Computers & Internet (3 answers total)

i'd separate the recording and the tallying into two different sheets

https://spreadsheets.google.com/pub?key=0Amxwmu_50Ln_dFN3dV9qNlNXYm1nc2dLVjI1YjR3U3c&hl=en&output=html

click the link to edit and i can give you the permissions to see formulas etc

the key is in column B of the Progress sheet, the formula is

=sum(filter(Log!$C:$C,exact($A2,Log!$A:$A)))

you can pre-populate the rows in Progress and just keep adding rows to Log when you eat stuff
posted by maulik at 2:44 PM on January 14, 2011

I've changed your spreadsheet slightly. I've had to add dates to each line (you have blanks when you've eaten more than one thing on one day).

Once you do add the dates you can input in column D (and copy down to the end of the spreadsheet) this formula.

=IF(AND(A4=A3,A4<>A5),SUMIF(A:A,A4,C:C),"")

In column E I've got

=IF(ISNUMBER(D4),2734-D4,"")


I've put it online here. I hope I've linked to google docs correctly.
posted by selton at 3:09 PM on January 14, 2011

ah, i had not linked up my sheet properly. here's a more functional link. thanks, selton
posted by maulik at 3:17 PM on January 14, 2011