averaging

[Derrell]

Help averaging data between dates, problem is that the data is never on the same dates each month.
example:
1st 123
2nd
3rd
4th 345
5th
6th 500

and so forth for the month.

I need to know how to subtract the 4th from the 1st and average that data.

Thanks for your help,
Derrell

 

[fairwinds]

Welcome to the board!

Assuming your values are always ascending, try:

=AVERAGE(SMALL(B2:B7,ROW(INDIRECT("2:"&COUNT(B2:B7))))-SMALL(B2:B7,ROW(INDIRECT("1:"&COUNT(B2:B7)-1))),MIN(B2:B7))

Confirmed with Ctrl + Shift + Enter.

Book1
	A	B	C	D	E
1
2	1st	123
3	2nd			166.6667
4	3rd
5	4th	345
6	5th
7	6th	500
8
Sheet3

 

[Derrell]

Back in Dec 2005 you were kind and really helped with the averaging, but now I have another averaging problem thats almost the same.
I need to know how to calculate the reading in the amount column and as before the readings are never on the same days month to month.

date---reading----amount
4/30----100--------11.5
5/1------123---------11.5
5/2------000---------74
5/3------000---------74
5/4------345---------74
5/5------000---------77.5
5/6------500---------77.5

Thanks for your help again,
Derrell

 

[fairwinds]

Hi,

Even though I do not get the same result as you in the beginning (there is only one day between 30/4 and 1/5), my guess is that this is what you want:

=IF(B3,(B3-LOOKUP(2,1/($B$2:B2>0),$B$2:B2))/(A3-LOOKUP(2,1/($B$2:B2>0),$A$2:A2)),C4)

in C3 dragged down.

Book1
	A	B	C	D
1	date	reading	amount
2	4/30	100
3	5/1	123	23
4	5/2	0	74
5	5/3	0	74
6	5/4	345	74
7	5/5	0	77.5
8	5/6	500	77.5
Sheet3