Forum Discussion
jsinghbest
Jan 08, 2024Copper Contributor
Ignoring blank cells when indexing
I've been working on a self-written daily habit tracker, habits are grouped into 3 categories. I aim to produce a data table that efficiently quantifies/summarises my performance in these habits over different timeframes (last 3,7,10,14 etc. days). It allows for simple alterations to the sample data, for example referencing a cell with value, "60", representing an hour's worth of time, that can be changed to "120" later on without rewriting the formula.
I've created (admittedly over-complicated) formulas to tackle this, my current problem is that I've stopped some habits/want to leave a blank row to separate blocks of time and my formulas take blank cells as "0" without skipping them in the indexing and average-taking processes.
I'd appreciate any help with this, and any suggestions you have!
(Apologies in advance for my very messy formulas)
- mathetesSilver Contributor
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.
- jsinghbestCopper Contributormathetes
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 🙂- jsinghbestCopper Contributormathetes
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.