Array Formula

[Kashyap Barot]

Hi,

Below is my dataset.
12-Sep-13 10
13-Sep-13 10.01
16-Sep-13 10.02
17-Sep-13 10.03
18-Sep-13 10.04
19-Sep-13
20-Sep-13 10.06
23-Sep-13 10.07
24-Sep-13
25-Sep-13
26-Sep-13 10.10
27-Sep-13 10.11
30-Sep-13 10.12

I need to calculate average of daily return i.e. average of {[(10.01/10)-1]+.... [(10.12/10.11)-1]}/10

condition: if blank e.g 19-Sep-13, ignore the entry and for 20-Sep-13 calculate daily return as [(10.06/10.04)-1]. Similarly ignore 24 and 25-Sep-13 & calculate daily return for 26-Sep-13 as [(10.10/10.07)-1].


I am using the array formula {=AVERAGE((C1399:C1440/C1398:C1439)-1)}
which works fine with data set having no blanks.
I need to do this calculation for much larger series.

Please guide me on the same.

 

[oldchippy]

Hi Kashyap Barot,

If you split the time values from the date with

=MOD(A1,INT(A1)) copy down

Then use

=AVERAGE(IF(B1:B13<>0,B1:B13)) Entered as an array Ctrl+Shft+Enter and format to time

 

[Somendra Misra]

Hi Kashyap Baraot,

Use below formula
=IF(B2<>"",AVERAGE($B$2:B2),"")

No array formula, copy down.

Regards!

 

[Kashyap Barot]

hi oldchippy,

I think there is a confusion in the understanding of data,
date is a separate column while the numbers are a separate column, its not a time series.

also the please see the condition part of the problem

 

[oldchippy]

Can you please upload a file for a better understanding, with your expected result?

 

[Jake Collins]

Dear Kashyap Barot

If you can use a helper column (to calculate the daily return) then the array formula (In d3 for example)

=C3/OFFSET($C$2,LARGE(($C$2:$C2<>0)*ROW($C$1:C1),1)-1,0)-1

will give -1 for the daily return for rows with no data, else it will use the the previous non zero value of data to calculate the daily return.

you can then work out the average using the array formula

=AVERAGE(IF(D3:D14<>-1,D3:D14))

p.s.

Shouldn't in your example it be divided by 9 not 10?

 

[oldchippy]

Dear Kashyap Barot,

numbers are a separate column, its not a time series

My mistake, obviously I've have drunk to much already!