Closest Number to Either 30%, 50% or 100%

[judas]

Hi! I Am trying to analyze some data. If a certain number is either 30%, 50% or 100% (exact match) then i'd chose it. (I already did this test). If instead is not exact, I would like to compare the numbers and chose the closest to either of those %. I would create several columns with complex formulas, but I'm thinking there's an easier way I haven't thought off. Data is Layed out like this.

COL1 COL2 COL3 COL4
43,09% 42,78% 48,22% 47,87%

Also, I would like to return not the closest % but the Column Heading..in that example I would like it to return COL3, as 48,22% is closest to 50%. (It is not very likely that a value is 75%; equidistant to 50% and 100%.)

Any help is very appreciated. Please ask if I wasn't clear enough...I'll try to do better with my English next time !

 

[Andrew Poulsom]

To return the column heading for the number closest to 50%:

=INDEX(A1:D1,1,MATCH(MIN(ABS(A2:D2-50%)),ABS(A2:D2-50%),FALSE))

which must be confirmed with Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surround this array formula with curly braces {}.

 

[judas]

Thanks for the prompt reply!!!

That answers the question to find a close match to 50%...the thing is it can be closer to 100% or to 30% than to 50%, in which case this wouldn't work. I need to test at the same time to 30%, 50% and 100%...I need the answer per row:

COL1 COL2 COL3 COL4
43,1% 42,8% 48,2% 47,9%
86,2% 85,6% 96,4% 95,7%
85,8% 85,2% 86,4% 85,8%