Allocation Order Formulas

KMH23

New Member
Joined
Oct 13, 2017
Messages
3
Hello,

I work for a real estate company that does not want to invest in property management software. Every property has a report that is set up in Excel to keep track of rent payments, etc.

If the tenant pays for storage or parking in addition to rent, then the payments must first be allocated to storage, then parking, then rent. Right now I manually break out every allocation for every unit which is very time consuming.

I would love to have a formula where I can enter the total payment and it will auto distribute in order. Does something like this exist?

The columns are broken out by market and what they should be paying:

Column F - Base Rent (what they should pay to be used as a reference for allocations)
Column G - Base Parking (what they should pay to be used as a reference for allocations)
Column H - Base Storage (what they should pay to be used as a reference for allocations)
Column I - Current Rent Payment (what I manually allocate based on payment received - allocate to here third)
Column J - Current Parking Payment (what I manually allocate based on payment received - allocate to here second)
Column K - Current Storage Payment (what I manually allocate based on payment received - allocate to here first)

I hope this makes sense.

Thanks,
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,461
Office Version
365
Platform
Windows
Welcome to the Board!

Where is the total Payment amount being entered (which column)?
What if their payment amount exceeds the Base rates for all three? What happens to the overage?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,461
Office Version
365
Platform
Windows
Assuming your data starts in row 2, and your payment is being entered into cell A2, here are formulas that I think should do what you want.

Cell K2: =MIN(A2,H2)
Cell J2: =MAX(MIN(A2-H2,G2),0)
Cell I2: =MAX(A2-J2-K2,0)

If you want to cap the value in I2 to not exceed the amount of base rent in F2 (if they pay more than all three combined), change the formula to this:
Cell I2: =MIN(MAX(A2-J2-K2,0),F2)
 

KMH23

New Member
Joined
Oct 13, 2017
Messages
3
Welcome to the Board!

Where is the total Payment amount being entered (which column)?
What if their payment amount exceeds the Base rates for all three? What happens to the overage?
Hi Joe,

Thank you for your reply. The total payment amount is currently being entered into Column I (Current Rent) and then the parking and storage is subtracted and added into the appropriate columns.

If the payment amount exceeds all three bases, the overage will go into the Current Rent - Column I.

I can create a new column to record payments if necessary. All payments begin on line 8.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,461
Office Version
365
Platform
Windows
So, the amount is being entered into column I, and then you want it to be allocated to the other columns, and column I to be updated as well.
That is going to require VBA, as cells in Excel can either have hard-coded amounts, or formulas, but never both simultaneously (so column I could not hold both your original value and formula to calculate what is left).
Is VBA acceptable? If so, you cannot enter the total payment into the same column where you want the rent calculated.
 

KMH23

New Member
Joined
Oct 13, 2017
Messages
3
So, the amount is being entered into column I, and then you want it to be allocated to the other columns, and column I to be updated as well.
That is going to require VBA, as cells in Excel can either have hard-coded amounts, or formulas, but never both simultaneously (so column I could not hold both your original value and formula to calculate what is left).
Is VBA acceptable? If so, you cannot enter the total payment into the same column where you want the rent calculated.
I added a column to the report and used the formulas you provided above and this is FANTASTIC! :) This is already a breath of fresh air - thank you so much.

We are just starting to learn how to create macros for clearing out info after each fiscal year. How can I use VBA for allocation? Any suggestions or ideas are much appreciated.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,461
Office Version
365
Platform
Windows
How can I use VBA for allocation?
Basically, it would just follow the same rules as I used for my formulas up in post 3.
However, I really don't see much of an advantage to using VBA for something like this. If it can be done pretty easy by formula, there usually isn't a need for VBA.
 

Forum statistics

Threads
1,081,852
Messages
5,361,694
Members
400,648
Latest member
mamamia93

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top