Using namerange, iferror and a sparklines?

Bpuad

New Member
Joined
Feb 17, 2012
Messages
36
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,485
Office Version
  1. 365
Platform
  1. Windows
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))
 

Bpuad

New Member
Joined
Feb 17, 2012
Messages
36
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Bpuad

New Member
Joined
Feb 17, 2012
Messages
36
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,485
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Bpuad

New Member
Joined
Feb 17, 2012
Messages
36
Office Version
  1. 2019
Platform
  1. Windows
Thanks both, it worked great (y)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top