Finding certain text and then summing annual numbers

P

pricepersf

New Member
Joined
Sep 4, 2014
Messages
2
I work in commercial real estate and use Argus and Excel to underwrite properties. Argus is real estate software that outputs annual/monthly/etc proforma with varying detail depending on the property.

What I'd like to do:
- Use a sheet to annualize the data outputted by a monthly Argus Model. The issue I'm running into is that depending on the property, different expense line items with be within the output and the locations change per output.

I put in reference numbers that auto find the right name/row combination but haven't been able to come up with a formula that works.

Here is where I paste the monthly Argus output. The periods on the top extend for 15 years. The line items on the left change per property.

w7cDY.jpg




Here is the sheet where I try to annualize the Argus data. Please disregard the residential section at the top as it doesn't come from Argus. The numbers to the left of the Argus line items are the rows from the previous sheet. I figured I could use these numbers as reference points in a formula. Also, please disregard the data to the right of the line items as I manually linked the data. Sometimes the types of expenses vary per building so I'd like to be able to paste any Argus output into the previous sheet, manually add the line items names in this sheet and have it auto sum for the 15 year timeline on this sheet.

CjgCn.jpg


Thanks so much. If you have any followup questions, I'll be here.

-Mark
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Edited to include Mr. Excel's HTML maker

I work in commercial real estate and use Argus and Excel to underwrite properties. Argus is real estate software that outputs annual/monthly/etc proforma with varying detail depending on the property.

What I'd like to do:
- Use a sheet to annualize the data outputted by a monthly Argus Model. The issue I'm running into is that depending on the property, different expense line items with be within the output and the locations change per output.

I put in reference numbers that auto find the right name/row combination but haven't been able to come up with a formula that works.

Here is where I paste the monthly Argus output. The periods on the top extend for 15 years. The line items on the left change per property.Monthly Argus Output
ABCDE
1
2
3
4
5
6Potential Gross Revenue
7 Base Rental Revenue
8 Absorption & Turnover Vacancy
9 Base Rent Abatements
10
11 Scheduled Base Rental Revenue
12
13 Expense Reimbursement Revenue
14 Real Estate Taxes
15 Operating Expenses
16 Electric
17
18 Total Reimbursement Revenue

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Year 1[/TD]
[TD="align: center"]Year 1[/TD]
[TD="align: center"]Year 1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Month 1[/TD]
[TD="align: center"]Month 2[/TD]
[TD="align: center"]Month 3[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"][/TD]
[TD="align: center"]Jan-2015[/TD]
[TD="align: center"]Feb-2015[/TD]
[TD="align: center"]Mar-2015[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"] ___________[/TD]
[TD="align: right"] ___________[/TD]
[TD="align: right"] ___________[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]$247,931[/TD]
[TD="align: right"]$247,933[/TD]
[TD="align: right"]$247,926[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"](153,207)[/TD]
[TD="align: right"](153,209)[/TD]
[TD="align: right"](153,204)[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"](21,887)[/TD]
[TD="align: right"](21,887)[/TD]
[TD="align: right"](21,886)[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"] ___________[/TD]
[TD="align: right"] ___________[/TD]
[TD="align: right"] ___________[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]72,837[/TD]
[TD="align: right"]72,837[/TD]
[TD="align: right"]72,836[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]164[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]2,189[/TD]
[TD="align: right"]2,188[/TD]
[TD="align: right"]2,254[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"] ___________[/TD]
[TD="align: right"] ___________[/TD]
[TD="align: right"] ___________[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]2,189[/TD]
[TD="align: right"]2,188[/TD]
[TD="align: right"]2,418[/TD]

</tbody>
Argus​


Here is the sheet where I try to annualize the Argus data. Please disregard the residential section at the top as it doesn't come from Argus. The numbers to the left of the Argus line items are the rows from the previous sheet. I figured I could use these numbers as reference points in a formula. Also, please disregard the data to the right of the line items as I manually linked the data. Sometimes the types of expenses vary per building so I'd like to be able to paste any Argus output into the previous sheet, manually add the line items names in this sheet and have it auto sum for the 15 year timeline on this sheet.

Annual Profroma
ABCDEF
For the Years Ending
Gross Residential Income
General Vacancy $ - $ - $ -
Get Ready Cost $ - $ - $ -
Residential Effective Gross Income $ - $ - $ -
Potential Gross Revenue
Base Rental Revenue $ - $ 3,067,050 $ 3,151,183
Absorption & Turnover Vacancy $ (1,452,379) $ (405,781) $ -
Base Rent Abatements $ (264,592) $ (272,549) $ -
Scheduled Base Rental Revenue $ 1,274,128 $ 2,388,720 $ 3,151,183
Expense Reimbursement Revenue
Real Estate Taxes $ 1,664 $ 7,008 $ 15,835
Operating Expenses $ 30 $ - $ -
Electric $ 47,240 $ 104,087 $ 128,529
Total Reimbursement Revenue $ 48,880 $ 111,095 $ 144,364

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]Year 1[/TD]
[TD="align: right"]Year 2[/TD]
[TD="align: right"]Year 3[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12/31/2014[/TD]
[TD="align: right"]12/31/2015[/TD]
[TD="align: right"]12/31/2016[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]18[/TD]

</tbody>


Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
344
Akuini
Akuini
K
  • Question Question
Fill by matching and adding rows for multiple matches
Replies
0
Views
667
kidneythief
K
F
  • Question Question
How to find an exact number match in a text file?
Replies
9
Views
1K
frankT68
F
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
M
  • Question Question
Auto adjust row heights based on specific column
Replies
1
Views
398
XLfriend777
XLfriend777

Forum statistics

Threads
1,222,828
Messages
6,168,484
Members
452,193
Latest member
Arlochorim

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top