performing identical calculation over a range of cells?

[frankc29]

I almost never use excel except as a glorified running total calculator, so bear this in mind as I try to go beyond that for the first time.

Let's say I have a range of manually entered numbers in a column, such as b8:b20. Now lets say I want to subtract the same figure from each cell, a number located in B1, and only after that operation is completed then get a sum for the range of cells in b8:b20. I would only want this subtraction to occur if the number is greater than 0 (to avoid a negative number within the range where a blank cell was). I hope that wasn't too confusing.

So how to I proceed?

[VBA Noob]

Hi,


Without your zero criteria I would of suggested enter your neg number in say cell A1 and then copy cell A1 then select your range and right click to get the paste special box and click add and ok

http://www.bettersolutions.com/excel...I216522111.htm

As this is not a option then use a helper column ( e.g any available column)
Say Col C. Enter this in C8 and drag down.

=IF(B8>0,B8-10,B8) ...change -10 to your number

Then drag down. Select the Col C range and copy then select B8 and paste special the values (See link).

VBA Noob

[frankc29]

I first posted this with a ambiguous title, which is why it got the boot I think. Sorry! Anyway, before that thread was deleted "NBVC" answered with:

"Try:

=SUM(IF(B8:B20<>0,B8:B20-B1))

this must be confirmed with CTRL+SHIFT+ENTER not just ENTER...you should see { } brackets appear around the formula."


It worked liked a charm. Thanks!

[VBA Noob]

Glad you got an answer.

Please ensure you read the rules below so your post won't get deleted

VBA Noob