Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using If statement to change values in a range
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 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 we 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 ------ 2090 Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using If statement to change values in a range
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using If statement to change values in a range
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 "arrow" wrote in message oups.com... 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 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 we 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 ------ 2090 Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using If statement to change values in a range
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using If statement to change values in a range
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using If statement to change values in a range
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?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change a range name back to the underlying data range? | Excel Worksheet Functions | |||
How can you change the color of the selected range? | Excel Discussion (Misc queries) | |||
Want to change the color of a true/false logical statement with i. | Excel Worksheet Functions | |||
How to move Y-axis values when X range is -a to +b | Charts and Charting in Excel | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |