Currently Being ModeratedMar 9, 2013 1:29 PM (in response to George Coghill)
Haven't seen you here for a while.
You need something to compare TODAY() with to create a Boolean expression for the IF statement. How about sharing your layout with us and we can suggest some programming to get you where you want to go.
Currently Being ModeratedMar 21, 2013 9:01 PM (in response to Jerrold Green1)
Hi Jerrold, sorry was trveling and work got busy. I've added a copy of the spreadsheet to Dropbox.
On the spreadsheet, I have a main "Overview" sheet where I am pulling data in from the tables for a quick reference. I am actually using this on the iPhone, but opening the document on the Mac screws everything up so the one here is an older copy. But the layout is the same.
There is also a sheet named Sleep Totals, which pulls in the data from the main table where I enter the data. I had to do this to reformat the info the graph it properly.
Basically I am looking to add a new box to the Overview sheet that displays last night's sleep duration, and if possible a row of cells that display the sleep duration for the past week. Perhaps the past 7 days would be more ideal. Orignally I just wanted Sun-Sat, but that would result in blank days until the week was over.
Currently Being ModeratedMar 21, 2013 11:35 PM (in response to George Coghill)
You've done most of the heavy lifting here. All that's left is to write a VLOOKUP formula to retrieve the values from the rows you want, then chart it.
Here's what it looks like with the new table and chart placed beside the Sleep Duration table on your Sleep Totals sheet.As your table doesn't include current data, I used a fixed date in place of TODAY():
Formulas in the small table:
A2, and filled down to A8: =A$1-(8-ROW())
B2, and filled right to C2 and down to C8: =VLOOKUP($A,Sleep Duration :: $A:$C,COLUMN())
To get the current week, either enter =TODAY() into cell A1, or replace the cell reference to A$1 with TODAY() in the first formula (before filling it down the column).
Chart uses the values in column C, and is the default vertical bar chart with two adjustments: Squeezed horizontally to fit better in the screen shot; labels on category axis rotated 90°.
Currently Being ModeratedMar 22, 2013 6:10 AM (in response to George Coghill)
Thanks for sharing your spreadsheet with us. Barry's suggestion looks appropriate for your needs. I notice that you have a provision for a Walked Miles Average. Is that going to be a Miles per Day average for the entire period covered by the data?
Currently Being ModeratedApr 28, 2013 6:34 PM (in response to Barry)
Sorry for the delay again. This worked great on the Mac. I am not sure how to get that $A function into the formula for columns B and C on the iPhone though. When I add it manually it treats it like the literal text string. Not sure which funtion to choose to get that inserted.
I'm not exactly sure how this works, but it seems to be what I was looking for. Thanks!
Currently Being ModeratedApr 28, 2013 6:38 PM (in response to Jerrold Green1)
The idea there was to gather all the days I actually walked, and then get an overall average of how many miles I walked. I don't walk every day even though I should
Some of these forumals are me trying to push the spreadsheet so I can learn what things do. I am primarily gathering data as I learn functions, and from there will use the data to see if I can find any info. In other words, some of these are just tests. This one is like that.
The sleep totals for the week is something I'd like to have on the main screen though.