Getting the correct SUM answer

[txheart]

Good morning,

I am having a lot of trouble with the formulas in a spreadsheet and
can't quite figure out how to word what it is that I need. Hopefully
this will at least come close enough to get a little help. =/

=MIN (b4*12-25%,b4*12-500) + MIN(c4*12-25%,c4*12-500) fills in the cell
with the correct answer, but it's not quite the answer I need. Let me
illustrate:

The first part of the formula is to take b4 times 12 minus 25% --if
this amount is less than or equal to 500 then use the total. If it is
more than 500 then 500 should be used.
(b4*12-25%)
234.25 * 12 = 2811 - 25% = 702.75 Since this amount is more than 500,
500 should be used instead. ~~this is actually the amount that I want
to show in the cell, the amount saved~~
2811 - 500 = 2311.00 ~~this is the answer that is showing up in the
cell, which is not quite what I need. ~~

The formula seems to be going one step farther than I need it to go,
but it's doing exactly what the formula tells it to do. I can't figure
out how to tell the formula to give me the saved amount. Help?

I did ask this question yesterday, but my question was more confusing
than the formula I'm trying to get. I hope this one clears up what I
need a little better. Thank you!

 

[Sandy Mann]

Fred Smith explains earlier in your other thread that b4*12-25% is not
taking 25% of the multiplication.

Unless I am still not understanding you try:

=MIN(B4*12*25%,500,B4*12-500)+MIN(C4*12*25%,500,C4*12-500)

The B4*12*25% returns the 702.25 you quote and B4*12-500 returns 2311 as you
say but the addition of the 500 causes 500 to be returned by that part of
the formula. Is that what you want?

It would have been better if you had remained in the same thread.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk