Formula to get reduced value

Hello Expert here,

New to SS. I need help on the formula that can derive the value of license with every entry.

Eg: I have 5 license(A/B/C/D/E) with each $10,00 value and every time I make an entry let say of $100 of any random license number how can i get initial license value- the closing value.

Kindly refer to below

License number Debit value Balance license value

1) License A- $50 950

2) License B- $100 900

3) License A- $100 850(Opening lice value- current debit entry)

4) License B- $100 800

so on with multiple license

Best Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    I would set up the sheet with some extra columns to hold the running balance of each license and use IF formula to pull the correct running balance into the Balance license value column. Like this:

    (you can hide the columns in grey so they aren't visible)

    Row 1 is fixed with your starter license values. The formula are as follows:

    In Balance license value enter this in row 2 and drag it down:

    =IF(License@row = "A", A@row, IF(License@row = "B", B@row, ""))

    I have written this as a nested IF so you can expand for licenses C, D, and E, as follows:

    =IF(License@row = "A", A@row, IF(License@row = "B", B@row, IF(License@row = "C", C@row, IF(License@row = "D", D@row, IF(License@row = "E", E@row, "")))))

    In A enter this in row 2 and drag it down:

    =IF(License@row = "A", A1 - [Debit value]@row, A1)


    And, similarly, in B enter this in row 2 and drag it down:

    =IF(License@row = "B", B1 - [Debit value]@row, B1)

    (when you add C, D, E, columns you can use the same replacing the text in bold with C, D, and E as appropriate.

    =IF(License@row = "B", B1 - [Debit value]@row, B1))

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Just put this formula where you want the balance to appear:

    =1000 - SUMIF(License:License, "A", [Debit value]:[Debit value])

    Repeat for the other licenses changing the text in bold.

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi @Ravi K

    If you can, please mark the answer on October 28th as solving your original question. This will help everyone to know the question has been answered. Thanks!

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    I would set up the sheet with some extra columns to hold the running balance of each license and use IF formula to pull the correct running balance into the Balance license value column. Like this:

    (you can hide the columns in grey so they aren't visible)

    Row 1 is fixed with your starter license values. The formula are as follows:

    In Balance license value enter this in row 2 and drag it down:

    =IF(License@row = "A", A@row, IF(License@row = "B", B@row, ""))

    I have written this as a nested IF so you can expand for licenses C, D, and E, as follows:

    =IF(License@row = "A", A@row, IF(License@row = "B", B@row, IF(License@row = "C", C@row, IF(License@row = "D", D@row, IF(License@row = "E", E@row, "")))))

    In A enter this in row 2 and drag it down:

    =IF(License@row = "A", A1 - [Debit value]@row, A1)


    And, similarly, in B enter this in row 2 and drag it down:

    =IF(License@row = "B", B1 - [Debit value]@row, B1)

    (when you add C, D, E, columns you can use the same replacing the text in bold with C, D, and E as appropriate.

    =IF(License@row = "B", B1 - [Debit value]@row, B1))

  • Ravi K
    Ravi K ✭✭

    Hello, Thanks a ton for help and it worked.

    Instead of having the running/closing balance after every entry , how can I have the balance and top of the row after the license value. Any guidance

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Just put this formula where you want the balance to appear:

    =1000 - SUMIF(License:License, "A", [Debit value]:[Debit value])

    Repeat for the other licenses changing the text in bold.

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi @Ravi K

    If you can, please mark the answer on October 28th as solving your original question. This will help everyone to know the question has been answered. Thanks!

  • Ravi K
    Ravi K ✭✭

    @KPH Appreciate all your help. I could create the sheet I wanted. Once a again big thank you

  • KPH
    KPH ✭✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!