How To Formula !!!!

[mike.lewis]

Quite an interesting one here
I need a formula that will do the following

In column B there is a ref to a month and year, in particular i am looking at the last 3 digits, in the first row 707, i need a forumal that will link this number to the column number some way along.
I then need any sums including and prior to ( 707, 706, 705 etc ) these to be added together and displayed as a total in another cell ( having the code stop adding when it hits and empty cell )
Also at the same time, any cells afterwards. not including 707, to give an average of any sums entered in another cell, also to stop when it hits a blank cell

any ideas ?


<TABLE style="WIDTH: 528pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=704><COLGROUP><COL style="WIDTH: 48pt" span=11 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20 width=64>Member</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>Redemp date</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>705</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>706</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>707</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>708</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>709</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>18791</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">705</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">200707</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4097</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$4,097.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,491.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$876.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,417.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,224.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>16806</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">200706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1346</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,346.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,591.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,334.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$3,621.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$2,415.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>17258</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">200706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1284</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,284.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$880.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,168.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$2,488.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$838.00</TD></TR></TBODY></TABLE>

 

[Aladin Akyurek]

1) Total query...

P2: 200707

Q2, control+shift+enter, not just enter:

=SUM(IF($C$2:$C$4=P2,$G$2:INDEX($G$2:$K$4,0,MATCH(RIGHT(P2,3)+0,$G$1:$K$1,0))))

2) Average query...

Would you elaborate this a bit more, in particular the "any sums" bit?

 

[mike.lewis]

thankyou

yes so where you start the "sum" and add any figure prior, in the other direct i need to know what the average of the sums maybe

e.g

123456789

if the source is 5, the sum total would be 15 and the average would be 31

hope that helps

 

[Aladin Akyurek]

thankyou

yes so where you start the "sum" and add any figure prior, in the other direct i need to know what the average of the sums maybe

e.g

123456789

if the source is 5, the sum total would be 15 and the average would be 31

hope that helps

I still don't know what sums are. Care to refer to the sample you posted? I assumed this sample to be in A1:K4 as can be seen from the sum formula I suggested earlier.

 

[mike.lewis]

<TABLE style="WIDTH: 480pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=640><COLGROUP><COL style="WIDTH: 48pt" span=10 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20 width=64>Member</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>Redemp date</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>705</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>706</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>707</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>708</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>18791</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">705</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">200707</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">707</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4097</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$4,097.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,491.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$876.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,417.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>16806</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">200706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1346</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,346.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,591.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,334.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$3,621.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>17258</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">200706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1284</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,284.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$880.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,168.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$2,488.00</TD></TR></TBODY></TABLE>


O.k so you have given me the first bit but i will explain a little more

Firstly i need to be able to look at the date refrence ( yellow ), and cross correspond it with the relevant column ( also yellow ), i then need all the figures in the cell bellow it and before to be added up ( in red ).
This total to be displayed in a cell on its own, the trick is there can be many figures to add depending on how far down the sheet the reference falls.

the second part ( but you unsure on ) is i also need to work out the average of all cells after ( in blue ), these also change in length etc.
This figure to be displayed in again another cell.

Each line would be a seperate formula as each one related to a diferent sale / customer

hope that helps

 

[Aladin Akyurek]

<TABLE style="WIDTH: 480pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=640><COLGROUP><COL style="WIDTH: 48pt" span=10 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20 width=64>Member</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>Redemp date</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>705</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>706</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>707</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>708</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>18791</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">705</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">200707</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">707</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>4097</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$4,097.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,491.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$876.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,417.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>16806</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">200706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1346</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,346.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,591.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,334.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$3,621.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>17258</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">200706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1284</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,284.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$880.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$1,168.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 align=right>$2,488.00</TD></TR></TBODY></TABLE>


O.k so you have given me the first bit but i will explain a little more

