Dividing a Range Containing a Formula by the Same Value

[grovelli]

I'd like to divide the range S6:W17 in the attached file by the value in cell G2 but when I copy the value in G2 and "Paste Special" it using the divide option, the values in W6:W17 get all wrong because they depend on the values in columns S and V. How can I solve this?

[HansV]

Perhaps only divide S6:V17 by the value of G6? Because of the formulas in W6:W17, the values in that range will change automatically.

[grovelli]

Of course
Thank you Hans

[snb]

I'd suggest:

in W6: =(IF($S6>0;$S6;$K6)+V6)/$G$2

Numberformat "0,000"

[grovelli]

As the final formula I'm using =IF((IF($S6>0;$S6;$J6)+$V6)>=0;(IF($S6>0;$S6;$J6)+$V6);0). I don't think there's the need to use "/$G$2" as the formula components are already divided by the content of the G2 cell using Paste Special.

[HansV]

You can shorten that to

=MAX(IF($S6>0;$S6;$J6)+$V6;0)

[grovelli]

[snb]

Copy/paste is unnecessary when using the formula I suggested.