0

I'm making a financial analysis model for real estate properties. I created a named function called INCOME that calculates total income for a given property name from a database of different income types for each property, e.g. rent, late fees, misc. income, etc.

For example, if you type in =INCOME("Property A"), it will calculate the total income for Property A from the database.

Now I'm trying to calculate Income (along with a lot of other more complicated functions) across the entire portfolio of properties.

So I want to make a list of all the properties, say Property A, Property B, and Property C, then I want to calculate the sum of the incomes using my INCOME function. See snippet below:

enter image description here

But I want this to be dynamic. So what I really want is to enter a formula that says: take the sum of INCOME(B5) to INCOME(Bn) where Bn is the last non-empty row, i.e. the last property in the Properties column. This way it will update correctly as I add more properties.

Is it possible to do this in Google Sheets (without using Apps Script)?

double-beep
  • 5,031
  • 17
  • 33
  • 41
Kim Hopkins
  • 59
  • 7
  • Will you be able to share your customized function so we can replicate your concern, if you can provide dummy sheets with your input and desired output, it will be more helpful. – Twilight May 03 '23 at 03:12

3 Answers3

2

You may try this & see how it goes:

=let(Σ,tocol(B5:B,1),
       reduce(,sequence(counta(Σ)),lambda(a,c,a+index(INCOME(Σ),c))))
rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • This is code for Google sheets? I don't recognize the syntax. – Kim Hopkins May 02 '23 at 23:04
  • https://i.imgur.com/VhFq6Mu.png I created a `INCOME Custom function` for a simple lookup within my sample dataset (as in screenshot) and then used the above formula to dynamically sum up all the income lookup values of all properties listed in `B5:B` – rockinfreakshow May 03 '23 at 10:40
  • Wow! You just used like 5 functions I had never heard of before. Thank you! I would never have thought of this on my own. I tried to unwrap this but am getting stuck. For one, I would expect the `INDEX` function to take a range in as its first argument, but I see you have the monetary income result of the `INCOME` function as its first argument which I can't figure out. Trying to figure out what the `LAMBDA` function is doing after that. I think I understand the `LET`, `TOCOL`, `REDUCE`, and `SEQUENCE` functions though! Thank you. – Kim Hopkins May 03 '23 at 15:33
0

Tried it just for sport!

example

Let's say we have the custom function INCOME bellow :

function INCOME( value ) {
  
    return 2
}

Using REDUCE and LAMBDA with the GS formula bellow seems to be enough to get the sum you want

reduce(0;B5:B;lambda(a;c;if(c="";a;a+INCOME(c))))
Thomas
  • 94
  • 7
0
  1. Enter “Properties” in B4, then in B5 enter:
=SORT(UNIQUE(Sheet1!A2:A))

Where you replace “Sheet1!A2:A” with whatever sheet & column has your property names, to get a dynamic list of all your properties.

  1. Enter “Income” in C4, then in C5 enter:
=BYROW(B5:B,LAMBDA(r,
    IF(r=“”,””,
    INCOME(r))))

Which will go through and run your custom INCOME function for every property in your dynamically populated properties list.

  1. In C3 enter =SUM(C5:C) which will give you the total income for all your properties dynamically and will remain in the same cell no matter how long or short that property list gets.

  2. Repeat steps 2 & 3 in the columns to the right for however many custom formulas/metrics you need!

horanimal
  • 352
  • 1
  • 10