Re: Daily Averages

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@xxxxxxxxxxxxx
"Vincent" <Vincent@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:FFC98483-D43D-4BAD-8847-550E1A50E0E3@xxxxxxxxxxxxxxxx
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: Hide Columns based on a cell value
    ... Microsoft MVP Excel ... Right now I have cells set up to sum the columns. ... that column equals zero, ... "Don Guillett" wrote: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Min Function
    ... This is an array formula that must be entered using ctrl+shift+enter ... Don Guillett ... Microsoft MVP Excel ... value excluding any zero values, i.e if a zero value occurs I want the result ...
    (microsoft.public.excel.worksheet.functions)
  • Re: simple question
    ... tools>options>view>unchcheck zero values ... Don Guillett ... Microsoft MVP Excel ... "eman" wrote in message ...
    (microsoft.public.excel)
  • Re: Compressing sparse data
    ... Don Guillett ... Microsoft MVP Excel ... many cases are blank or zero. ... the data spread out over lots of rown. ...
    (microsoft.public.excel)
  • Re: Daily Averages
    ... If i dont have any reading it´s equal to zero. ... Sub GetDailyAverage() ... Loop While Not c Is Nothing And c.Address firstAddress ... Column B is the readings column. ...
    (microsoft.public.excel.misc)
Loading