inverting a sparkline

ljwood

New Member
Joined
Aug 10, 2014
Messages
1
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Watch MrExcel Video

Forum statistics

Threads
1,102,305
Messages
5,486,072
Members
407,532
Latest member
shahbazfcca

This Week's Hot Topics

Top