Nested IF() Alternative - Criterias

[lattiz]

Hello all,

Being my first post I would like to say thanks for the breadth of knowledge I have gained from this site and its patrons.

This is the situation I have. Given a value in a cell, say A1, I would like to return in B1 a value from column II below where A1 follows the criteria listed under column I (i.e. if A1 were 26%, B1 would populate with 20%).

I
>30%
25%< <30%
20%< <25%
15%< <20%
10%< <15%
<10%

II
0%
20%
40%
60%
80%
100%

I have thought of various ways of doing this (nested ifs, arrays, db) and was wondering if someone could throw out what they think is the CLEANEST solution to this problem.

Thanks much!

 

[Aladin Akyurek]

=LOOKUP(B1,{0;0.1;0.15;0.2;0.25;0.3},{1;0.8;0.6;0.4;0.2;0})

If so desired, the formula cell can be formatted as %.

 

[PA HS Teacher]

I had been trying to work with the vlookup. (couldn't quite get it to work for values greater than thirty.)

Aladin's solution is far more flexible, and in general far superior.

But for some reason on my next attempt my brain came up with the following:
(it only works because all of your bin sizes are in multiples of 5)

=CHOOSE(MIN(INT(A1/0.05)*(A1>=A3)+1,7),1,,0.8,0.6,0.4,0.2,0)

 

[lattiz]

Awesome, thanks.

 

[lattiz]

Aladin,

Is there anyway to make the contents of your formula dynamic (i.e. have them linked from cells)?

Thanks

 

[Aladin Akyurek]

Aladin,

Is there anyway to make the contents of your formula dynamic (i.e. have them linked from cells)?

Thanks

aaLOOKUP lattiz.xls
	B	C	D	E	F	G
1	26%	0.2	0.2		LookupTable
2					0%	100%
3					10%	80%
4					15%	60%
5					20%	40%
6					25%	20%
7					30%	0%
8
Sheet1

Select F2:G7, go to the Name Box on the Formula Bar, type LTable, and hit enter. Then use:

D1:

=LOOKUP(B1,LTable)