Ignoring blank cells when indexing

Copper Contributor

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)

9 Replies

@jsinghbest 

 

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.
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.

@jsinghbest 

 

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, thanks

@jsinghbest 

 

Since 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!