# Using namerange, iferror and a sparklines?

##### New Member
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?

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### jasonb75

##### Well-known Member
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.
Excel Formula:
``=\$C\$5:INDEX(\$C:\$C;MATCH(1e+100;\$C:\$C))``

##### New Member
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.
Excel Formula:
``=\$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!

##### New Member
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

##### Well-known Member
Try
Excel Formula:
``=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.

##### New Member
Thanks both, it worked great

Replies
11
Views
162
Replies
1
Views
53
Replies
1
Views
115
Replies
0
Views
45
Replies
1
Views
218

1,127,787
Messages
5,626,876
Members
416,208
Latest member
tan21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back