please help! my brain isnt brain-ing today

[LIZEEY]

i need to create a formula that will return me different value based on percentage met ie; under 90% = 1 / 90-95% = 2 / 95-100% = 5 / 100+% = 10 my mind is a complete blank1 i have tried IF/AND but just cant get it to work for all 4 scenarios -( please help

 

[Dave Patton]

try the following; use the one that you prefer

T202402a.xlsm
	A	B	C	D	E
1
2		0%	1
3		90%	2
4		95%	5
5		100%	10
6
7		92%	2	2	2
1a

Cell Formulas
Range	Formula
C7	C7	=LOOKUP(B7,B2:C5)
D7	D7	=LOOKUP(B7,{0,1;0.9,2;0.95,5;1,10})
E7	E7	=IF(B7>=1,10,IF(B7>=0.95,5,IF(B7>=0.9,2,IF(B7>=0,1,0))))

 

[dreid1011]

Perhaps this:

Book1
	A	B
1	0.5	1
2	0.75	1
3	0.89	1
4	0.9	2
5	0.93	2
6	0.95	5
7	0.97	5
8	0.99	5
9	1	10
10	1.01	10
11	1.09	10
12	0.949	2
Sheet1

Cell Formulas
Range	Formula
B1:B12	B1	=LOOKUP(A1,{0,0.9,0.95,1},{1,2,5,10})

 

[LIZEEY]

Perhaps this:

Book1
	A	B
1	0.5	1
2	0.75	1
3	0.89	1
4	0.9	2
5	0.93	2
6	0.95	5
7	0.97	5
8	0.99	5
9	1	10
10	1.01	10
11	1.09	10
12	0.949	2
Sheet1

Cell Formulas
Range	Formula
B1:B12	B1	=LOOKUP(A1,{0,0.9,0.95,1},{1,2,5,10})

thank you for this! ive never used lookup like this before and it looks like it could be really useful! i have tried adapting it for my sheet but its not working, what am i doing wrong - my data is very specific no it needs to be exact to the .00%

 

[LIZEEY]

thank you! i have tried the bottom and changed to match my data but the 0.5 bracket cells are returning 0 what am i doing wrong?

=IF(AI16>=100%,2,IF(AI16>=95%,1,IF(AI1>=90%,0.5,IF(AI16<90%,0,0))))

 

[LIZEEY]

try the following; use the one that you prefer

T202402a.xlsm
	A	B	C	D	E
1
2		0%	1
3		90%	2
4		95%	5
5		100%	10
6
7		92%	2	2	2
1a

Cell Formulas
Range	Formula
C7	C7	=LOOKUP(B7,B2:C5)
D7	D7	=LOOKUP(B7,{0,1;0.9,2;0.95,5;1,10})
E7	E7	=IF(B7>=1,10,IF(B7>=0.95,5,IF(B7>=0.9,2,IF(B7>=0,1,0))))

​

thank you! i have tried the bottom and changed to match my data but the 0.5 bracket cells are returning 0 what am i doing wrong?

=IF(AI16>=100%,2,IF(AI16>=95%,1,IF(AI1>=90%,0.5,IF(AI16<90%,0,0))))

 

[dreid1011]

thank you! i have tried the bottom and changed to match my data but the 0.5 bracket cells are returning 0 what am i doing wrong?

=IF(AI16>=100%,2,IF(AI16>=95%,1,IF(AI1>=90%,0.5,IF(AI16<90%,0,0))))

The trick with LOOKUP is the lookup array should contain the lowest possible values in each bracket. So for the "under 90%", we use 0, then 90 for the 90-95 bracket.

 

[Joe4]

It probably means the entry in AI16 is really text and not a number.
What does this formula return?

=ISNUMBER(AI16)

By the way, your formula can be shortened/simplified to:

=IF(AI16>=100%,2,IF(AI16>=95%,1,IF(AI16>=90%,0.5,0)))

 

[Joe4]

HOLD ON!

I just noticed a typo in your formula:

=IF(AI16>=100%,2,IF(AI16>=95%,1,IF(AI1>=90%,0.5,IF(AI16<90%,0,0))))

I believe the part in red should also be "AI16"

 

[LIZEEY]

try the following; use the one that you prefer

T202402a.xlsm
	A	B	C	D	E
1
2		0%	1
3		90%	2
4		95%	5
5		100%	10
6
7		92%	2	2	2
1a

Cell Formulas
Range	Formula
C7	C7	=LOOKUP(B7,B2:C5)
D7	D7	=LOOKUP(B7,{0,1;0.9,2;0.95,5;1,10})
E7	E7	=IF(B7>=1,10,IF(B7>=0.95,5,IF(B7>=0.9,2,IF(B7>=0,1,0))))

try the following; use the one that you prefer

T202402a.xlsm
	A	B	C	D	E
1
2		0%	1
3		90%	2
4		95%	5
5		100%	10
6
7		92%	2	2	2
1a

Cell Formulas
Range	Formula
C7	C7	=LOOKUP(B7,B2:C5)
D7	D7	=LOOKUP(B7,{0,1;0.9,2;0.95,5;1,10})
E7	E7	=IF(B7>=1,10,IF(B7>=0.95,5,IF(B7>=0.9,2,IF(B7>=0,1,0))))

Dave Patton i could kiss you! used the first option and its fabulous thank you so much!