Tough number crunch!
 

I would like to average out some numbers. Say B1 had a value of 2, and B2 had
a value of 25, and B3 had a value of 4. I would like to have B2 to be an
average number between the other two. BUT, I only want it to be changed if it
is greater than a 500% difference between the TWO numbers (B1-B3).

So, Take value 1 compare it to value 2...If it is under 500% diff, go to
the next one(value B2 -B3). If it is OVER 500% then it needs to check value 3
to see if is also a difference of greater than 500% if indeed it is, value 2
needs to be changed to an average between 1&3..if it is less than 500% , no
changes are made. Once done I need to compare value 2 to value 3, and so on
and so on...

Tough one for me anyhow....

Tough number crunch!
 

You are looking at three cells B1, B2 and B3
You want a formula to change B2 based on B2's value?
Not possible
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Arty Morty" <Arty wrote in message
...
I would like to average out some numbers. Say B1 had a value of 2, and B2
had
a value of 25, and B3 had a value of 4. I would like to have B2 to be an
average number between the other two. BUT, I only want it to be changed if
it
is greater than a 500% difference between the TWO numbers (B1-B3).

So, Take value 1 compare it to value 2...If it is under 500% diff, go to
the next one(value B2 -B3). If it is OVER 500% then it needs to check
value 3
to see if is also a difference of greater than 500% if indeed it is, value
2
needs to be changed to an average between 1&3..if it is less than 500% ,
no
changes are made. Once done I need to compare value 2 to value 3, and so
on
and so on...

Tough one for me anyhow....

Tough number crunch!
 

No, I have thousands of cells. All filled with numbers. I want to take out
the spikes to create a smoother line chart.

"Bernard Liengme" wrote:

You are looking at three cells B1, B2 and B3
You want a formula to change B2 based on B2's value?
Not possible
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Arty Morty" <Arty wrote in message
...
I would like to average out some numbers. Say B1 had a value of 2, and B2
had
a value of 25, and B3 had a value of 4. I would like to have B2 to be an
average number between the other two. BUT, I only want it to be changed if
it
is greater than a 500% difference between the TWO numbers (B1-B3).

So, Take value 1 compare it to value 2...If it is under 500% diff, go to
the next one(value B2 -B3). If it is OVER 500% then it needs to check
value 3
to see if is also a difference of greater than 500% if indeed it is, value
2
needs to be changed to an average between 1&3..if it is less than 500% ,
no
changes are made. Once done I need to compare value 2 to value 3, and so
on
and so on...

Tough one for me anyhow....

Tough number crunch!
 

Okay, top of my head stuff now, as it's getting late:

You could have a MAX function in column D to find the highest value of
the three, and a MIN function in column E to find the lowest. Then in
column F a CHOOSE function to get the third value. The third value
obviously sits in between the other two, so in two more columns you
could measure the difference between this and the highest and lowest
values - maybe this is the starting point to determine if one of the
numbers (it can only be either the max or the min vlaue) needs to be
adjusted.

Too late for me to elaborate ... just some thoughts ...

Hope this helps.

Pete