+ Reply to Thread
Results 1 to 7 of 7

Sum calculation on dynamically updating rows.

  1. #1
    Registered User
    Join Date
    06-14-2019
    Location
    Bristol, England
    MS-Off Ver
    365
    Posts
    4

    Sum calculation on dynamically updating rows.

    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

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    15,256

    Re: Sum calculation on dynamically updating rows.

    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.

  3. #3
    Registered User
    Join Date
    06-14-2019
    Location
    Bristol, England
    MS-Off Ver
    365
    Posts
    4

    Re: Sum calculation on dynamically updating rows.

    Hi Alan

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

    Graham
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-14-2019
    Location
    Bristol, England
    MS-Off Ver
    365
    Posts
    4

    Re: Sum calculation on dynamically updating rows.

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

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    15,256

    Re: Sum calculation on dynamically updating rows.

    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

  6. #6
    Registered User
    Join Date
    06-14-2019
    Location
    Bristol, England
    MS-Off Ver
    365
    Posts
    4

    Re: Sum calculation on dynamically updating rows.

    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

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    15,256

    Re: Sum calculation on dynamically updating rows.

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1