Need Mode and Median in Pivot Table - not just Average 2

[Louise99]

Is there any way I can get a median and mode in a pivot table - or type of table.? I have a column of dates for an entire year, the days of the week that represent that date and the number of people per date. I want to show that on Mondays we had an average of 10.5, mode 9 etc. It would look like:
Average Mode Median
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

Any ideas?

Thanks!

 

[SkipVought]

I'd sort the source data by the Day Of Week and then analyze each group individually for your three aggregations.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Louise99]

Yeah, kinda figured I'd have to do that . . .

Hey, Microsoft, LET US PICK OUR OWN FUNCTIONS!! DARN YA!

Thanks, Skip ;-)


Thanks!

 

[yogia]

Hi Louise99:

Let us have a look at ...

array formula in cell F2 is ...
=MODE(IF(WEEKDAY($A$2:$A$29)=MOD(ROWS($1:1),7)+1,$B$2:$B$29))

array formula in cell G2 is ...
=MEDIAN(IF(WEEKDAY($A$2:$A$29)=MOD(ROWS($1:1),7)+1,$B$2:$B$29))

formula in cells E2, F2, and G2 are then copied down.

Would something like this do for you?

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

Hat's off to ya, Yogi ==> *

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[yogia]

Thanks Skip ... It is late here (midnight in Michigan). Are you also burning the midnight oil?

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[alostart]

Yogi, I'd like to follow-up on your response -- how does something like that work with something that isn't days of the week? Right now your formula has WEEKDAY specified -- what would you do if it wasn't a day of the week? Same idea of a large data set with a core set of values in one column, returning back the median for a corresponding column. And how is it referencing column D so it knows which row it is on to display the summary info?

(Sorry if this is a stupid question! I'm really interested in using this formula, it's v. cool!)

 

[yogia]

Hi alostart:

It will help if you would post a small sample along with the expected result and associated logic/explanation as necessary ... and then let us take it from there.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[Louise99]

Thanks, Yogia.

I finally did put together a spreadsheets with similar formulas. Thanks for your input!

I still want Microsoft to make this available in a pivot table! How hard could it be? They have min, product, standard deviation & such . . .

;-}

Thanks!