average with max

[Bryce]

I have a table of numbers, some cells are blank, and I'd like to calculate
the average (excluding blanks). Easy so far...

However some of the numbers are "spikes", and rather than discarding these,
I'd like to set a value for them, to be included in the average.

I could have a second table with an IF function replacing any numbers too
high, but would prefer not to if possible

Any help much appreciated

Thanks heaps

-Bryce

 

[carlo]

One possibility:
(Matrix-Formula, ctrl+shift+enter to apply)
=SUMPRODUCT(IF((A1:A23)>B25,B25,A1:A23))/SUMPRODUCT(((A1:A23)>0)*1)
where A1:A23 is your range of data
B25 is your spike value

hth

Carlo

 

[ShaneDevenshire]

Hi Bryce,

This probably won't do it but if it will then it's a nice solution - use
TRIMMEAN

If this doesn't meet your needs, then we need to know how you decide a
number is too large or too small and what value you want to substitute in
these cases. Further do you want to actually replace the spikes or have a
new range that shows the adjusted values but retain the original data?

 

[T. Valko]

Assumptions:

Spikes are any value >=100. Spikes should be "capped" at 100.

C1 = spike value = 100

A1:A10 = numbers to average

Try this array formula** :

=AVERAGE(IF(ISNUMBER(A1:A10),IF(A1:A10>=C1,C1,A1:A10)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Bryce]

Excellent, worked perfectly

Thanks heaps
-Bryce

One possibility:
(Matrix-Formula, ctrl+shift+enter to apply)
=SUMPRODUCT(IF((A1:A23)>B25,B25,A1:A23))/SUMPRODUCT(((A1:A23)>0)*1)
where A1:A23 is your range of data
B25 is your spike value

hth

Carlo

 

[Bryce]

Thanks, now I'll have to decide which formula looks nicer

-Bryce

 

[Bryce]

Hi Shane

Carlo's formula seemed to work fine. Just to clarify: I had a couple of
tabs for various projects showing a table each of numbers. I then had a
summary tab listing the projects, the average, and now average with spikes
capped. The cap value is now a cell on the summary page which allows me to
play with it, leaving the original data.

Thanks heaps

-Bryce

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks, now I'll have to decide which formula looks nicer

-Bryce

 

[carlo]

Your welcome, thanks for the feedback

Carlo

You're welcome!

--
Biff
Microsoft Excel MVP






- Show quoted text -