Formula with cap

    Hi All,

    Struggling with a formula if any body could help please?


    What I need is a formula for cells B7, B8 & B7 and the criteria is as follows:


    Values in these cells needs to be the percentage of the values in B3, B4 & B5 respectively, which is the easy bit. The total sum of cells B7, B8 & B7 must not exceed 100% of the value of cell B1.

    Also if the sum of cells B3, B4 & B5 add to less than 100% then cells B7, B8 & B7 still need to total B1.


    So in summary B7, B8 & B7 always need to add up to B1 but split by the percentages of B3, B4 & B5, which can be under or over 100%.


    Thanks to whoever accepts the challenge!!

    Hi,


    When you say :

    Quote


    Values in these cells needs to be the percentage of the values in B3, B4 & B5 respectively

    Do you mean a Percentage of each Percentage ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Like 1

    How do you want to allocate any over/under amounts? If it's pro rata across all three, then you just need to scale the percentages in each cell accordingly. So in your picture, they add up to 110% so you'd divide each percentage by 110% before multiplying by B1.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Like 1
    Quote from DANGPS

    Basically ignore them.

    That doesn't really make sense. You have to do something about them or your totals will be too high/low. The question I asked is what do you want to do about them? Given the example you posted, what should be the outputs in each cell?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Quote from rory

    That doesn't really make sense. You have to do something about them or your totals will be too high/low. The question I asked is what do you want to do about them? Given the example you posted, what should be the outputs in each cell?


    Sorry, if the amount is over then the pro-rata method you mention above works perfect, if the amounts are under then it needs upping to 100%.

    Rory is .... 110 % right ... ;)


    Attached is a test file where you can add a few examples in order to illustrate the scope of your objective ...


    Meanwhile ... Cells B3,B4 and B5 have been capped ...

    In B7: =ROUND($B$1*B3/SUM($B$3:$B$5),2)

    B8: =ROUND($B$1*B4/SUM($B$3:$B$5),2)

    B9: =B1-SUM(B7:B8)


    the formula in B9 just ensures there are no rounding issues.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Like 1

    DANGPS


    Once you have added several explicit examples to the worksheet, feel free to attach the file back to the Forum

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Like 1
    Quote from rory

    In B7: =ROUND($B$1*B3/SUM($B$3:$B$5),2)

    B8: =ROUND($B$1*B4/SUM($B$3:$B$5),2)

    B9: =B1-SUM(B7:B8)


    the formula in B9 just ensures there are no rounding issues.

    Have been testing this for a while and is working exactly like we wanted, thanks for your help on this.

Participate now!

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

Register Yourself Login