applying IF function to a range of rells in an entire worksheet
Posted by Jeanette on April 08, 2001 1:43 PM
I want to apply the following formula to all the selected cells in my worksheet.
" if theres a dollar amount then divide it by 9"
Here's the formula I typed in =IF(E98:O98>1,/9,"")
which I understand to read if the contents of cells E98 to O98 are greater than 1,divide by 9,if it's not leave it blank.
when I put /9 I get the message invalid, yet if I just apply /9 to one cell it works. What am I doing wrong?
Posted by Aladin Akyurek on April 08, 2001 2:14 PM
Jeanette
You say "when I put /9 I get the message invalid": Where do you put /9 exactly?
Aladin
Posted by Brian Ackermann on April 08, 2001 7:37 PM
Jeanette,
I think what you are trying to do is use an array formula to perform a conditional calculation.
Assuming that your original data resides in Row 98, Columns E through O, highlight another row, say Row 99, Columns E through O.
(This range will contain your processed data.)
Type =if(E98:O98>1,E98:O98/9,"") and then press Ctrl-Shift-Enter to activate the cells.
I think the problem you ran into is that when you use array functions, multiple ranges in the same function all have to be the same size.
I hope this helps.
Posted by Jeanette on April 09, 2001 10:24 PM
Posted by Jeanette on April 09, 2001 10:33 PM
Thanks for the reply, I tried =IF(E98:O98>1,E98:O98/9,"")then Ctrl-Shift-Enter, and I get the error message value, which I looked up in the 'Help' but didn't understand what it said.
Am I asking it the right question with the "IF" statement? All I want it to do is , if there is a dollar amount entered in any of a selected range of cells then divide that cell contents by 9 and entere it in another column. All the selected cells have the $ sign where an amount is enterd
Posted by Aladin Akyurek on April 09, 2001 11:16 PM
=============
Jeanette
Activate E99, type the formula
=IF(E98:O98>1,(E98:O98)/9,"") or rather
=IF(E98:O98>1,(E98:O98)/9,E98:O98) if you want to preserve the numbers that are not greater than 1,
then select the range F99:O99. While the range E99:O99 is selected, go to the formula bar where you see the formula that you just typed, hit CONTROL+SHIFT+ENTER at the same time. As a result, you'll see the range E99:O99 filled up with the values you are after.
Note. If you use the second formula, you can copy the range E99:O99 and paste them over the original values by activating Paste Special and using the option Values. Then delete everything in E99:O99.
Aladin
