# lookup last value and deduct entries from another row

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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...

9. ## 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. ## 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. ## 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.

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

#### 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