Forum Discussion
Ignoring blank cells when indexing
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 🙂
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.- Replying doesn't seem to be working well, but I've attached a spreadsheet that hopefully shows/explains what I'm trying to do and the steps I've taken/avoided to achieve that.
I'm not familiar with pivot tables and their functionality, so I'm unsure how to modify one to suit my needs.I gave you a link in my first reply that will explain Pivot Tables. Did you look at it? Did you do anything to work to understand it?
And there are MANY videos on YouTube that do the same.
The Pivot Table is one of the most popular (deservedly so) tools in Excel. It will do a lot of the work, if not all of it, that you're trying to do.
You might need to supplement it if you want to compare actuals with some kinds of goals (which appears to be what you're trying to do). For example I've modified your raw data table to be in the form you should start using, one row per day per activity. This avoids those blank cells you are otherwise plagued by.
Then I've created two Pivot Tables from the data;
- the first one gets the averages (though since they're daily, those would only be meaningful if you recorded, for example, two walks in one day), but the grand total column shows the averages for the activities. Once you get more days--you can start collecting in weekly or monthly averages, which will be more meaningful.
- the second Pivot Table just counts activities. Again, you'll need to get more raw data in order to get numbers other than 1.
But the basic point here is that you should do some self-education on the Pivot Table. It's not hard to learn and will be something you'll come to treasure.
