Calculation of mean excluding target subject

Y

Yumi

Hi,
This is the situation - Let say I have 10 firms (firm 1 to firm 10).
In order to calculate value for firm 1, I need to compute mean for fir
2 to 10; and in order to calculate value for firm 2, I need t
calculate mean for firm 1, 3 to 10. Meaning: In order to calculat
value for firm x, I have to exclude firm x in calculating the mean. Ho
can I do this?

Many thanks
 
F

Frank Kabel

Hi
if your firm name is in column A and column B contains the values use
the following array formula (entered with CTRL+SHIFT+ENTER):
=MEAN(IF(A1:A10<>"firmx",B1:B10))
 
R

Ron Rosenfeld

Hi,
This is the situation - Let say I have 10 firms (firm 1 to firm 10).
In order to calculate value for firm 1, I need to compute mean for firm
2 to 10; and in order to calculate value for firm 2, I need to
calculate mean for firm 1, 3 to 10. Meaning: In order to calculate
value for firm x, I have to exclude firm x in calculating the mean. How
can I do this?
Click to expand...


Assume your individual Firm Means are in B1:Bn and are named "FirmMeans"

In C1 place the formula: =(SUM(FirmMeans)-B1)/(COUNT(FirmMeans)-1)

and copy/drag it down as far as needed. The B1 will change as you drag down
the formula to exclude that value.


--ron
 
Y

Yumi

Thanks to Frank and Ron..
It does help!!

But, need more help.

The same sample, but now I need to compute median and harmonic mean
Still need to exclude the target firm.

Many thanks in advance
 
R

Ron Rosenfeld

Thanks to Frank and Ron..
It does help!!

But, need more help.

The same sample, but now I need to compute median and harmonic mean.
Still need to exclude the target firm.

Many thanks in advance.
Click to expand...

Just use Frank's formula with the appropriate function.


--ron
 
Top