Help please with simple formula please

[formratings]

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

 

[jasonb75]

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]

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

 

[rlv01]

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]

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