# averaging

#### Derrell

##### New Member
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.

Derrell

### 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
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
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.

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

Derrell

#### fairwinds

##### MrExcel MVP
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
24/30100
35/112323
45/2074
55/3074
65/434574
75/5077.5
85/650077.5
Sheet3

Replies
6
Views
142
Replies
1
Views
94
Replies
1
Views
231
Replies
7
Views
244
Replies
0
Views
45