Forum Discussion
Ignoring blank cells when indexing
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.
- Apologies, I seem to have missed the link in the text, that does help. I see that your changes are very helpful so thank you very much, I'll adapt the tables in order to implement 'scores'.
I've never used pivot tables before so I agree, I'll need to familiarise myself with their uses.
Again, thanksSince there are only a few days in the sample data, here's are two revised Pivot Tables that simply show the averages and counts for the full database.
As I've said, though, and as you'll learn when you read up (or YouTube up) on Pivot Tables, the system is smart enough to start itself grouping by month. And you can force other groupings if you would like, by using helper columns to identify, for example, weeks.
- Ahh yes, thanks for that, indeed it appears there's lots to learn about pivot tables. I'll make sure to look into adapting them to my needs.
Thanks!
