Re: Daily Averages

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
dguillett1@xxxxxxxxxxxxx
"Vincent" <Vincent@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:14402518-79C4-4CE1-BE7E-CE2D62F77AEE@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • RE: Time if/then caclculate average formula?
    ... H and I to accumulate the readings ... You will need to format you column B as time in the ... Sub TODavg() ... Dim lr, lr2 As Long ...
    (microsoft.public.excel.programming)
  • Re: Daily Averages
    ... Don Guillett ... Microsoft MVP Excel ... If i dont have any reading it´s equal to zero. ... this column is equal to the days when the readings ...
    (microsoft.public.excel.misc)
  • Re: Sort Problem!
    ... > 412 five zero six eleven ... > 540 rolex ... > 076 Munich ... > 331 Readings in Natural Language Processing ...
    (comp.unix.shell)
  • Re: [OT] Navy releases photos of U.S.S. San Francisco
    ... Triton found some correlation between hers and their search sonar (their ... to the underlying geologic structure- and both can throw of the readings ... But if you had one of these near the stern of the sub, ... gravitational attraction at the two ends of the sub as the seamount was ...
    (sci.space.history)
  • Re: Sort Problem!
    ... I would like to sort a huge ascii file like this: ... > 412 five zero six eleven ... > 540 rolex ... > 331 Readings in Natural Language Processing ...
    (comp.unix.shell)