Hi, I've modelled myself into a cul-de-sac so I'm hoping someone has an easy solution! The data is accounting data, and so credit values are interfaced as negatives, and debits as positives.
I'm using sparklines to show the sales and ebitda trends over several years for a number of sites, but want the sparkline to treat negatives as positives and vice versa. There is a big model that references this data and so it's not possible to change the base data in anyway (i.e. multiplying by minus 1 is not an option) and to create a copy of the data just to change the sign would be a terrible waste of space and resources.
I've used the following formula as the definition for my sparkline data;
=OFFSET(SALES!$L$2,MATCH(INDIRECT("RC[-1]",),SALES!$A:$A,0)-1,0,1,31)
where RC-1 is the reference of the site i'm looking up in the column A of the sales spreadsheet, and there are 31 columns of data after cell SALES!$L$2 which I wish to show on the sparkline.
is there a neat way to change the sign of my data just for the sparkline purpose only?
many many many thanks
I'm using sparklines to show the sales and ebitda trends over several years for a number of sites, but want the sparkline to treat negatives as positives and vice versa. There is a big model that references this data and so it's not possible to change the base data in anyway (i.e. multiplying by minus 1 is not an option) and to create a copy of the data just to change the sign would be a terrible waste of space and resources.
I've used the following formula as the definition for my sparkline data;
=OFFSET(SALES!$L$2,MATCH(INDIRECT("RC[-1]",),SALES!$A:$A,0)-1,0,1,31)
where RC-1 is the reference of the site i'm looking up in the column A of the sales spreadsheet, and there are 31 columns of data after cell SALES!$L$2 which I wish to show on the sparkline.
is there a neat way to change the sign of my data just for the sparkline purpose only?
many many many thanks
