@kasey

What purpose would a LAMBDA serve in this case? You have some repetition of functions in it that you could avoid with LET, and thus shorten the formula. Please upload a file with some example data. Then we don't have to recreate from scratch.

I have attached a mock up. I would be grateful for any advice! 

Thank you for you time 

@kasey 

Thanks! I understand you have some stock listed in "GBPounds" and some in "GBpence".

In the Transactions table you could use LET to make it a bit easier to read and it should pick-up the difference between GBp and what is not GBp.

But your original formula should also work. So, I don't understand your issue with the "orange" cells.

=LET(
div, IF(EXACT([@Ccy], "GBp"), 100, 1),
calc,ROUND([@Quantity]*XLOOKUP([@[Sell ID]],[BUY ID],[Share Price]),6),
IFERROR(calc / div - [@[Cost Value]], "")
)

I want to calculate the Profit & Loss on the ORANGE rows but I get the circular error, therefore, can't do calculations on these

@kasey 

OK, so you want the total realised (sell) and unrealised (no sell) results. Then I suggest you insert a column that calculates the quantity 'on hand' for rows with no Sell-ID. That makes it a lot easier. Then use the following LET formula (file attached):

=LET(
div, IF(EXACT([@Ccy], "GBp"), 100, 1),
sell, NOT(ISBLANK([@[Sell ID]])),
calcsold,ROUND([@Quantity]*XLOOKUP([@[Sell ID]],[BUY ID],[Share Price]),6)/ div - [@[Cost Value]],
calcunsold, ROUND([@QtyOnHand]*([@[Today''s Price]]-[@[Share Price]]),6)/ div,
result, IF(sell, calcsold, calcunsold),
result
)

div sets the divisor based on GBp or anything else

sell, sets TRUE for rows with a Sell-ID, FALSE for those without

calsold, calculates the net result as if the item was sold

calunsold, calculates the net result as if the item was NOT sold

result, if sell = TRUE then return calsold else calunsold

@riny thank you so much! 

Learning to use LET just to get to know it, so thank you