average with max
 

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

average with max
 

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

On Jan 16, 2:24*pm, Bryce wrote:
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

average with max
 

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?
--

Cheers,
Shane Devenshire


"Bryce" wrote:

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

average with max
 

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:A 10)))

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

--
Biff
Microsoft Excel MVP


"Bryce" wrote in message
...
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

average with max
 

Excellent, worked perfectly

Thanks heaps
-Bryce

"carlo" wrote:

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

On Jan 16, 2:24 pm, Bryce wrote:
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

average with max
 

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

-Bryce

"T. Valko" wrote:

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:A 10)))

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

--
Biff
Microsoft Excel MVP


"Bryce" wrote in message
...
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

average with max
 

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

"ShaneDevenshire" wrote:

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?
--

Cheers,
Shane Devenshire


"Bryce" wrote:

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

average with max
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Bryce" wrote in message
...
Thanks, now I'll have to decide which formula looks nicer :-)

-Bryce

"T. Valko" wrote:

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:A 10)))

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

--
Biff
Microsoft Excel MVP


"Bryce" wrote in message
...
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

average with max
 

Your welcome, thanks for the feedback

Carlo

On Jan 17, 3:17*am, "T. Valko" wrote:
You're welcome!

--
Biff
Microsoft Excel MVP

"Bryce" wrote in message

...



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

-Bryce

"T. Valko" wrote:

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:A 10)))

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

--
Biff
Microsoft Excel MVP

"Bryce" wrote in message
...
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- Hide quoted text -

- Show quoted text -