STDEV and values

[yaelcohen]

Hello

Thanks for your help!!!

I attached a file with an example.

I have to take the value of the stock only at the end of the year, the date will be the last trading day in the year while the next day will be the trading day of the next year.

I also have to calculate the Standard deviation (std) of each stock for every month.

Thanks for any help, and also thanks for the help you gave me until now

yom tov

 

[xlstime]

You can put simply pivot table (value as StdDevp) or convert this =STDEV.P(E3:E33) formula into array with multiple criteria using if function

like

let say
j3 have 111013 (stock)
k3 have 1 (month)
l3 have 2015 (year)

=STDEV.P(IF((($B$3:$B$84=L3)*($C$3:$C$84=K3)*($A$3:$A$84=J3)),E3:E84))

Hello

Thanks for your help!!!

I attached a file with an example.

I have to take the value of the stock only at the end of the year, the date will be the last trading day in the year while the next day will be the trading day of the next year.

I also have to calculate the Standard deviation (std) of each stock for every month.

Thanks for any help, and also thanks for the help you gave me until now

yom tov

 

[yaelcohen]

Hello @xlstime
Thanks for your help. I did as you suggest but got a different answer (attached)
Did I do something different than you say?

 

[xlstime]

Sorry mate, you have to enter with ctl+shift+enter (array formula)

Hello @xlstime
Thanks for your help. I did as you suggest but got a different answer (attached)
Did I do something different than you say?

 

[yaelcohen]

Hello @xlstime

You are right, I just forgot it. thanks a lot!
So, regarding the value, you suggest to use the same formula (w/o the std), right?

 

[xlstime]

One more array formula for cell n3
=INDEX($F$3:$F$84,MAX(IF(($A$3:$A$84=J3)*($B$3:$B$84=L3)*($C$3:$C$84=K3),ROW(D3:D84))+2))

with ctl+shft+enter

Hello @xlstime

You are right, I just forgot it. thanks a lot!
So, regarding the value, you suggest to use the same formula (w/o the std), right?

 

[yaelcohen]

Thanks @xlstime !!!

According to the sample (in the attached file) the only value that have to be shown by the formula is as mentioned in cell H53. As this is the only value of a stock that represents the value of the last day of the year.

I checked your proposal for formula in N3 and I got values for stock 111013 while this stock has not any value of end of the year (means - end of December)

Hoping I am clear with my question

Thanks a lot!

 

[xlstime]

Try this with ctl+shift+enter

=OFFSET($F$1,MAX(IF(($A$3:$A$84=$J$3)*($B$3:$B$84=L3)*($C$3:$C$84=12),ROW(D3:D84)-1)),0)

Thanks @xlstime !!!

According to the sample (in the attached file) the only value that have to be shown by the formula is as mentioned in cell H53. As this is the only value of a stock that represents the value of the last day of the year.

I checked your proposal for formula in N3 and I got values for stock 111013 while this stock has not any value of end of the year (means - end of December)

Hoping I am clear with my question

Thanks a lot!