Forum Discussion
Ignoring blank cells when indexing
It's hard to navigate that sheet and figure out what you are recording -- obviously some of it is number of times you do some of the things; some of it is minutes spent doing some of them -- and what you want to do with all that info.
My general impression--as a database designer--is that you're doing too much of the work that Excel could do, were you to simply record raw data on a per day per habit basis, and then employ something like the Pivot Table to summarize. You wouldn't need to enter zero in some of the columns; there simply would be no entry for that habit for that day.
I've created a simple starter as an example. The Pivot Table can produce both averages AND counts for the different activities. And once you've done a few months worth of entries, it will enable you to summarize by month if you'd like that.
- mathetes
Thank you so much for the help, advice and insight. I'll implement your suggestions as they appear to fix a lot of the issues I've been having/make it simpler.
Again, cheers 🙂- mathetes
I am wondering though, is it possible to output the last 'x' non-blank cell values in a column? And for the formula to use the new list in its operations? Thanks.- I'm not following your meaning. For one thing, using a Pivot Table to summarize the data, generally does not require the use of any formulas. You just record, in tabular form, the raw data. Excel does the work of summing, averaging, counting...whatever. It's an amazingly powerful tool.
But, please, just explain a bit more fully--perhaps by again adding a spreadsheet that illustrates your question--and I (and others here) will see if we can help further.
