+ Reply to Thread
Results 1 to 11 of 11

lookup last value and deduct entries from another row

  1. #1
    Registered User
    Join Date
    02-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    15

    lookup last value and deduct entries from another row

    I really struggle with this one

    excel shot.jpg

    i have a dynamic table with the columns indicating years
    Row 22 are provisions built up so $182.5k in Y1, $183k in Y2 and $182.5k in Y3
    In Y3 there is an investment due of $850k however there have been provisions built in the previous years so the amount should be $850k-182.5k-183k-182.5k.

    How do I create a formula that looks up the last value in row 21, checks when the previous value occurred in row 21, counts how many rows are in between the last and the second last occurrence, adds the equivalent number of columns up in row 22 and deducts this from the last entry in row 21?

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,287

    Re: lookup last value and deduct entries from another row

    Can you run that last paragraph again? It is not clear what you want to calculate.

    If you enter data into cell P22, then you can't have a formula in cell P22 at the same time.

    Please manually mock up the expected result and explain the logic that leads to the result.

  3. #3
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,287

    Re: lookup last value and deduct entries from another row

    Can you run that last paragraph again? It is not clear what you want to calculate.

    If you enter data into cell P22, then you can't have a formula in cell P22 at the same time.

    Please manually mock up the expected result and explain the logic that leads to the result.


    How do I create a formula that looks up the last value in row 21,
    That is cell R21, right?

    checks when the previous value occurred in row 21,
    That is cell P21, right?
    counts how many rows are in between the last and the second last occurrence,
    Wait, what?? They are both on the same row.

    adds the equivalent number of columns
    Wait, what?? Which columns?

    up in row 22 and deducts this from the last entry in row 21?
    It does not make sense.

  4. #4
    Registered User
    Join Date
    02-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: lookup last value and deduct entries from another row

    thanks for your effort and I am sorry if my initial message was confusing

    so let me try again

    1) find the first value in row 21, which is P21 - $850k
    2) add up all values in row 22 up to (and including) P22 - $182.5k+$183k+$182.5k
    3) reduce P21 by the sum N22 to P22 - or in other words 1) minus 2)
    4) find next value in row 21 - here R21
    5) add up all corresponding values in row 22 between the last and the second last entry in row 21 - so here Q22 and and R22 - $182.5k+182.5k
    6) reduce R21 by the sum Q22+R22 - so again 4) minus 5) - 500k-182.5k-182.5k

    and so on...

  5. #5
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,287

    Re: lookup last value and deduct entries from another row

    Still not clear. Where should the formula go? Can you please mock up the expected result and post a screenshot?

  6. #6
    Registered User
    Join Date
    02-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: lookup last value and deduct entries from another row

    in this particular example, this is how the net result should look like:

    Annotation 2019-01-10 085315.jpg

    All fields are dynamic so need a formula that does the steps as described above.

  7. #7
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,287

    Re: lookup last value and deduct entries from another row

    I don't understand. P21 now shows with 302k. What happened to 850k?

    Look, I'm really trying to help you here, but you are being a bit cryptic. I asked you which cells should have the formulas. You did not answer that. If you don't reply to questions for clarification, then I can't help you.

    If P21 contains the $850k, in which cell do you want to put the formula? You cant have a value and a formula in the same cell.

  8. #8
    Registered User
    Join Date
    02-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: lookup last value and deduct entries from another row

    sorry I am not trying to annoy you nor being cryptic and sincerely appreciate your effort - the 850k (P21) is a computed value from another cell - in order not to make too complicated I left the input factors for that value out.

    So it this specific case it would be P21-SUM(N22:P22) and R21-SUM(Q22:R22)

    or generalised
    first entry in row 21(assume letter X)-SUM(N22:X22)
    second entry in row 21(assume letter AA)-SUM(Y22:AA22)
    third entry in row 21 (assume letter BA)-SUM(AB22:BA22) and so on...
    Last edited by eastpak; 01-11-2019 at 05:40 AM.

  9. #9
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,287

    Re: lookup last value and deduct entries from another row

    Do you have a problem reading what I write or are you ignoring my question ?

    In which cell(s) or row(s) do you want to put the formula that uses the value from rows 21 and 22?

  10. #10
    Registered User
    Join Date
    02-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: lookup last value and deduct entries from another row

    said it now twice:

    So it this specific case it would be P21-SUM(N22:P22) and R21-SUM(Q22:R22)

    However as all fields are dynamic it's more difficult as i always first need to test where the entries in row 21 are to do the above calculations. The calculation itself is easy, the more tricky bit is to find a formula that looks up where the values in column 21 are

  11. #11
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,287

    Re: lookup last value and deduct entries from another row

    I don't think you understand what I'm asking.

    A formula has to be entered into a cell. You've given me the formula logic. But which cell should I click on to enter the formula??

    If a formula calculates values from P22 and P21 it cannot be entered into either cells P22 or P21. So, into which cell should the formula be entered?

    I'm trying to help you here, but you are not doing much to let me do that.

+ 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