Spreadsheet: 107989d1305109944-isblank-plus-if-plus-complicated-average-isblank_if-1-
Sheet: Sheet1

I started with manual input |  |  |  | I then wanted to extract an automatic calculation |  |  |  |  |  |  |  | and then the problem ran into the following
 |  |  |  | that gave me a cost that but this does not take into account previous cost
Position | Open |  |  | Position | Open | Cost | Adjust col D to number |  |  |  |  | Position | Open | Cost | Adjust col D to number
Flat | 1400 |  |  | Flat | 1400 | 1400 |  |  |  |  |  | Flat | 1400 | 1400
Long 1 | 1405 |  |  | Long 1 | 1405 | 1405 | 1 |  |  |  |  | Long 1 | 1405 | 1405 | 1
Long 1-1/2 | 1395 |  |  | Long 1-1/2 | 1395 | 1395 | 1.5 |  |  |  |  | Long 1-1/2 | 1395 | 1395 | 1.5
Long 2 | 1410 |  |  | Long 2 | 1410 | 1410 | 2 |  |  |  |  | Long 1 | 1400 | 1400 | 1 |  |  | Long 1 | 1
Long 3 | 1425 |  |  | Long 3 | 1425 | 1425 | 3 |  |  |  |  | Flat | 1390 | 1390 |  |  |  | Long 2 | 2
Long 4 | 1426 |  |  | Long 4 | 1426 | 1426 | 4 |  |  |  |  | short 1 | 1395 | 1395 |  |  |  | Long 1-1/2 | 1.5
Long 5 | 1423 |  |  | Long 5 | 1423 | 1423 | 5 |  |  |  |  | short 2 | 1386 | 1386 |  |  |  | Long 2 | 2
Long 6 | 1440 |  |  | Long 6 | 1440 | 1440 | 6 |  |  |  |  | short 1 | 1395 | 1395 |  |  |  | Long 3 | 3
 |  |  |  |  |  |  |  |  |  |  |  | Because the criteria |  |  |  |  |  | Long 4 | 4
 |  |  |  | But Cell F4 should be blank |  |  |  |  |  |  |  | short in Col Position has been added I did not |  |  |  |  |  | Long 5 | 5
 |  |  |  |  |  |  |  |  |  |  |  | know how to incorporate this |  |  |  |  |  | Long 6 | 6
 |  |  |  |  |  |  |  |  |  |  |  | as IF statement has already used up
 |  |  |  |  |  |  |  |  |  |  |  | 7 Ifs
 |  |  |  |  |  |  |  |  |  |  |  | end result looked for
NOTE: |  |  |  |  |  |  |  |  |  |  |  | Position | Open | Cost | Adjust col D to number
position can be Flat or Long 1 through Long 9 inclusive or short 1 through short 6 inclusive |  |  |  |  |  |  |  |  |  |  |  | Flat | 1400
If necessary I can do away with the -1/2s as perhaps that complicates |  |  |  |  |  |  |  |  |  |  |  | Long 1 | 1405 | 1405 | 1
 |  |  |  |  |  |  |  |  |  |  |  | Long 1-1/2 | 1395 | 1401.6 | 1.5
 |  |  |  |  |  |  |  |  |  |  |  | Long 1 | 1400 | 1401.6 | 1
 |  |  |  |  |  |  |  |  |  |  |  | Flat | 1390
 |  |  |  |  |  |  |  |  |  |  |  | short 1 | 1395 | 1395 | 1
 |  |  |  |  |  |  |  |  |  |  |  | short 2 | 1386 | 1390.5 | 2
 |  |  |  |  |  |  |  |  |  |  |  | short 1 | 1395 | 1390.5 | 1
 |  |  |  |  |  |  |  |  |  |  | 1 | this sorts out the cost Col returning a blank if the Position
 |  |  |  |  |  |  |  |  |  |  |  | Column is Flat
 |  |  |  |  |  |  |  |  |  |  | 2 | This sorts out the Average of Cost taking into account previous
 |  |  |  |  |  |  |  |  |  |  |  | Cost
 |  |  |  |  |  |  |  |  |  |  | 3 | Note that on a reduction of position the Cost remains at the
 |  |  |  |  |  |  |  |  |  |  |  | previous high cost
 |  |  |  |  |  |  |  |  |  |  | 4 | Thi sorts out the incorporation of position whether long or short
 |  |  |  |  |  | have a complicated ISBLANK and IF setup
 |  |  |  |  |  | I am using excel 2003
 |  |  |  |  |  | the major problem arises from nested IF statements in that I have at least 16 possible IFs
 |  |  |  |  |  | also
 |  |  |  |  |  | I am looking to have a result in a column that sums or averages a cost input taking into account a previous entry. The previous cost input entry will always be one cell above the required new entry
 |  |  |  |  |  | There are several problems which I have laid out in the attached file and annotated with what I have currently and an example of what I would like to achieve
 |  |  |  |  |  | Thks kindly in advance for your suggestions and pointers