# Don't Exceed Certain Amount In Calculation

• Good morning again,

I got help here yesterday and thought that took care of the worksheet that the boss wanted but I was wrong. He wants more formulas and I'm still stuck.

The MS State Tax Credit maximum per person, per year is \$500.00. I need to multiply the monthly credit allowance by 12, then multiply that by 25%. This answer must not exceed \$500.00. I need this formula for both the male and female. I can't figure out how to make the formula both a SUM and an IF.

EX:

(79-D22)*(D20*12*25%max \$500)+(83-F22)*(F20*12*25% max \$500)

Any chance someone could help me with this one?

• Re: If And Sum Formulas In Same Cell

Hi,

Not sure which part you want to be limited to 500 max but see if below helps:-

=(79-D22)*MIN(D20*12*25%,500)+(83-F22)*MIN(F20*12*25%,500)

Eric.

• Re: If And Sum Formulas In Same Cell

=if(MCA*12*.25>500,500,MCA*12*.25)

syntax is =if(Logical_Test,Value_if_True,Value_if_False)

Logical_Test: When MCA*12*.25 is greater than 500 "MCA*12*.25>500"
Value_if_True: "500"
Value_if_False: The Monthly Credit Allowance times 12 times .25 "MCA*12*.25"

Quote

I need to multiply the monthly credit allowance by 12 -TxHeart

Where MCA, the Monthly Credit Allowance, is where I assume your sum formula goes?

Also, you have posted proprietary information to this post and have not referenced the original post, could you please link it to this post.

Thanks,
-Dude

• Re: If And Sum Formulas In Same Cell

Thanks so much for your reply, Eric. Unfortunately that formula did not give me the answer I need. Let me try to explain better what it is that we are trying to do...

234.25 * 12 = 2811.00 * 25% (the result must not be greater than 500) = 2311 * 20 = 46220.00

D20 * 12 = D32 * 25% (this number must not be greater than 500) = 2311 * (79-59) = final answer I need

Does that make it clearer? The 500 maximum is the times 25%. In this example 234.25*12*25% is more than 500, therefore 500 must be used. I used the formula you gave me but am not getting the correct totals. I sure wish I was better at these formulas than this, but I certainly see the need to get that way!

Thank you for help and I sure wish it had worked.

Yesterday's request for help can be found here

• Re: If And Sum Formulas In Same Cell

Quote

(D21*12)*F23+(E21*12)*G23 with the paranthesized (is that even a word?) sections being what I need to cap at \$500. Can anyone help me with this please?

-From your first post

I am still not understanding fully, as I am slow, but let me take another crack at this...

=(79-D22)*(if(D20*12*.25>500,500,D20*12*.25))+(83-F22)*(if(F20*12*.25>500,500,F20*12*.25))

Where 79, 83, D22, and F22 are values not explained but assumed to be a male value and a female value.

D20 and F20 are assumed male and female MCA's

Regards,
-Dude

• Re: If And Sum Formulas In Same Cell

Hi,

Dude - Am I missing a post here, I don't see where your quote comes from or the term MCA?

txheart - Afraid it doesn't really clarify things.

Quote

2811.00 * 25% (the result must not be greater than 500) = 2311

to me 2811 * 25% = 702.75 which is greater than 500 so = 500

unless you mean subtract 25% or 500 whichever is lower? That would equal 2311?

Eric

• Re: If And Sum Formulas In Same Cell

Insured's monthly premium is 234.25, multiplied by 12 to get the yearly premium = 2811
subtract 25% up to a max of 500 = 2311
multiply by the number of years left in life up to avg lifespan of male, 20 = 46220
(D20*12) = 2811
(D20*12-25%up to 500) = 2311
(D20*12-25%up to 500)*(79-D22) = 46220

Insured's monthly premium is 234.25, multiplied by 12 to get the yearly premium = 2811
subtract 25% up to a max of 500 - 2311
multiply by the number of years left in life up to avg lifespan of female, 24 = 55464
(F20*12) = 2811
(F20*12-25%up to 500) = 2311
(F20*12-25%up to 500)*(79-F22) = 46220

Add his total to her total for grand total. My goodness, I sure hope that helps. Heh, I'm confusing myself now.

• Re: If And Sum Formulas In Same Cell

Hi again,

Dude - Found the other post.

txheart - For follow on questions it helps to provide a link.

If it is subtract 25% or 500 whichever is lowest then the following should do it:-

Code
``=(79-D22)*MIN(D20*12-25%,D20*12-500)+(83-F22)*MIN(F20*12-25%,F20*12-500)``

You could also use Ifs as per Dudes suggestion.

Eric

Edit : The previous post http://www.ozgrid.com/forum/sh…d.php?p=427888#post427888

and why haven't you used shg or sicarii's suggestions?

• Re: If And Sum Formulas In Same Cell

Uhm, I did use Sicarii's suggestion. It's in my worksheet right now, but I still needed more and didn't know how to put the two together. Which is what all of this is doing. Sicarii's suggestion was absolutely perfect for the part that it answered.

I just plugged your suggested formula into my cell and hit enter... wooohoo! Thank you so much Eric, it's perfect! Now to study it so that I can figure out what you did...

• Re: If And Sum Formulas In Same Cell

=(79-D22)*(D20*12-(if(D20*12*.25>500,500,D20*12*.25)))+(83-F22)*(F20*12-(if(F20*12*.25>500,500,F20*12*.25)))

Where
D22 is the age of the male
F22 is the age of the female
D20 is the male monthly premium
F20 is the female monthly premium

if(D20*12*.25>500,500,D20*12*.25) is to determine the Tax Credit capped at 500, or "Monthly Credit Allowance", as understood above, Annualized

Regards,
-Dude

Ah, Didn't see that. Good job Eric :cheers:

• Re: If And Sum Formulas In Same Cell

Both give the same result Dude :cheers:

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!