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

judas

Board Regular
Joined
Mar 19, 2005
Messages
60
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 :)!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Mar 19, 2005
Messages
60
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%
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,038
Messages
5,466,164
Members
406,471
Latest member
tsou88

This Week's Hot Topics

Top