formratings

Board Regular
sorry but I am petty novicey at Excel and would very much appreciate a bit of help please
I have this formula, which part of means any minus numbers = 0

=IFERROR(AVERAGE(dataformlatest!AJ6,dataformlatest!BH6,dataformlatest!AI6,dataformlatest!BF6)/((dataformlatest!AJ6<>0)+(dataformlatest!BH6<>0)+(dataformlatest!AI6<>0)+(dataformlatest!BF6<>0)),0)*6*CP6

and in the cells the figures are
AJ=56
BH=0
AI=0
BF=0
CP=0.5

if I do AVERAGE I have 56,0,0,0 = 14 * 6 = 84* 0.5 = 42
BUT I want to use MEDIAN, the majority of cells will have no 0's and the odd cells will have two or maybe three 0's
and this skews the results

e.g =IFERROR(MEDIAN(dataformlatest!AJ6,dataformlatest!BH6,dataformlatest!AI6,dataformlatest!BF6)/((dataformlatest!AJ6<>0)+(dataformlatest!BH6<>0)+(dataformlatest!AI6<>0)+(dataformlatest!BF6<>0)),0)*6*CP6

if I do MEDIAN I have 56,0,0,0 which gives me as a result 0, when in this case I would want the result to be 56

I would like to use MEDIAN but where 0's don't count unless there is another way or function

I hope I have explained this well and many thanks in advance for your help
cheers dave

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jasonb75

Well-known Member
Try this, must be array confirmed with Shift Ctrl Enter.

=MEDIAN(IFERROR(--TEXT(CHOOSE({1,2,3,4},AJ6,BH6,AI6,BF6),"0.00;;;"),""))

Jogender singh

New Member
answer 56 only possible if values are 56,0,0 or 56,4,0,0 for average
median formula not seems good

rlv01

Well-known Member
Perhaps you could test for the MEDIAN = 0 case and use SUM instead:

=IFERROR(IF(MEDIAN(AJ6,BH6,AI6,BF6)=0,SUM(AJ6,BH6,AI6,BF6),MEDIAN(AJ6,BH6,AI6,BF6))/((AJ6<>0)+(BH6<>0)+(AI6<>0)+(BF6<>0)),0)*6*CP6

formratings

Board Regular
sorry I didn't reply earlier I only just remembered to check on here
thank you that worked perfectly

Replies
4
Views
114
Replies
12
Views
182
Replies
3
Views
33
Replies
1
Views
150
Replies
0
Views
80

1,129,839
Messages
5,638,652
Members
417,040
Latest member
EC1728

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back