Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-13-2013, 05:53 AM
Nibbles Nibbles is offline Adding an amount to a min-max range Windows 7 64bit Adding an amount to a min-max range Office 2010 64bit
Novice
Adding an amount to a min-max range
 
Join Date: Nov 2012
Posts: 9
Nibbles is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 06-13-2013, 07:22 AM
CGM3 CGM3 is offline Adding an amount to a min-max range Windows XP Adding an amount to a min-max range Office 2007
Advanced Beginner
 
Join Date: Oct 2009
Posts: 38
CGM3 is on a distinguished road
Default

What is the formula you use for the "singleton" cells? If it's something on the order of:

Code:
   =100 + adjust
(where adjust is the cell with the adjustment value), you should be able to apply it to the min-max ranges with something like:

Code:
    =TEXT(150 + adjust, "£0") & "-" & TEXT(175 + adjust, "£0")
Granted, this does make the cell contents text, but I don't know if that matters.
Reply With Quote
  #3  
Old 06-13-2013, 07:36 AM
Nibbles Nibbles is offline Adding an amount to a min-max range Windows 7 64bit Adding an amount to a min-max range Office 2010 64bit
Novice
Adding an amount to a min-max range
 
Join Date: Nov 2012
Posts: 9
Nibbles is on a distinguished road
Default

Hmmm that could work, il give that a go......
Reply With Quote
  #4  
Old 06-13-2013, 07:39 AM
Nibbles Nibbles is offline Adding an amount to a min-max range Windows 7 64bit Adding an amount to a min-max range Office 2010 64bit
Novice
Adding an amount to a min-max range
 
Join Date: Nov 2012
Posts: 9
Nibbles is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 06-13-2013, 08:05 AM
Nibbles Nibbles is offline Adding an amount to a min-max range Windows 7 64bit Adding an amount to a min-max range Office 2010 64bit
Novice
Adding an amount to a min-max range
 
Join Date: Nov 2012
Posts: 9
Nibbles is on a distinguished road
Default

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)
where A1 is the 'xxx' or 'xxx-yyy' cell and A2 is the change cell
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding an amount to a min-max range how to cut and paste for the range tomlam Excel Programming 5 12-26-2012 02:20 AM
Adding an amount to a min-max range use VBA to name a range g48dd Excel Programming 7 06-20-2011 06:34 AM
Adding an amount to a min-max range 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
Adding an amount to a min-max range Range Formula aleksandr Excel 10 05-18-2009 12:14 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:03 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft