Unsure which to use: IF, IF AND or IF THEN Function

[KuraiChikara]

I need to show certain percentages if certain numbers come up. The formula can be in A1 and the number can be in B1. Example:

If B1 is equal to 2.9 or less put in a 0
if B1 is equal to 3 but less than 3.5 put 10%
if B1 is equal to 3.5 but less than 4.0 put 15%
if B1 is equal to 4 but less than 4.5 put 20%
if B1 is 4.5 or greater put 25%

I have a general idea of how to do it, I just havent been able to get anything to work.

 

[jtakw]

Hi,

You can do this:

Book1
	A	B	C	D	E	F
1	0.00%	0.00%	2.9		0	0.00%
2	10.00%	10.00%	3		3	10.00%
3	15.00%	15.00%	3.5		3.5	15.00%
4	20.00%	20.00%	4		4	20.00%
5	25.00%	25.00%	4.5		4.5	25.00%
6	25.00%	25.00%	5
7
8	^Format as Percentage
Sheet523

Cell Formulas
Range	Formula
A1	=LOOKUP(C1,{0,3,3.5,4,4.5},{0,0.1,0.15,0.2,0.25})
B1	=LOOKUP(C1,E$1:E$5,F$1:F$5)

Use A1 formula with Tiers Hard-coded,
Use B1 formula with Tiers in Table E1:F5

 

[Scott Huish]

Don't use IF at all, this is what VLOOKUP is for:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	4	20%						0	0
2								3	10%
3								3.5	15%
4								4	20%
5								4.5	25%
Sheet1

Cell Formulas
Range	Formula
B1	=VLOOKUP(A1,$H$1:$I$5,2,1)

 

[KuraiChikara]

These are great but my issue is why I included the "equal to but less than and equal to or greater than" information because the numbers I get can be in a range like 3, 3.1, 3.2, 3.41, etc. Those would all result in 10% since my second criteria was "if B1 is equal to 3 but less than 3.5 put 10%" .

A Vlookup could do that, I would just have to incorporate all of the possible values to look at, at and that's alot with what I have to do.

Thank you for your help though. That will help me in other things I have to do.

 

[jtakw]

These are great but my issue is why I included the "equal to but less than and equal to or greater than" information because the numbers I get can be in a range like 3, 3.1, 3.2, 3.41, etc. Those would all result in 10% since my second criteria was "if B1 is equal to 3 but less than 3.5 put 10%" .

A Vlookup could do that, I would just have to incorporate all of the possible values to look at, at and that's alot with what I have to do.

Thank you for your help though. That will help me in other things I have to do.

Did you even try the formulas given by myself and Scott? Our formula covers All those scenarios:

Book1
	A	B	C	D	E	F
1	0.00%	0.00%	2.9		0	0.00%
2	10.00%	10.00%	3		3	10.00%
3	15.00%	15.00%	3.5		3.5	15.00%
4	20.00%	20.00%	4		4	20.00%
5	25.00%	25.00%	4.5		4.5	25.00%
6	25.00%	25.00%	5
7	10.00%	10.00%	3
8	10.00%	10.00%	3.1
9	10.00%	10.00%	3.2
10	10.00%	10.00%	3.41
11	10.00%	10.00%	3.499999999
Sheet523

Cell Formulas
Range	Formula
A1	=LOOKUP(C1,{0,3,3.5,4,4.5},{0,0.1,0.15,0.2,0.25})
B1	=LOOKUP(C1,E$1:E$5,F$1:F$5)

 

[KuraiChikara]

I apologize, I've never seen your type of formula before and was waiting till I had more time to mess around with it. And it works. I'll figure out how it works later but it works, thank you very much.

 

[jtakw]

You're welcome, glad we can help.