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


    What about this...


    =(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:

Participate now!

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

Register Yourself Login