change value across cells

[Guest]

Cells E16 through P16 contain different numbers. I would like to be able to
select all cells in this range and apply a 3% increase.

Is there a function that will handle this?

Also, if several of the cells contain a formula, is there a way that I can
apply the same increase through the same range?

Thanks!

JT

 

[Roger Govier]

Hi

With regard to the cells with values.
First type 1.03 into any blank cell.
Copy the cell.
Select the cells with values by holding down Control and clicking each
in turn.
Right click>Multiply.

As for the cell with formulae, I think you would need to amend each by
adding *X1, where X1 is a cell address holding the value 1.03. This
would mean you could adjust those values any time, by just amending the
value in X1.
You could of course, do the same for all of the cells holding values.

Again, having amended the first cell, if you highlit the *X1 part of the
formula and did Control+C to copy, then for each other cell press F2 to
edit, place cursor at end of formula and do Control+V followed by Enter.

 

[T. Valko]

You can do the copy/paste special to formulas also. Of course, it depends on
the formula.

A1:A10 = 10

Suppose the formula was =SUM(A1:A10). The result is 100

You want to increase the result by 3%

You do the copy/paste special/multiply and Excels appends the formula as
=(SUM(A1:A10))*1.03

Biff

 

[Roger Govier]

Hi Biff

Do you know, in all the years I have been using Excel, I have never
tried to do that and didn't realise that it operated in that way.

XL2007? I haven't even mastered all of XL97 yet<bg>
Many thanks.

 

[Guest]

That's two of us!
Picked up another 'previously unknown to me' just today also (a variation on
insert row). I just keep learning stuff here almost every day.

 

[Dave Peterson]

I like this one:

Pick out a cell that will be used for adjustments (say A1).

Then pick out an empty cell (say K1)
put this in K1: =$a$1
Then select K1
edit|copy

Select the range to adjust (say B2:C5)
edit|Paste special|multiply

Clear K1 (we're done with it).

Put whatever adjustment you want in A1 and watch B2:C5.

Kind of a neat way to play what-if games.

 

[Roger Govier]

Hi Dave

It might be safer to make that formula in K1 =1+$A$1
Then enter in A1 the % increase or decrease.
That way, the values don't all get set to zero if you delete the value
in A1

 

[Dave Peterson]

Depends on what you want...

I like the technique no matter what detail you use.