Pivot Medians

[Loomah]

Morning All!
I'm trying to create a Medians field in a pivot table of salary data using a formula

The data is broken down by grade from fairly large data set and shows head count and mean. I'm trying to use a formula for the median value but keep ending up with sum of the calculated field.

The formula I'm using for the calculated field (named median) is
=MEDIAN(SALARY)
which seems perfectly logical to me!!

However the pivot table is summarising the data by summing so I end up with the sum of the median(?) and I'm starting to lose the plot/the will to live/my marbles (delete as necessary) The actual result I get is mean * head count.

Am I missing something simple or am I trying something I can't do?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

OK
I've got part of my answer - the fact that calculated fields work on the sum of the underlying data could have something to do with it!

So the median of the sum of the salaries is going to be the sum of the salaries!!

Is there another way I can do this? At the moment it looks like going through and calculatiing each median seperately etc etc but I'd prefer if there were a more dynamic way of doing this analysis - if anyone knows of one....

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Hi,

It would sure be helpful of you could post some sample source data.

Skip,

_{[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE!}

 

[Loomah]

Skip
Thanks for coming back on this.

It's a little tricky posting sample data but a 'picture' of what it's like may help you help me!

Simplified it's columns showing Grades, salaries, min salary for grade, max salary for grade, which quartile of the pay range salary is in. So an example might be

Grade A; £25,500; £20,000; £30,000; 3
Grade F; £10,000; £10,000; £15,000; 1

There are about 14,000 rows of data covering about 30 grades. I have the arithmetic mean, total costs, count of individuals in quartiles etc by grade but would like the median salaries by grade too.

Any help would be greatly appreciated - even if it's to tell me I can't do it!

BTW, as it may have an impact, I'm on xl97 at the moment.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

I'm still not sure that I understand the problem.

What about creating your own aggregation table. You might use SUMIF or SUMPRODUCT to sum the Salaries by Grade and calculate the MEDIAN from that. That way you can mix & control aggregation methods.

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[Loomah]

Skip
Not sure I understand what you're saying now! Using Sumif or someproduct isn't going to help me calculate the median, is it?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

I guess using some array formulas would have been a better choice.


Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[xlbo]

Loomah - I get ya. I've never found a proper way around this as you are correct in that the value you calculate will always come back to the SUM as you will be summing up your medians. The only way I have got around it is either to do away with the pivottable and use SUMPRODUCT or add some extra formulae outside of the pivottable (not so satisfactory if the amount of data is likely to change

 

[Loomah]

Gentlemen
Thank you both for your input.

This is now more an academic exercise as I just got someone else to manually enter median formulas for each grade - changing the range as appropriate!!

Now I'm intrigued as to how to use sumproduct to calc medians for a range. You may have realise some time ago I never really got to grips with sumproduct!!

eg
something like this is simply utterly wrong!
=SUMPRODUCT((A2:A30="a")*MEDIAN(B2:B30))

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

[tt]
=median(sumproduct((grade=A2)*(salary)))
[/tt]


Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[Loomah]

Thanks Skip but it don't work!

The formula returns only the sumproduct element, which is the same as using sumif. Unless I've missed something!

I'd have never got that far myself, though - just see my own attempt!! But having seen your suggestion I'm 'guessing' that the formula is working out the sumproduct element and taking the median of that. In otherwords the median of X is X.

As I have said, this is now purely academic. If any one has or comes up with a solution I'd love to see it but the job itself is done & dealt with!

Thanks again for the input so far.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​