Complicated average based on conditional

[alleyb]

The column that needs reworking is J from cells 20:35
The uploaded spreadsheet has the problem in J33 (#VALUE!) -- which is logical following its current input
However what I wish to achive is that in J33 the result should be an empty cell based off the criteria that the F33 cell says flat and if the F32 cell is also flat then the result should be an empty cell BUT if cell F32 is anything other EG short x or Long x then =G33-H32

I have tried playing with double IFs (relative to F33 and F32) , EXACT (relative to F33 and F32), ISBLANK (relative to F33 and H32) all shown on the worksheet but for the life of me I cannot get my tiny brain around the logic of what I hope to achieve


I am using Excel 2003

[zbor]

Try this:

=IF(F21="flat", G21-MAX(0, H20), "")

Instead of -H20 write -MAX(0, H20)

[alleyb]

Zbor many thanks for a super quick reply
It still in Cell J33 gives an answer of 1400 whereas I was looking for an empty cell

The point being that once flat has been achieved and if position remains flat then there is no need to calculate entry cost, average nor until it returns back to flat a resultant P/L

[alleyb]

H32 has at its heart IF(F32="Flat"," ", ......................................... so H32 returns an empty cell
whereas in your solution it is looking for a 0

Conundrum