Complicated average based on conditional

[alleyb]

http://www.tradingclinic.com/oneoff/comp_av.xls

The INDEX and MATCH I have managed to get working in COL I
This takes the Value of COL F and turns it into a number in COL I

No1 problem
the issue is the average of position Long or short or flat in COL H specifically in H31 whre the expected result should be bringing forward the same number from H30. Why? it's because there is no change in position at F31 vs F30 so the result in H31 should show 1405

No2 problem
The same type of issue occurs as a seperate problem in COL J when trying to calculate positions that have gone flat from long or short. The first instance of flat results in a correct calculation of the cost versus open values but as soon as a 2nd position is flat then it brings in a cost when the cell should be empty. cc J 33

I've tried using ISBLANK or EXACT or even nested IFs as shown elsewhere on the example link but my tiny brain cannot find the logic to sort this out. Any help would be appreciated

I am using excel 2003

 

[jasonb75]

Please post a data sample to the board, not a link to a downloadable file.

The majority of formum users will not download such files for a number of reasons.

 

[alleyb]

<TABLE style="WIDTH: 250pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=333 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 39pt" height=52><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 39pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=52 width=64>
Position
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>

Open

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=77>
Cost
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=64>


Adjust col F to number
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>P/L</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Flat</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1400</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num x:fmla='=IF(ISBLANK(A2)=TRUE,"",B2)'>1400</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str="" x:fmla='=IF(A2="flat","",B2)'></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Long 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1405</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right x:num x:fmla='=IF(A3="Flat","",IF(ISBLANK(A3)=TRUE,"",IF(D3<D2,C2,(((D3-D2)*B3)+(B2*D2)))/D3))'>1405.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str="" x:fmla='=IF(A3="flat",B3-MAX(0, C2),"")'></TD></TR><TR style="HEIGHT: 41.25pt; mso-height-source: userset" height=55><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 41.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=55>Long 1-1/2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1395</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right x:num="1401.6666666666667" x:fmla='=IF(A4="Flat","",IF(ISBLANK(A4)=TRUE,"",IF(D4<D3,C3,(((D4-D3)*B4)+(B3*D3)))/D4))'>1401.67</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1.5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str="" x:fmla='=IF(A4="flat",B4-MAX(0, C3),"")'></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Long 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1400</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right x:num="1401.6666666666667" x:fmla='=IF(A5="Flat","",IF(ISBLANK(A5)=TRUE,"",IF(D5<D4,C4,(((D5-D4)*B5)+(B4*D4)))/D5))'>1401.67</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str="" x:fmla='=IF(A5="flat",B5-MAX(0, C4),"")'></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Flat</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1390</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 x:str="" x:fmla='=IF(A6="Flat","",IF(ISBLANK(A6)=TRUE,"",IF(D6<D5,C5,(((D6-D5)*B6)+(B5*D5)))/D6))'></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num="-11.666666666666742" x:fmla='=IF(A6="flat",B6-MAX(0, C5),"")'>-11.6667</TD></TR><TR style="HEIGHT: 36pt; mso-height-source: userset" height=48><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 36pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=48>short 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1395</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right x:num x:fmla='=IF(A7="Flat","",IF(ISBLANK(A7)=TRUE,"",IF(D7<D6,C6,(((D7-D6)*B7)+(B6*D6)))/D7))'>1395.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str="" x:fmla='=IF(A7="flat",B7-MAX(0, C6),"")'></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>short 2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1386</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right x:num x:fmla='=IF(A8="Flat","",IF(ISBLANK(A8)=TRUE,"",IF(D8<D7,C7,(((D8-D7)*B8)+(B7*D7)))/D8))'>1390.50</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str="" x:fmla='=IF(A8="flat",B8-MAX(0, C7),"")'></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>short 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1395</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right x:num x:fmla='=IF(A9="Flat","",IF(ISBLANK(A9)=TRUE,"",IF(D9<D8,C8,(((D9-D8)*B9)+(B8*D8)))/D9))'>1390.50</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str="" x:fmla='=IF(A9="flat",B9-MAX(0, C8),"")'></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Flat</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1400</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 x:str="" x:fmla='=IF(A10="Flat","",IF(ISBLANK(A10)=TRUE,"",IF(D10<D9,C9,(((D10-D9)*B10)+(B9*D9)))/D10))'></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num x:fmla='=IF(A10="flat",B10-MAX(0, C9),"")'>9.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>short 2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 align=right x:num>1401</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right x:num x:fmla='=IF(A11="Flat","",IF(ISBLANK(A11)=TRUE,"",IF(D11<D10,C10,(((D11-D10)*B11)+(B10*D10)))/D11))'>1401.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str="" x:fmla='=IF(A11="flat",B11-MAX(0, C10),"")'></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>short 3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 align=right x:num>1413</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right x:num x:fmla='=IF(A12="Flat","",IF(ISBLANK(A12)=TRUE,"",IF(D12<D11,C11,(((D12-D11)*B12)+(B11*D11)))/D12))'>1405.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str="" x:fmla='=IF(A12="flat",B12-MAX(0, C11),"")'></TD></TR><TR style="HEIGHT: 89.25pt" height=119><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 89.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=119>short 3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 align=right x:num>1420</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right x:num x:fmla='=IF(A13="Flat","",IF(ISBLANK(A13)=TRUE,"",IF(D13<D12,C12,(((D13-D12)*B13)+(B12*D12)))/D13))'>1413.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str="" x:fmla='=IF(A13="flat",B13-MAX(0, C12),"")'></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>Flat</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 align=right x:num>1400</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 x:str="" x:fmla='=IF(A14="Flat","",IF(ISBLANK(A14)=TRUE,"",IF(D14<D13,C13,(((D14-D13)*B14)+(B13*D13)))/D14))'></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num x:fmla='=IF(A14="flat",B14-MAX(0, C13),"")'>-13</TD></TR><TR style="HEIGHT: 86.25pt; mso-height-source: userset" height=115><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 86.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=115>Flat</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 align=right x:num>1400</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 x:str="" x:fmla='=IF(A15="Flat","",IF(ISBLANK(A15)=TRUE,"",IF(D15<D14,C14,(((D15-D14)*B15)+(B14*D14)))/D15))'></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num x:fmla='=IF(A15="flat",B15-MAX(0, C14),"")'>1400</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>Long 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 align=right x:num>1405</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right x:num x:fmla='=IF(A16="Flat","",IF(ISBLANK(A16)=TRUE,"",IF(D16<D15,C15,(((D16-D15)*B16)+(B15*D15)))/D16))'>1405.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" x:str="" x:fmla='=IF(A16="flat",B16-MAX(0, C15),"")'></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=18>Flat</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right x:num>1410</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 x:str="" x:fmla='=IF(A17="Flat","",IF(ISBLANK(A17)=TRUE,"",IF(D17<D16,C16,(((D17-D16)*B17)+(B16*D16)))/D17))'> </TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right x:num>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right x:num x:fmla='=IF(A17="flat",B17-MAX(0, C16),"")'>5</TD></TR></TBODY></TABLE>
The above is in and is the result of the formulae listed below versus the freee text and numbers in the F and G COL
F19 through I35


