• 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.

Median if - formula

R

ranaray

Member
Hi gurus,

Struggling to get a median if formula. The one that I am using is
=MEDIAN(IF(B:B=B5,C:C))

Want to find the midpoint for the below data.

where B = the Range of names
B5 = the actual name within the range
C = values related to the Bs

Personal Job Title (B) Annual Pay (C)
Abnormal Loads Assistant 16000
Abnormal Loads Officer 19096.44
Account Manager 63278.38
Accountant 55934.25
Accountant 66883.5
Accountant 60054.75
Accountant 51005.6
Accountant 59276.5
Accountant 56155.6
Accountant 46000
Accountant 34000
Accountant 39682.5
Accountant - Investments 35000
Accounts Assistant 29110.89
Accounts Assistant 22500
Accounts Assistant 18105.34
Accounts Assistant 25060.23
Accounts Assistant 20350
Accounts Assistant 20350
Accounts Assistant 18315
Accounts Assistant 10158.72
Accounts Assistant 22893.75
Accounts Assistant 15262.5
 
ranaray said:
Hi gurus,

Struggling to get a median if formula. The one that I am using is
=MEDIAN(IF(B:B=B5,C:C))

Want to find the midpoint for the below data.

where B = the Range of names
B5 = the actual name within the range
C = values related to the Bs

Personal Job Title (B) Annual Pay (C)
Abnormal Loads Assistant 16000
Abnormal Loads Officer 19096.44
Account Manager 63278.38
Accountant 55934.25
Accountant 66883.5
Accountant 60054.75
Accountant 51005.6
Accountant 59276.5
Accountant 56155.6
Accountant 46000
Accountant 34000
Accountant 39682.5
Accountant - Investments 35000
Accounts Assistant 29110.89
Accounts Assistant 22500
Accounts Assistant 18105.34
Accounts Assistant 25060.23
Accounts Assistant 20350
Accounts Assistant 20350
Accounts Assistant 18315
Accounts Assistant 10158.72
Accounts Assistant 22893.75
Accounts Assistant 15262.5
Click to expand...
Hi,

The formula is fine but you must remember to enter it as an ARRAY formula, see below.

Just one point, I would avoid full columns because it could make the formula and worksheet slow to calculate. Is 5000 row enough?

=MEDIAN(IF(B1:B5000=B5,C1:C5000))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.
 
There is something that I am not doing right in the formula.

Eg : if I do a standard median formula for 9 accounts above, I get a differnt median number as compared to the above formula event after using (CTRL+Shift+Enter). Its off by 400

Any advice?
 
Hi,

I assume you mean the 9 account called 'Accountant'. I get exactly the same result using

=MEDIAN(C5:C13)

as I do with

=MEDIAN(IF(B2:B5000=B5,C2:C5000))

Are you sure there isn't another account somewhere in the data being picked up by the ARRAY formula?
 
You must log in or register to reply here.
Back
Top