using If statement to change values in a range

A

arrow

Hello... I'm new to using Excel. I have a spreadsheet that I'm trying
to find a formula for. I suppose I should mention that I am using
Excel 2000 and have the R1C1 reference enabled.

What I have is a column, R15C9:R43C9 (I believe this is how you'd
represent that, Row 15 through 43 in column 9). These columns are
autocalulated into the cell R53C9. I want a formula that will add 10%
to all the cells in the R15C9:R43C9 range if the result in R53C9 is
equal to or less then 2000.
From what I've read this can probably be accomplished using the IF
Click to expand...
command, but I am not familiar enough with Excel to figure out the
formula using the help files.

For instance: if the original numbers for column nine were:

500
500
500
400
------

1900

Then I want a formula that will add 10% to each of the cells in the
range, thus creating:

550
550
550
440
 
D

davemorrison

are you asking for a circular reference?
this can probably be done with a macro, but I don't think it can be
done with a regular spreadsheet formula, but of course, I may be wrong
, so lets see what others think, if you want a macro for it let me know.
 
B

Biff

Hi!

To do this with formulas would require an additional column and a separate
sum formula.

In J15 enter this formula:

=IF(I53<=2000,I15*1.1,I15)

Copy down to I43.

Put another Sum formula in J53:

=SUM(J15:J43)

If you're using R1C1 references, temporarily switch to back to A1 style,
enter the new formulas then switch back to R1C1.

Biff
 
P

Pete

If you want to increase the value of every cell in a range by 10%, you
can do it manually quite quickly. In a blank cell enter 1.1 (i.e.110%),
and click <copy>. Then highlight your range (i.e. R15C9:R43C9) and
click Edit | Paste Special | Values (check) and Multiply (check) and
click OK then press <Esc>.

You can choose to do this if the value in R53C9 is less than or equal
to 2000.

As Dave says, you will get a circular reference error if you try to
devise a formula to do this.

Hope this helps.

Pete
 
A

arrow

A macro would be great. I don't know anything about them, so I'm not
sure how to use them, but if you could give me some quick instructions
or point me to where I can read up on macros, that'd be fantastic.
Thanks.
 
D

davemorrison

yes that will work, I believe we are dealing with a newbee,
if I were you I would go with arrow's suggestion
just out of curiosity, why are you using R1C1 references??
 
Top