Help please with simple formula please

formratings

Board Regular
Joined
Apr 5, 2015
Messages
98
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 21, 2020
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
May 16, 2017
Messages
771
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
Joined
Apr 5, 2015
Messages
98
sorry I didn't reply earlier I only just remembered to check on here
thank you that worked perfectly
 

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,553
Members
416,923
Latest member
jarri

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top