ExcelBanter - Daily Averages

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Daily Averages (https://www.excelbanter.com/excel-discussion-misc-queries/155901-daily-averages.html)

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Vincent" wrote in message
...
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

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

"Don Guillett" escreveu:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Vincent" wrote in message
...
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