Paste special multiply - but preserve original numbers?

[cornflakegirl]

In A1:A5 I have some hard-keyed numbers. I want to increase them by 5%. I know I can do this with Paste Special Multiply, but I'd like to keep an audit trail of what I've done.

I know if in eg A1 I had =1, then PSM would give me =(1)*1.05. Is there any way to make Excel do this with hard-keyed numbers?

 

[Richard Schollar]

Emma

Because you're effectively asking for a constant to be converted to a formula I think you'd need to use code to achieve it (ie and iterate thru the cells inserting a formula in place of the constant).

 

[cornflakegirl]

Thanks Richard.

I did find a tip on another site - that if I put 1.05 in B1, and then in C1 put =$B$1, then I can copy C1 and do the PSM - that would give me =1*($B$1) in A1. So I guess I could then do a find and replace to change all the $B$1 to 1.05.

Bit clunky, though.

 

[Richard Schollar]

Looks like a cool way of doing it to me

 

[pgc01]

Hi Emma

Instead of the $B$1 you can use the 1.05 directly.

Remark: unfortunately this does not work in excel 2007. A bug. Already reported.