Using namerange, iferror and a sparklines?

[Bpuad]

Hi,

I'm have one sheet with data (in this case share price) and in the same sheet (called total) I'm calculating the % gain or loss of that share. So far so good. The share price is automatically updated each day (one row per date). So column A and B is automatically updated and column c is a formula that looks like this =IFERROR((Total!D5-Dashboard!$I$28)/Dashboard!$I$28;""). The Dashborad sheet is there the initial buying price for the share is. Again no problem. The C column is copies all the way down to row 2000, hence the if error formula to make columns where there is no share price yet marked with a blank ("").

column a column b column c
2021-01-01 172,3 0,42%
2021-01-02 172,2 0,39%

I have also made a named range for column C =OFFSET(Total!$c$5;;;COUNTA(Total!$c$5:$c$2000);1) to use with in the sparkline. But the problem here is that the sparkline interpret the "" as a value so for all the "" rows I get a 0 value in the sparkline??

Is there a way to do this differently instead of using iferrror?

 

[jasonb75]

If I'm following what you've done correctly then the cause of the problem is the use of COUNTA in your named range. Simply changing it to COUNT should resolve the issue but as you need to change it you might as well make it non-volatile at the same time.

=$C$5:INDEX($C:$C;MATCH(1e+100;$C:$C))

 

[Bpuad]

If I'm following what you've done correctly then the cause of the problem is the use of COUNTA in your named range. Simply changing it to COUNT should resolve the issue but as you need to change it you might as well make it non-volatile at the same time.

=$C$5:INDEX($C:$C;MATCH(1e+100;$C:$C))

Hi,

yes of cause, that was an easy fix. Just copied the formula from another sheet I had.

Thank a lot!

 

[Bpuad]

One more question.

if the first couple of cells are empty (due that the share was bought later) the sparklines will not work?

Like the example bellow, the first 2 rows in colum c are empty

column a column b column c
2020-12-30
2020-12-31
2021-01-01 172,3 0,42%
2021-01-02 172,2 0,39%

 

[jasonb75]

Try

=INDEX($C:$C;MATCH(1e+100;$C:$C)-COUNT($C$5:$C$2000)+1):INDEX($C:$C;MATCH(1e+100;$C:$C))

as long as there are no intermediate blanks.

 

[Bpuad]

Thanks both, it worked great