Wrong amount formula

[George]

Excel 2007
The amount to this formula should be 363.04
=(C4*2%+(C4>15651.89)*(C4-15651.89)*5%)
where C4 = 16651.89
I need to find 2% of C4 up to 15651.89 and anything over that (difference)
at 5%

 

[bapeltzer]

Your formula is picking up 2% of everthing plus another 5% of the excess;
thus the excess contributes a total of 7%. I think you want
=(C4*2%+(C4>15651.89)*(C4-15651.89)*3%)

 

[Niek Otten]

=2%*MIN(C4,15651.89)+5%*MAX(0,C4-15651.89)

 

[Bernard Liengme]

You are add the 2% twice. What you need is
=C4*2%+(C4>15651.89)*(C4-15651.89)*3%
or
=MIN(C4,15651.89)*2%+(C4>15651.89)*(C4-15651.89)*5%

Odd how the same question popped up twice today
best wishes

 

[joeu2004]

The amount to this formula should be 363.04

What value do you get instead?

=(C4*2%+(C4>15651.89)*(C4-15651.89)*5%)
where C4 = 16651.89

That should result in about 383.04.

I need to find 2% of C4 up to 15651.89 and
anything over that (difference) at 5%

Then you have the wrong formula. Ostensibly, you want:

=min(C4,15651.89)*2% + max(0,C4-15651.89)*5%

That does indeed result in about 363.04.

Notice that I say "about" this or that. The actual value is 363.0378. It
would behoove you to use ROUND to avoid surprises in later calculations.
Assuming that you have ROUND'd C4, the above formula should be:

=round(min(C4,15651.89)*2% + max(0,C4-15651.89)*5%, 2)

Alternatively:

=round(C4*2% + max(0,C4-15651.89)*3%, 2)

where 3% is 5% - 2%.

 

[George]

Thank you for your help, your formula worked perfect. Sorry about the double
posting. Can I add something???
The baseline is in B4
That formula is in a Totals column, N4
The 16651.89 is in a January column, C4
Feb. is in C5, Mar. in C6... each month the sales amount will change and i
would like to have the total column add all these up using this new formula.

 

[George]

One more issue with the formula is if C4 is left blank, the 2% still shows up
in the total column - this total column should also be blank