Book reading speed to graph

Occasional Contributor

I am trying to keep track of my book reading and i want to know aprox how many pages i read per day. I have similiar data to what i have attached and i would like to plot it on a chart, to something like this:

Metchik_0-1634560462268.png

So i can see total number of pages per day (week,month) and different stack colours for individual books i am reading during that time. Pages per day is calculated as an average (total number of pages/days).

 

Can someone help me with the graph, since i cannot make excel do it without too much hassle (too much formulas and vlookups)?

Thank you!

 

5 Replies

@Metchik 

If identify each book on the chart I don't think that will be easy task even for pros. You may have hundreds of books totally, I don't think we shall show all of them, that will be mess. Perhaps only books were read during filtered period of time, thus dynamic labeling.

 

@Metchik  not sure all that you want or going for but see the attached as a starting point.  I gave you time steps (days) so you could change the time periods from days to weeks (7 days) or something close to months (30 days).  I also added a start and end date to filter the relevant range you want to look at.  But the part i was most unsure about was if you wanted to show pages/day regardless of the time period or the total number of pages for each time period (based on the average).  i went with the later so if you are looking at a week and the book ended 1/2 way then it took # of days in that period when you read * your avg pages/day at the # of pages read during that partial week.

 

@mtarler yes, you did it, It looks just like i wanted! Thank you
But now whenever i change it somehow, the calculations go wild (#value or #name). What to do, when i would like to add a row to the table of books; or take your concept to different excel sheet?
@Sergei Baklan
Yes, that would be sufficient. I would be ok even with only the total number, how many pages ive read on average on the given day/week/month.
Do you know the way to do it?
how are you changing it? The data table will automatically include all the books across the top and dates accordingly on the left. The data section is based on those headers and may need to be filled down/right as needed. The plot may need to be re-created (or editted) when you do fill down/right. I could create custom names that would autoscale the length of the data but that will not solve the issue if you add data sets (i.e. books).