making a formula but a cell is reffered to an other cell

this formula is very close to what i mean
=IF(F3="trade","nvm",IF(D3="trade",IF(F3>0,F3-LOOKUP(2,1/($F2:F$3="trade"),$D2:D$3),""),IF(F3>0,F3-D3,"")))

in the 2nd document is the last issue with the formula.
if here is an answer for life would be fulfilled.

thx.


Hello!

i have a complicated problem writing a formula in a document.
it goes like this:

say that i buy and sell items for instance~
so i have some sort of profit right?

sell price - buy price = profit.

OK, clear.

now i buy an item, BUT i trade it for another item. and the traded item i sell later on.
then i have this as the following: buy price - trade - sell price. And for this i want to make a formula.

I hope this is an clear and simple example. Now lets take a look at this in excel.
this is what i have in the document:

inkoop verkoop winst

deurmat 10 32 ? 22,00
hobbelpaard 40 30 -? 10,00
sjonnie 100 100 ? 0,00
dobbelstenen 50 trade nvm
beker =f6 75 #WAARDE!

See what i mean? i want to calculate the profit for the "beker" but the beker was traded for dobbelstenen. so naturally i use the buy price from the dobbelstenen. and writing that formula is a headache.
The formula i want to write must see that the buy price is traded. then it needs to search what row that is and find the buy price from that row.

in the xlsx map is the docu. If someone could write it in there my life would be complete.

thx in advance

Try

in H3

=IF(F3="trade","nvm",IF(D3="trade",IF(F3>0,F3-LOOKUP(2,1/($F2:F$3="trade"),$D2:D$3),""),IF(F3>0,F3-D3,"")))

Copy down

Something simalar,

Please try in H3 and copy down:

Formula:

Please Login or Register  to view this content.

Will your real data contain more than ONE pair of "trade"? For example, if the two black rows were not there. If so... how can you link the buying part of a trade with is correct selling part?

You may need to rethink this. You may need a more realistic sample sheet.

Why ? looking at the "logic" of consecutive trades then E18 should be 100 ??? Are consecutive trades in "C" to match consecutive trades in "A" i.e C15 with A18 / C16 with A19 ?? If not, how do we match the trades?

Please take a moment to READ the forum rules that you agreed to adhere to. Especially Rule 5.

https://www.excelforum.com/forum-rul...rum-rules.html

I have merged the two threads into one.

Please DO NOT duplicate threads again.