Multiple IF Formulas

[AllisonMartin]

Looking for some suggestions of multiple if formulas in a cell.

Here's the general idea of what I'm trying to accomplish, looking for the formula for "Payout Bonus" to be calculated for me. When trying to nest multiple IF functions I keep getting an error. It needs to be equal to or greater than in the formula. If the actual sales are less than Tier 1, it needs to read "NO BONUS"


[TABLE="width: 841"]
<colgroup><col><col><col span="2"><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD] Tier 1 [/TD]
[TD] Tier 2 [/TD]
[TD] Tier 3 [/TD]
[TD] Tier 4 [/TD]
[TD][/TD]
[TD] ACTUAL SALES [/TD]
[TD][/TD]
[TD] PAYOUT BONUS [/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD][/TD]
[TD] $ 1,000.00[/TD]
[TD] $ 2,000.00[/TD]
[TD] $ 3,000.00[/TD]
[TD] $ 4,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Actual Sales will be populated with the amount, and depending on the tier, there will be a bonus associated with it.

Tier 1 Bonus - $ 350.00
Tier 2 Bonus - $ 400.00
Tier 3 Bonus - $ 500.00
Tier 4 Bonus - $ 750.00

Any guidance is appreciated.

 

[Joe4]

I helped someone with a question like this earlier today. To see an example of what this nested IF formula should look like, take a look at the last formula in my last post here: http://www.mrexcel.com/forum/excel-...-sheet-if-formula-my-excel-sheet-how-use.html

Try it and see if you can get it to work out for you. If not, post back your attempt and we'll help your through it.

 

[AllisonMartin]

Tried the following IF formula for the first 2 columns =IF(I5>=D5,350,"NO BONUS",IF(I5>=E5,400))

gave me the error too many arguments for this function.

?

 

[AhoyNC]

Try:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		Tier 1	Tier 2	Tier 3	Tier 4		ACTUAL SALES	PAYOUT BONUS
2	January	$1,000.00	$2,000.00	$3,000.00	$4,000.00		$3,222.00	$500.00
3
Sheet2

 

[AllisonMartin]

Tried that formula too, still got an error.

 

[AhoyNC]

What error msg. did it give. As you can see it worked without an error for me.
It would help if you posted the actual formula (references you used) so we might see where the problem is.

The issue with your formula in post 3 is: The IF function has a logical test then gives a True or False - IF(logical test,TRUE,FALSE) in your formula you gave it a 3rd argument after your No Bonus.

 

[Joe4]

Tried the following IF formula for the first 2 columns =IF(I5>=D5,350,"NO BONUS",IF(I5>=E5,400))

gave me the error too many arguments for this function.

Not only do you have too many arguments, but you are also going in the wrong direction.
The way that a nested IF statement works is that one it finds a true condition, it stops there. So If you had $5000, it would check your first condition and say that $5000 is greater than $1000, so it would stop there and assign $350 as the bonus.
You want to start from the top and work your way down, i.e. (assuming the amount you are checking is in cell A1):

=IF(A1>=4000,750,IF(A1>=3000,500,IF(A1>=2000,400,IF(A1>=1000,350,"NO BONUS"))))

You can replace those hard-coded values with cell references. I just did it that way because it makes it easier to see how it should be structured.