Excel help again

[tbourner]

Another one for the experts.

 

Is there any way to make a formula that gives the value from a group of values, that is closest to the average?

 

The "=AVE()" formula gives the exact average, but I want to perform another function on one particular value in a range that is the average value, and it won't recognise it because the exact average is between 2 of the values.

[Todd]

=INDEX(units,MATCH(MIN(ABS(units-AVERAGE(units))),ABS(units-AVERAGE(units)),0))

 

Which fried my brain, try this and look at the MATCH option, should give you what you need.

 

http://www.cpearson.com/excel/lookups.htm

[tbourner]

Thanks dude, brain fried now as well!!

Got one of them to work, but can't add the formula to another formula because it's an array index thingy.

Have to have another look at work tomorrow.

[Todd]

See the attachment I've just thrown together, should help.....I say should

[tbourner]

Here's the one I've got working last night, I haven't made it nice and easy to understand like you have though!! So don't bother looking at the other 3 sheets just the first one.

 

We test HDDs in chassis, so I've put a cell in (C2) that allows the user to add the number of drives tested. This made it harder because the test range is always changing!

The results then go in E:G, numbers are I/O per second, so come up as 134.0233145 for example.

Column I is the average of the 3 runs for each drive.

Column K then tells which of the drives is the MIN, MAX or AVE running drive.

I've used column T as the drive average result, minus the average of all average results ( ). So the lowest value in T is the one closest to the average. Column K refers to this to get the AVE result, I've even made T in white text so (simple) users won't know it's there.

 

 

It's not correct but it works, feels like a bodge though.

[Todd]

It's not correct but it works, feels like a bodge though.

 

If it works then it's correct