Forum

Notifications
Clear all

Share Portfolio Dashboard - # will not work in formula

4 Posts
2 Users
0 Reactions
132 Views
(@gabrielviljoen)
Posts: 3
Active Member
Topic starter
 

Hi

Mynda's Share Portfolio Dashboard is the basis for the attached workbook.

I want to see the movement per calendar month.  I can calculate the first row, but then when I update the formula with # to make it dynamic, it gives me a #REF! error (see column R).  How can I update this formula to make it auto fill down.

It would also be nice to plot the current price between the 52week low and 52 week high to see how cheap or expansive the current price is compared to these two values.  I can do the first line with a conditional formatting, but cannot make it dynamic.  Ideally it should not be filled to the left but only be a vertical line indicating the reading.

Any suggestions on what changes I need to make?

Gabriël

 
Posted : 13/01/2022 4:12 am
(@debaser)
Posts: 838
Member Moderator
 

If  you have BYROW available to you, you could use:

=BYROW(A3#,LAMBDA(a,(INDEX(STOCKHISTORY(a,EOMONTH(TODAY(),-4)-1,,2,0),1,2)-INDEX(STOCKHISTORY(a,EOMONTH(TODAY(),-5)-1,,2),2,2))/INDEX(STOCKHISTORY(a,EOMONTH(TODAY(),-5)-1,,2),2,2)))

 
Posted : 13/01/2022 8:11 am
(@gabrielviljoen)
Posts: 3
Active Member
Topic starter
 

Thank you Velouria

I tried your formula above, but it returns #NAME? error.

=BYROW is not available on my Excel 365.

Gabriël

 
Posted : 13/01/2022 11:30 am
(@debaser)
Posts: 838
Member Moderator
 

I think you are out of luck then until such times as it is released more generally (I think it's Insider only currently).

 
Posted : 13/01/2022 2:46 pm
Share: