averaging

Derrell

New Member
Joined
Dec 12, 2005
Messages
2
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
ABCDE
1
21st123
32nd166.6667
43rd
54th345
65th
76th500
8
Sheet3
 

Derrell

New Member
Joined
Dec 12, 2005
Messages
2
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
ABCD
1datereadingamount
24/30100
35/112323
45/2074
55/3074
65/434574
75/5077.5
85/650077.5
Sheet3
 

Watch MrExcel Video

Forum statistics

Threads
1,118,671
Messages
5,573,577
Members
412,537
Latest member
Mohamed_5966
Top