Wrong amount formula

Excel 2007
The amount to this formula should be 363.04
=(C4*2%+(C415651.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%

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%+(C415651.89)*(C4-15651.89)*3%)

"George" wrote:

Excel 2007
The amount to this formula should be 363.04
=(C4*2%+(C415651.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%

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"George" wrote in message
...
Excel 2007
The amount to this formula should be 363.04
=(C4*2%+(C415651.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%

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

Odd how the same question popped up twice today
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"George" wrote in message
...
Excel 2007
The amount to this formula should be 363.04
=(C4*2%+(C415651.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%

"George" wrote:
The amount to this formula should be 363.04

What value do you get instead?

=(C4*2%+(C415651.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%.

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.

" wrote:

"George" wrote:
The amount to this formula should be 363.04

What value do you get instead?

=(C4*2%+(C415651.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%.

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

" wrote:

"George" wrote:
The amount to this formula should be 363.04

What value do you get instead?

=(C4*2%+(C415651.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%.