Spreadsheet: 108391d1305449219-complicated-average-based-on-conditional-isblank_if-1-version-1-
Sheet: Sheet1

I started with manual input |  |  |  |  | THAT BECAME THIS
Position | Open |  |  |  | Position | Open | Cost | Adjust col M to number
Flat | 1400 |  |  |  | Flat | 1400 | 1400 | 0
Long 1 | 1405 |  |  |  | Long 1 | 1405 | 1405 | 1
Long 1-1/2 | 1395 |  |  |  | Long 1-1/2 | 1395 | 1395 | 1.5 |  |  | Index & Match
Long 2 | 1410 |  |  |  | Long 1 | 1400 | 1400 | 1 |  |  | Long 1 | 1 | 1
Long 3 | 1425 |  |  |  |  |  |  |  |  |  | Long 2 | 2 | 0
Long 4 | 1426 |  |  |  | Flat | 1390 | 1390 | 0 |  |  | Long 1-1/2 | 1.5
Long 5 | 1423 |  |  |  | short 1 | 1395 | 1395 | 1 |  |  | Flat | 0
Long 6 | 1440 |  |  |  | short 2 | 1386 | 1386 | 2 |  |  | Long 3 | 3
 |  |  |  |  | short 1 | 1395 | 1395 | 1 |  |  | Long 4 | 4
 |  |  |  |  | But I need to get a true cost based off the average of prior |  |  |  |  |  | Long 5 | 5
 |  |  |  |  | positions taking into account just the change |  |  |  |  |  | Long 6 | 6
 |  |  |  |  | when flat is achieved |  |  |  |  |  | short 1 | 1
 |  |  |  |  | IE increment the average but to work the P/L result only when achieved flat status |  |  |  |  |  | short 2 | 2
 |  |  |  |  | Works fine until positions remains flat consecutively |  |  |  |  |  | short 3 | 3
 |  |  |  |  | end result looked for
 |  |  |  |  | Position | Open | Cost | Adjust col F to number | P/L | ALTERNATIVE with ISBLANK |  |  |  | ALTERNATIVE with EXACT | ALTERNATIVE with 2 Ifs
 |  |  |  |  | Flat | 1400 | 1400 | 0 |  | 0
 |  |  |  |  | Long 1 | 1405 | 1405 | 1 |  | 5 | K21 should be empty |  |  | 5 | 5 | P21 should be empty
 |  |  |  |  | Long 1-1/2 | 1395 | 1401.666666666667 | 1.5 |  | -10 | K22 should be empty |  |  | -10 | -10 | P22 should be empty
 |  |  |  |  | Long 1 | 1400 | 1401.666666666667 | 1 |  | -1.666666666666742 | K23 should be empty |  |  | -1.666666666666742 | -1.666666666666742 | P23 should be empty
 |  |  |  |  | Flat | 1390 |  | 0 | -11.66666666666674 | -11.66666666666674 |  |  |  | -11.66666666666674 | -11.66666666666674
 |  |  |  |  | short 1 | 1395 | 1395 | 1 |  | #N/A | Clearly ISBLANK is not the way to go for H24 has a formula and is not actually BLANK |  |  | #N/A | #N/A
 |  |  |  |  | short 2 | 1386 | 1390.5 | 2 |  | -9 | [NAME] should be empty |  |  | -9 | -9 | P26 should be empty
 |  |  |  |  | short 1 | 1395 | 1390.5 | 1 |  | 4.5 | [NAME] should be empty |  |  | 4.5 | 4.5 | P27 should be empty
 |  |  |  |  | Flat | 1400 |  | 0 | 9.5 | 9.5 |  |  |  | 9.5 | 9.5
 |  |  |  |  | short 2 | 1401 | 1401 | 2 |  | #N/A | K29 should be empty |  |  | #N/A | #N/A
 |  |  |  |  | short 3 | 1413 | 1405 | 3 |  | 12 | K30 should be empty |  |  | 12 | 12
 |  |  |  |  | short 3 | 1420 | 1413 | 3 |  | 15 | K31 should be empty |  |  |  | 15 | P31 should be empty
 |  |  |  |  | Flat | 1400 |  | 0 | -13 | -13 |  |  |  | -13 | -13
 |  |  |  |  | Flat | 1400 |  | 0 | #N/A | #N/A | IF F33 and F32 are Flat then return empty cell but if F33 is Flat and in F32 there is anything but Flat then G33-H32 | EXACT wont work cos the requirement is to compare FLAT vs FLAT not Short 3 versus Short 3 | #N/A
 |  |  |  |  | long 1 | 1405 | 1405 | 1 |  | #N/A | K34 should be empty |  |  | #N/A | #N/A
 |  |  |  |  | long 1 | 1406 | 1405 | 1 |  | 1 | [NAME] should be empty |  |  |  | 1 | P35 should be empty
 |  |  |  |  |  |  |  | Total | #N/A | #N/A
 |  |  |  |  |  |  |  |  |  |  | IF and ISBLANK
 |  |  |  |  |  |  |  |  |  |  | #N/A