Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excell-How to add the number of cells containing text? = a sum | Excel Worksheet Functions | |||
Generating (in a random order)each number once from a given number | Excel Worksheet Functions | |||
Change number format from text to number? | New Users to Excel | |||
vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) |