Macro or Formula?

[RWright]

I have a spreadsheet loaded with rates. If I need to change all the rates by
a specific amount, what should I use.. I tried a formula and found that I
couldn't get away from circular...

Example: Rate this week is 1.25 - Need to be raised .05 for the week...
result is 1.30. The following week the rate needs to be lowered by .05...
result is 1.25.

I have approximately 40 rates on one sheet and this kind of change can
happen across the board. (all 40 raised and/or lowered the same amount) -
does this need to be a macro - if so, how would I tell the macro to make the
specific amount change?

any expert advise would be appreciated!!!!!
thanks

 

[Jim Cone]

You could change all 40 formulas so they include the value from a specific cell (A1).
Then put the incremental rate change in that cell...
"=Rate(60,-135.35,5000) *2+A1"
That will allow you to change all of the values just by changing A1
-or-
If the cells with the rates do not have formulas then...
Enter the rate change amount in a blank cell.
Copy the cell.
Select the 40 cells.
Go to Edit | Paste Special | Add - click OK.
Clear the cell with the rate change.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"RWright" <[email protected]>
wrote in message
I have a spreadsheet loaded with rates. If I need to change all the rates by
a specific amount, what should I use.. I tried a formula and found that I
couldn't get away from circular...

Example: Rate this week is 1.25 - Need to be raised .05 for the week...
result is 1.30. The following week the rate needs to be lowered by .05...
result is 1.25.

I have approximately 40 rates on one sheet and this kind of change can
happen across the board. (all 40 raised and/or lowered the same amount) -
does this need to be a macro - if so, how would I tell the macro to make the
specific amount change?

any expert advise would be appreciated!!!!!
thanks

 

[RWright]

Thank you!!!