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