Sum calculation on dynamically updating rows.

[daddyg01]

Hi

I wonder if I could get some help please...

I've got a google sheet where I want to work out the maximum loan to value for each lead. Each lead is on it's own row. However the spreadsheet dynamically populates with a new row for each lead as the lead is generated so the sheet formula needs to take account of this.

I can't post any links yet as first time poster but if it helps the first lead row would be in row 2.

Here's the basic logic of the formula

formula = ((home value x 0.80) - outstanding mortgage) - debt amt

In spreadsheet home value is column M, outstanding mortgage is column N and debt amt is column L.

I'd be very grateful for any help, many thanks.

Graham

[alansidman]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[daddyg01]

Hi Alan

Thanks very much for responding so quickly. Please see attached.

Graham

[daddyg01]

Just to reiterate, it's not just the formula for the LTV but the fact it's a dynamically updating Google sheet.

[alansidman]

1. Put this formula in E2 =(B2+D2)/C2
2. Highlight the range (in this example) A1:E4
3. Click on Ctl + T (Create Table)
4. Click OK
5. Now when you add data in column A, it will auto update the formula in Column E

[daddyg01]

Hi Alan,

Many thanks for your help with this. I tried your instructions but it didn't update the fields. I'm guessing I'm doing something wrong though. Please see this very short video:

I put E2=(B2+D2)/C2 into field E2. Not sure if that's right or I should have just put =(B2+D2)/C2 into field E2 ?


Many thanks,

Graham

[alansidman]

Just type the formula =(B2+D2)/C2 in E2. This will work in Excel. I do not use Google Docs so I am not sure how this will work there if you create a table.