Excel Online How to get these functions to Spill

Hi There,

I have excel file online in MS Office Online, as I am still on 2010, I am using online to take advantage of new functions in Excel.

I am unable to get some functions to Spill if anyone could help me please.

I have named ranges in another sheet for columns A to D populated cells starting from Row 2 till end of Data: "TimeStamp", "Price", "Volume" & "Value"

In another sheet I have the following functions:
=UNIQUE(TimeStamp) Spill is working fine in this column
=MINIFS(Price;TimeStamp;A2#) Spill is working fine in this column
=MAXIFS(Price;TimeStamp;A2#) Spill is working fine in this column
=(B2#+C2#)/2 Spill is working fine in this column

=STDEV.P(FILTER(Price;TimeStamp=A2)) I am Unable to get this to Spill Down
=COUNT(UNIQUE(FILTER(Price;TimeStamp=A2))) I am Unable to get this to Spill Down

=MINIFS(Volume;TimeStamp;A2#) Spill is working fine in this column
=MAXIFS(Volume;TimeStamp;A2#) Spill is working fine in this column

=COUNT(FILTER(Price;TimeStamp=A2)) I am Unable to get this to Spill Down

Hi,

Instead of:

=COUNT(UNIQUE(FILTER(Price;TimeStamp=A2)))

use:

=MMULT(N(TRANSPOSE(IF(FREQUENCY(MATCH(TimeStamp&"|"&Price,TimeStamp&"|"&Price,0),SEQUENCE(ROWS(Price))),TimeStamp))=A2#),SEQUENCE(1+ROWS(Price))^0)

Regards

Try this:

=IFERROR(SQRT(MMULT(IFERROR((TRANSPOSE(IF(TimeStamp=TRANSPOSE(A2#),Price,""))-MMULT(TRANSPOSE(IF(TimeStamp=TRANSPOSE(A2#),Price,0)),SEQUENCE(ROWS(Price))^0)/COUNTIF(TimeStamp,A2#))^2,0),SEQUENCE(ROWS(Price))^0)/(COUNTIF(TimeStamp,A2#)-1)),"")

Regards

Could you attach the workbook here, please?

Regards

Apologies - I used STDEV.S instead of STDEV.P. Tiny change to divide by one more element:

=IFERROR(SQRT(MMULT(IFERROR((TRANSPOSE(IF(TimeStamp=TRANSPOSE(A2#),Price,""))-MMULT(TRANSPOSE(IF(TimeStamp=TRANSPOSE(A2#),Price,0)),SEQUENCE(ROWS(Price))^0)/COUNTIF(TimeStamp,A2#))^2,0),SEQUENCE(ROWS(Price))^0)/(COUNTIF(TimeStamp,A2#))),"")

Regards

You're welcome!

Regards