select from a list of values

[sotiris_s]

in each row in an excel sheet, i have up to 8 values, {in cell A1,B1...H1} i
need to select the two values that are close to the geomean of them, and to
mark them? or best, to show them at a new pair of cells.
the values are possitive numbers with no decimal places.

thank you.

 

[Biff]

Hi!

Try this formula entered as an array with the key combo of CTRL,SHIFT,ENTER:

=INDEX($A$1:$H$1,MATCH(SMALL(ABS($A$1:$H$1-GEOMEAN($A$1:$H$1)),COLUMN(A:A)),ABS($A$1:$H$1-GEOMEAN($A$1:$H$1)),0))

Then just drag copy across to get the second value.

Biff

 

[sotiris_s]

thank you for the post, i copied the formula and entered it as an array
CTRL,SHIFT,ENTER , but it seams that the formula is invalid! the excel
returns that it has an error! begging from the match function....
thank you again

 

[Bob Phillips]

Worked fine for me, suggest trying again.

 

[sotiris_s]

it seams that at the =INDEX($A$1:$H$1,MATCH it needs question mark... and
down the formula....the same pls its a petty i can't make it work.... but
thank you for the post! You are unbeleivable people extraordinary!, from
Athens Greece thank you

 

[Bob Phillips]

Did you try it replacing the comma (,) with ; for Greek Excel?

 

[sotiris_s]

i did it! i replace the comma with the ; for the Greek keyboard/region
settings....(excel 2002 eng) the case is that i can't get it work....pls
advice it drives me crazy i have the solution and i can't get it to work....
thanks anyway....

 

[Bob Phillips]

send me the workbook and I will see?