Firstly i need to be able to look at the date refrence ( yellow ), and cross correspond it with the relevant column ( also yellow ), i then need all the figures in the cell bellow it and before to be added up ( in red ).
This total to be displayed in a cell on its own, the trick is there can be many figures to add depending on how far down the sheet the reference falls.[/

the second part ( but you unsure on ) is i also need to work out the average of all cells after ( in blue ), these also change in length etc.
This figure to be displayed in again another cell.

Each line would be a seperate formula as each one related to a diferent sale / customer

hope that helps

I'll come back to the average query...

The formula I suggested does not sum per record. If you enter 200706 in P2, you'll notice that it picks out both "lines". Is that not what you want?

 

[mike.lewis]

hi there

each line to be seperate ( ill show an example below of what it may look like )



for the first line as an example

• The sum of all figures from 707 backwards to 705 is $646,400.00
• The average of all figures from 708 to 712 is $1328.90

The calculation would continue until it came across a blank cell signalling an end so to speak

The next line would follow a similar calculation however dependant on its own parameters

<TABLE style="WIDTH: 710pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=946><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 48pt" span=9 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=64>Member</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2726843 class=xl65 width=64>Redemp date</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 78pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=104>Sum</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=74>Averag</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>705</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>706</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>707</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>708</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>709</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>710</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>711</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>712</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>18791</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">707</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>200707</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>$ 646,400.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>$ 1,328.90 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$4,097.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,491.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$876.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,417.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,224.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$930.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,149.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,924.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>16806</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>200709</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>$ 1,030,700.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>$ 556.50 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,346.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,591.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,334.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$3,621.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$2,415.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$688.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$425.00</TD></TR></TBODY></TABLE>


The 200707 or 200709 can repeat itself in the sheet as it is pretty much a month and date, the unique field is the member ID

 

[snoopyhr]

Not sure if this what you want, maybe get you started ...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Member	Redemp date				705	706	707	708	709
2	18791	705	200707		4097	$4,097.00	$1,491.00	$876.00	$1,417.00	$1,224.00
3	16806	706	200706		1346	$1,346.00	$1,591.00	$1,334.00	$3,621.00	$2,415.00
4	17258	706	200706		1284	$1,284.00	$880.00	$1,168.00	$2,488.00	$838.00
5
6
7		Date of interest	Respective Amount	Sum	Average
8		707	$ 876.00	$6,464.00	$ 1,320.50
9		706	$ 1,591.00	$2,937.00	$ 2,456.67
Sheet1

 

[Aladin Akyurek]

hi there

each line to be seperate ( ill show an example below of what it may look like )




for the first line as an example

• The sum of all figures from 707 backwards to 705 is $646,400.00
• The average of all figures from 708 to 712 is $1328.90

The calculation would continue until it came across a blank cell signalling an end so to speak

The next line would follow a similar calculation however dependant on its own parameters

<TABLE style="WIDTH: 710pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=946><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 48pt" span=9 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=64>Member</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2726843 class=xl65 width=64>Redemp date</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 78pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=104>Sum</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=74>Averag</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>705</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>706</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>707</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>708</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>709</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>710</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>711</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>712</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>18791</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">707</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>200707</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>$ 646,400.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>$ 1,328.90 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$4,097.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,491.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$876.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,417.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,224.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$930.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,149.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,924.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>16806</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">706</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>200709</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>$ 1,030,700.00 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>$ 556.50 </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,346.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,591.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$1,334.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$3,621.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$2,415.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$688.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 align=right>$425.00</TD></TR></TBODY></TABLE>


The 200707 or 200709 can repeat itself in the sheet as it is pretty much a month and date, the unique field is the member ID

D2, just enter and copy down:

=SUM(G2:INDEX(G2:N2,MATCH(RIGHT(C2,3)+0,$G$1:$N$1,0)))

E2, just enter and copy down:

=IF(LOOKUP(9.99999999999999E+307,$G$1:$N$1)=RIGHT(C2,3)+0,"",
    AVERAGE(INDEX(G2:N2,MATCH(RIGHT(C2,3)+0,$G$1:$N$1,0)+1):N2))

 

[mike.lewis]

D2, just enter and copy down:

=SUM(G2:INDEX(G2:N2,MATCH(RIGHT(C2,3)+0,$G$1:$N$1,0)))

E2, just enter and copy down:

=IF(LOOKUP(9.99999999999999E+307,$G$1:$N$1)=RIGHT(C2,3)+0,"",
   AVERAGE(INDEX(G2:N2,MATCH(RIGHT(C2,3)+0,$G$1:$N$1,0)+1):N2))

Thanks for this, first works a treat, the second one does return some figures but they dont seem to match what i have