Adding a value to a min-max range

Hi,

I have some pricing data in a spreadsheet with a range (when there is a minimum and maximum value), of the form min-max (£200-£220 for example).

In the same spreadsheet I have prices that are set, that dont have a minimum/maximum (for example just £200).

I have a formula set up that will change all of the values by an amount that I enter into a box for the weekly price change, that changes all of the set prices by whatever the amount is, i.e. if the change is +20 it'll move from £200 to £220. But I cannot find a way to do this automatically for a range, i.e. change £200-£220 to £220-£240.

Does anyone have any ideas?

Steve

Depends on how your data is set-up. Are these price ranges defined in 1 cell? Do you include the pound sign in the cells? Are the ranges split by a dash? Is this always the same?

The cells are either 'xxx' or 'xxx-yyy'. Adding a value to the 'xxx' ones is easy. For reference, i have this formula for the normal cells;

Please Login or Register  to view this content.

Where L1750 is the previous value (in this case 158) and M$1797 is the monthly change (-15). That will make M1750=143. The start of the formula is just to remove boxes with blanks in them.

In the case whereby there is a range it is entered as 158-168 for example, rather than two seperate cells. As an aside, I have another column (W) which I change each time I email prices to the current months column (in this case column M) which then automatically fills in another spreadsheet which I generate the mail merge from. Its long winded but is actually fairly simple, honest!

I dont understand that formula but it works :D

Thanks very much!!!