• 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 it is growng and they want stats and trends

S

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. *** See (fixed) attached sample file. ***

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.
 

Attachments

  • SampleSpreadsheet.xlsx
    19.4 KB · Views: 6
Welcome Sabon! If you search the forum database you can find a handful of useful posts similar to yours. For instance the following describes a formula for finding the 2nd largest number in an array. http://chandoo.org/wp/2010/10/08/large-if-array-formula-tutorial/
See your file with this simple formula expanded to include Average, Mean, and Small. I just ran the array range down to row 500 for this example.
 

Attachments

  • CopySampleSpreadsheet.xlsx
    20.4 KB · Views: 4
My spreadsheet is a lot more complicated than that. Or should I say the one that was given to me to keep updated.

Every work day I've given a bunch of stats (about 25) which they have me adding on a separate line for that day.

Then on another sheet that they created they have more columns where it uses the date to show what day of the week each date is. Then they have that sorted by day number (Sunday = 1, Monday = 2 and so on) because they want a comparison chart of each day compared to every other day over time.

A third sheet (and there about 30 sheets for different things) has the same columns as the first sheet but vertically it doesn't have a new row for every day but instead it has four rows for each day of the week with "Median, Averages, Record High, Record Low. For each stat there is a column with formulas such as:

=MEDIAN('Sorted by Day of the Week'!C$8:C$58)
=AVERAGE('Sorted by Day of the Week'!C$8:C$58)
=LARGE('Sorted by Day of the Week'!C$8:C$58,1)
=SMALL('Sorted by Day of the Week'!C$8:C$58,1)

Note that it has the following for each column. So you change C$ to D$ and so on through all the columns.

Now I figured out how to find the first row on the spreadsheet for each day of the week (we are only worrying about Monday thru Friday)

=8+(MATCH(2,'Sorted by Day of the Week'!$A$9:$A$438,1)) = Monday
=8+(MATCH(3,'Sorted by Day of the Week'!$A$9:$A$438,1)) = Tuesday

and so on.

For my example spreadsheet (actually a different one than the one I gave you but a bigger one I'm playing with), the row number I get for the formulas above are:

58 (Monday) Notice the "58" in the "Median Average, Large, Small" formulas above.
111 (Tuesday)

I've tried putting the "=8+(Match(2,'Sorted..." formula into the "mediun, etc" above but it always tells me that I have an error in my formula. Note that the =8+ part of the formula is because there are 8 header rows at the top of the spreadsheet and I had to make it start searching after that.

=MEDIAN('Sorted by Day of the Week'!C$8:8+(MATCH(2,'Sorted by Day of the Week'!$A$9:$A$438,1))

It doesn't like me combing them. It always gives me an error. Any suggestions?
 
The data in the in your sample sheet start on row 7 not 8 so I altered your formula to reflect that and I was able to use: {=AVERAGE(INDIRECT(CONCATENATE("C$7",":","C",7+(MATCH(2,A$7:A$26,1))-1)))} Be sure to use Ctrl+Shift+Enter.
 
You are very welcome! I would amend the formula to include a row count for the date portion so you don't have to adjust each dates formula just drag it down.
i.e. for Tuesday it could look like this: {=AVERAGE(INDIRECT(CONCATENATE("C$",7+(MATCH(ROWS($K$46:K47),A$7:A$26,1))-1+1,":","C",7+(MATCH(ROWS($K$45:K47),A$7:A$26,1))-1)))}
This part will allow the formula to switch between days of the week: ROWS($K$46:K47)
 
Thanks. You've given me home work to break that down bit by bit to figure out exactly what that does and then I'll be able to apply that. Thank you again.
 
Here's a variation on the theme .... I've used an Excel Table named Sales_Data to hold all the data, and used Array formulas and Structured references from the table to produce the output report. I also use range names for the Weekdays to make the formulas more literal. Although the initial setup is more work, as you continue to add data to the table the formulas remain the same ... no further editing is necessary.

On the subject bosses who decree "No Pivot Tables!", what other neanderthal tendencies do they exhibit? Do they make you light fires by rubbing bits of wood together?
 

Attachments

  • SampleSpreadsheet - DME.xlsx
    26.4 KB · Views: 11
@David Evans Weird: Whenever I open your file and try to switch tabs, Excel crashes. I'd be interested to hear if anyone else has an issue with this. I'm using Excel 365 running Excel 2016 version
 
jeffreyweir said:
@David Evans Weird: Whenever I open your file and try to switch tabs, Excel crashes. I'd be interested to hear if anyone else has an issue with this. I'm using Excel 365 running Excel 2016 version
Click to expand...

Sorry about that - it's a very simple file, in essence but I'll try to open it and see what's going on with it ...

The Pivot Table Haters are more common than I'd expect, to be honest. I suspect its rooted in their inability to use them properly, therefore the tool is of no use .... The workarounds people come up with to avoid them are spectacular, fair play ....:rolleyes:
 
David Evans said:
Here's a variation on the theme .... I've used an Excel Table named Sales_Data to hold all the data, and used Array formulas and Structured references from the table to produce the output report. I also use range names for the Weekdays to make the formulas more literal. Although the initial setup is more work, as you continue to add data to the table the formulas remain the same ... no further editing is necessary.

On the subject bosses who decree "No Pivot Tables!", what other neanderthal tendencies do they exhibit? Do they make you light fires by rubbing bits of wood together?
Click to expand...
Just to be clear, A1 through E21 in the SampleSpreadsheet - DME.xlsx is an example of a Pivot Table, Pivot Tables?
 
Dave, couldn't you ditch the Lookup,References sheet by replacing the formulae in column B:
=VLOOKUP(WEEKDAY([@Date],1),Table2,2,FALSE)
with:
=TEXT([@Date],"dddd")
 
p45cal said:
Dave, couldn't you ditch the Lookup,References sheet by replacing the formulae in column B:
=VLOOKUP(WEEKDAY([@Date],1),Table2,2,FALSE)
with:
=TEXT([@Date],"dddd")
Click to expand...


Absolutely.
There's many ways to skin every cat ;) (None of which the cat finds pleasurable, but they all get to the same ending via different waypoints ...)
 
You must log in or register to reply here.
Back
Top