formratings
Board Regular
- Joined
- Apr 5, 2015
- Messages
- 119
- Office Version
- 2019
- Platform
- Windows
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
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