<TABLE style="WIDTH: 124pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=165 x:str><COLGROUP><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 6034" width=165><TBODY><TR style="HEIGHT: 89.25pt" height=119><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 124pt; HEIGHT: 89.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=119 width=165>H31 should have the answer 1405 on basis that there is no increase in the short 3 position via the F31 vs F30 or in COL I the number is not incremented from I31 vs I30</TD></TR></TBODY></TABLE> <TABLE style="WIDTH: 124pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=165 x:str><COLGROUP><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 6034" width=165><TBODY><TR style="HEIGHT: 86.25pt; mso-height-source: userset" height=115><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 124pt; HEIGHT: 86.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=115 width=165>IF F33 and F32 are Flat then return empty cell in J33 but if F33 is Flat and in F32 there is anything but Flat then G33-H32</TD></TR></TBODY></TABLE>
H20 has following formula: =IF(ISBLANK(F20)=TRUE,"",G20)
H21 through I35 has following formula: =IF(F21="Flat","",IF(ISBLANK(F21)=TRUE,"",IF(I21<I20,H20,(((I21-I20)*G21)+(G20*I20)))/I21))
I20 has: =IF(ISERROR(MATCH($F20,$L$7:$L$17,0)),"",INDEX($M$7:$M$17,MATCH($F20,$L$7:$L$17,0)))
J20 has: =IF(F20="flat","",G20)
J21 and below to J35 has: =IF(F21="flat",G21-MAX(0, H20),"")
L6 through M17
has
<TABLE style="WIDTH: 220pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=293 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5961" width=163><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 98pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=130>Index & Match</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 122pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=163></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17>Long 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17>Long 2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17>Long 1-1/2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right x:num>1.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17>Flat</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right x:num>0</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=18>Long 3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right x:num>3</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=18>Long 4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17>Long 5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17>Long 6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17>short 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right x:num>1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=18>short 2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right x:num>2</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>short 3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right x:num>3</TD></TR></TBODY></TABLE>

The link is to an area within my website but I appreciate your comments about external downloads