Including If statements in Named Ranges?

[clarybelw]

Hello

I have a datatable with various metrics by month and have dynamic named ranges using index set up so that I can plot graphs/sparklines by selecting a start and end date.

I now want to create sparklines to look at percentage changes of each month compared to the value at the start date. Is there a way of incorporating this formula into the named ranges or do I need to create an extra column for each metric to do this?

Current named range (where start date is in 'Front Page (Sparklines)'!$E$1 and end date is in 'Front Page (Sparklines)'!$E$3):
=INDEX(Understand[Total Active Customers],MATCH('Front Page (Sparklines)'!$E$1,Understand[Dates],0)):INDEX(Understand[Total Active Customers],MATCH('Front Page (Sparklines)'!$E$3,Understand[Dates],0))

Current percentage change formula as a new column in the datatable: =IF('Front Page (Sparklines)'!$E$1=[@Dates],0,IF('Front Page (Sparklines)'!$E$1<[@Dates],[@[Total Active Customers]]/INDEX([Total Active Customers],MATCH('Front Page (Sparklines)'!$E$1,[Dates]))-1,NA()))

Any help much appreciated! Many thanks

 

[clarybelw]

I've now created a named range to calculate the percentages but I still need to be able to restrict on the dates. I've tried a couple of offsets and index-match formulas but can't get it to work!

=IF('Front Page (Sparklines)'!$E$1=Understand[Dates],0,IF('Front Page (Sparklines)'!$E$1<Understand[Dates],Understand[Total Active Customers]/INDEX(Understand[Total Active Customers],MATCH('Front Page (Sparklines)'!$E$1,Understand[Dates]))-1,NA()))