#1
|
|||
|
|||
Adding an amount 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 |
#2
|
|||
|
|||
What is the formula you use for the "singleton" cells? If it's something on the order of:
Code:
=100 + adjust Code:
=TEXT(150 + adjust, "£0") & "-" & TEXT(175 + adjust, "£0") |
#3
|
|||
|
|||
Hmmm that could work, il give that a go......
|
#4
|
|||
|
|||
Ah, right a problem.
The problem is that I want the adjustment to be applied to last months value. So if last month was 100, adjustment 20 would be 120 for this month. Then if next month the adjustment is 50 it would return a value of 170. However, for the ranges the cells read 'xxx-yyy' so i would therefore need a way for that =TEXT formula to take the first value only (xxx) and add the adjustment, then the second value only (yyy) and add the adjustment. |
#5
|
|||
|
|||
Got the answer from another forum, was trickier than expected but works a treat;
Code:
=IF(ISNUMBER(FIND("-",A1)),LEFT(A1,FIND("-",A1)-1)+A2&"-"&RIGHT(A1,LEN(A1)-FIND("-",A1))+A2,A1+A2) |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
how to cut and paste for the range | tomlam | Excel Programming | 5 | 12-26-2012 02:20 AM |
use VBA to name a range | g48dd | Excel Programming | 7 | 06-20-2011 06:34 AM |
Adding Image into a excel cell and adding a hyperlink to the image | saravananiyyanar | Excel | 3 | 05-04-2011 08:31 AM |
Sort Range | boutells | Excel | 1 | 07-15-2009 03:02 AM |
Range Formula | aleksandr | Excel | 10 | 05-18-2009 12:14 PM |