Quote:
Originally Posted by Paperbackstash
It's a formula I'm using, I go by start and end dates, but with a few exceptions since I read several at once, some sit on the back burner brewing for awhile, a few (not many) from the end of last year. This is what is driving the average up when it's mixing it all together. I'm especially slow with anthologies.
|
Thank you. I see it in your Sheet now. (I totally missed your signature line earlier.)
Your Sheet is great, so much detail. More than I've ever wanted or needed to track and I've been accused of tracking too much. (No such thing of course.
)
Spoiler:
I really want to introduce you to the ARRAYFORMULA function in Sheets though. It can save SO many formulas and pain points (although it does have some limitations and some functions won't work with it.)
As an example of what it can do, try deleting the values in column AA on your 2021 tab and then putting this in cell AA1:
Quote:
|
={"Days Read";ARRAYFORMULA(IF(Z2:Z="",,DATEDIF(Y2:Y-1,Z2:Z,"D")))}
|
That one formula will replace all of the other formulas you have in that column. The downside is that if you want to override a single row it will break and you can't do that. For most things it doesn't look like you do that though.
Another example would be erasing the formulas in Tables!R42:R50 and putting this in cell R42:
Quote:
|
=ARRAYFORMULA(COUNTIF('2021'!Q:Q,L42:L50))
|
And if your sheet was formatted different (all years on the same tab) the QUERY function would be WONDERFUL for some of what you are doing. As-is, it still might be cool for some things. For example, try putting this in a blank tab:
Quote:
|
=QUERY('2021'!Z2:AG,"Select Year(Z), Count(AG) where Z is not null group by Year(Z)",0)
|
If you had all years in the same tab you could easily get a list of all states read in all years with a single formula.
Admittedly, you could get close to that now, it's just not quite as easy. This will get the 2019 through 2021 tabs:
Quote:
|
=QUERY({'2019'!Z2:AG;'2020'!Z2:AG;'2021'!Z2:AG},"S elect Col8, Count(Col8) where Col1 is not null and Col8 is not null group by Col8 pivot Year(Col1)",0)
|
This wouldn't let you group 2011-2015 or 2016-2017 like you've done now though.