Forum Discussion

MNaguib's avatar
MNaguib
Copper Contributor
Oct 27, 2025

I need formula that solve the problem that the amount allocated exceeded the budget

https://docs.google.com/spreadsheets/d/1w3XKSwphQ0lu4xvDXbjrPhi4oSzIcny8/edit?usp=drive_link&ouid=106705750453392166520&rtpof=true&sd=true

excel

1 Reply

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Oct 27, 2025

    So if I'm reading it right you want the $$ distribution each month to be propotional to the GFA ratio for that property but over the course of the year that sum of distributions can't exceed the Budget for that property.  But if you reduce the payment to a property to prevent it from exceeding the budget then the GFA will not maintain that ratio and the total allocation that month will not be the full amount.  You could reduce all of the distributions to keep the GFA ratio and make sure none exceed budget then the total distributions will be much less.  If you increase the distributions to properties not limited by the budget then the GFA ratios will be that much more incorrect.

    one approach might be to use the lesser % of either GFA ratio or Budget ratio:

    =F$4*min($C6/$C$23, $D6/$D$23)

    but it doesn't checksum to zero on either axis but is more like a pseudo compromise.

    otherwise you need to better define the exact logic you want (i.e. which of the 'rules' can bend/break)

Resources