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 -