• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel spreadsheet has daily totals on it and is growing and they want stats and trends...[CLOSED]

Status
Not open for further replies.

Sabon

New Member
Excel spreadsheet has daily totals on it and is growing and they want stats and trends. Please don't say, "Use Pivot Tables" because my boss with fire me and hire someone else if I suggest that.

One of the things they want is daily and monthly stats:
* Median
* Average
* Record High
* Record Low

That's a start but they will probably want more.

So we have the main sheet that has daily totals. On it I added a column for "Day of the Week" which has a number from 1 to 7 where 1 is Sunday and so forth. I then copied that sheet onto another sheet which I then sorted by the Day of the Week.

For each of the columns they want the status above.

New lines on the main sheet (and my second sheet which has links to all the cells on the first sheet) are added every day. So every day I run the sort on the second sheet so that all the day numbers are lined up again.

I found a way to find the last day of day 2 (Monday) using =8+(MATCH(2,'Sorted by Day of the Week'!$A$8:$A$450,1))-1

Note that the 8+ is because there are seven (7) header lines and the daily totals don't start until row 8. In my simple version of the spreadsheet that I'm testing things on the daily totals for day three (Tuesday) is line 40 so that means the last line for Monday is row 39.

I now want to get the Median, Average, Record High and Record Low and ... well there are more. I haven't been able to figure out a way (and my boss does NOT like Pivot Tables so please don't suggest that. How can I get those status without having to manually change things (except running the sort).

I've got it to where I manually have to change it every day as follows: =MEDIAN('Sorted by Day of the Week'!C$8:C$36) but every new day there are new daily totals meaning a new line of stats. How can I make it so that it will automatically adjust depending on what day it was entered on.

On Monday, for instance, it will change from C8:C36 to C8:C37 which means Tuesdays will change from C37:C70 to C38:C71 and Wednesday from C71:C102 to C72:C103 and so on. And depending on the day only those days and onward would change.

Hopefully this makes sense. But Pivot Tables are not something my boss with listen to so I have to find another way even if it isn't as good.
 
NARAYANK991 - Sorry but my boss has had me swamped and I've been doing this by hand. If you or someone else can help that would be great.

Sample file is attached.
 

Attachments

  • Book1.xlsx
    21.9 KB · Views: 3
PS: I know I haven't been posting this forum for answers so it probably doesn't look like it is important to me but it ---REALLY--- is. We are just on the road a lot and I don't always have an Internet connection which really sucks.
 
Status
Not open for further replies.
Back
Top