Daily Averages

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vincent - 27 Aug 2007 11:18 GMT

Hi everyone,

I would like to have a column with daily averages.
Per example, In C25 i have "x" and in C27 i have 30. So, in both D26 and
D27, i want 30/2. Because i had 30 in two days interval.

B C D
24 :98
25 :100 2 2/1
26 : 30/2
27 :130 30 30/2
28 : 60/10
... 60/10
37 :190 60 60/10

Column B is the readings column.

The results are in random days so i´m having a hard time to find a formula
that´s able to give me the interval of days. I tried using:

D27
=IF(B27=0;D28;(C27/((((MATCH(B27;$B$1:B27))-MATCH((INDEX($B$1:B26;MATCH(9,99999999999999E+307;$B$1:B26)));$B$1:B26;0))))))

I used MATCH minus MATCH to identify the number of gaps in column B, which
is the equivalent of days between readings.

HOWEVER, i may have repeated values in column B, which means MATCH won´t
work. I´ll always have x-x=0.

Help!
Thanks everyone

Reply to this Message

Don Guillett - 27 Aug 2007 15:27 GMT

Try this using ONLY col A & col B to put the average in col C

Sub GetDailyAverage()
On Error Resume Next
lr = Cells(Rows.Count, "b").End(xlUp).Row
With Range("b4:b" & lr)
Set c = .Find("*", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
nc = Range(Cells(c.Row, 2), Cells(lr, 2)).Find("*").Row
num = Cells(nc, 2) - Cells(c.Row, 2)
dif = Cells(nc, 1) - c.Offset(, -1)
Cells(nc, "c") = num / dif
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
==
Will look like this
24 98
25 100 2
26
27 130 15
28
29
30
31
32
33
34
35
36
37 190 6

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi everyone,
>
[quoted text clipped - 27 lines]
> Help!
> Thanks everyone

Reply to this Message

Vincent - 27 Aug 2007 16:16 GMT

Don u´re the man.

Anyway, I invented a trick to solve my problem in a different way than yours:

D27
=IF(B27=0;D28;(C27/((((MATCH(Z27;$Z$1:Z27))-MATCH((INDEX($Z$1:Z26;MATCH(9,99999999999999E+307;$Z$1:Z26)));$Z$1:Z26;0))))))

Column Z is a hidden column. If i dont have any reading it´s equal to zero.
Otherwise it´s equal to the value in Column A (which starts in 1 jan and ends
in 31 dez). Basically, this column is equal to the days when the readings
were made:

Z27= IF(B27=0;"";A27)

In this case, function MATCH won´t give zero minus zero because i won´t have
repeated days :)

Thanks a lot Don for answering me

> Try this using ONLY col A & col B to put the average in col C
>
[quoted text clipped - 63 lines]
> > Help!
> > Thanks everyone

Reply to this Message

Don Guillett - 27 Aug 2007 16:44 GMT

I think I would use the macro approach to eliminate unnecessary overhead.

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Don u´re the man.
>
[quoted text clipped - 90 lines]
>> > Help!
>> > Thanks everyone

Reply to this Message