If Statement assigning values

[ddice502]

Hello all,

I'm attempting to create an if statement looks at a number finds the percentage within the range and returns the value. Any help wold be great. Using Excel 2007. Windows.

	Ranges
Value	High	Low
1	107.1%
1		79.9%
2	84.9%	80.0%
2	107.0%	105.6%
3	92.5%	85.0%
3	104.0%	105.5%
4	9590.0%	92.6%
4	103.9%	102.1%
5	102.0%	96.0%

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[JASONRUBALCAVA]

what number is it looking at.. and what value do you want returned .. please give and example

 

[ddice502]

what number is it looking at.. and what value do you want returned .. please give and example

If I have 93% I would want a value returned of 5. If I have 106% I would want a value returned of 2.


Thanks in advance.

 

[Aladin Akyurek]

If I have 93% I would want a value returned of 5. If I have 106% I would want a value returned of 2.


Thanks in advance.

Why does 93% correspond to 5, not to 4?

 

[kendel]

Hello, I've no training or education in Excel but this worked for me:
Number in A1, formula in A2: IF((AND(A1>C3,A1<b3)),a3,if(and(a1>C4,A1<b4),a4,"na"))<b3)),a3,if(and(a1><b4),a4,"na")) keep="" nesting="" ifs="" for="" each="" additional="" row="" of="" "value"
continue nesting ifs for each row in "Value"</b4),a4,"na"))></b4),a4,"na"))<b3)),a3,if(and(a1></b3)),a3,if(and(a1>
<b3)),a3,if(and(a1><b4),a4,"na")) keep="" nesting="" ifs="" for="" each="" additional="" row="" of="" "value"
"Value": A3:A10, or whatever your range is
High: B3:B10
Low: C3:C10
sorry it keeps dropping part of the formula, editing not help
</b4),a4,"na"))></b3)),a3,if(and(a1>'IF((AND(A1>C3,A1<b3)),a3,if(and(a1>C4,A1<b4),a4,"na"))< html=""></b4),a4,"na"))<></b3)),a3,if(and(a1><b3)),a3,if(and(a1>C4,A1<b4,a4,"na"))< html=""></b4,a4,"na"))<></b3)),a3,if(and(a1>
<B3)),A3,IF(AND(A1>C4,A1<B4),A4,"NA"))

 

[ddice502]

Why does 93% correspond to 5, not to 4?

Sorry, my mind is all scrambled trying to figure this out. 93% should return a value of 4.


Also "9590.0%" should be 95.9%.

Been a long difficult day my friends.

 

[kendel]

If((and(a1>c3,a1<b3)),a3,

 

[Aladin Akyurek]

Sorry, my mind is all scrambled trying to figure this out. 93% should return a value of 4.


Also "9590.0%" should be 95.9%.

Been a long difficult day my friends.

Suspected that much, but certainty is important if it can be obtained...

Let F2 house a percentage value like 93%.

G2, control+shift+enter, not just enter:

=INDEX($A$2:$A$10,MIN(IF($F2>=$C$2:$C$10,IF($F2<=$B$2:$B$10,
    ROW($A$2:$A$10)-ROW($A$2)+1)))

 

[kendel]

i must be doing something wrong trying to share the formula, its nested ifs for each row in your Values column

or what Al said