Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure how to write the formula
I am fairly new to writing formulas. The formula I always use is F2-J2/I2.
Pretty simple, except when I2 has no data or is zero then I get the #DIV. How do I either keep the #DIV from showing or write the formula in such a way that it basically says: If I2 is less than, equal to or has no data then leave blank, if not process the above formula. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure how to write the formula
=IF(I2="","",F2-J2/I2)
-- Gary''s Student - gsnu200773 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure how to write the formula
This will take care of it if it's zero or blank...
not sure what you wanted to test with the less than or equal to? =IF(OR(I2=0,I2=""),"",F2-J2/I2) "Caligirl" wrote: I am fairly new to writing formulas. The formula I always use is F2-J2/I2. Pretty simple, except when I2 has no data or is zero then I get the #DIV. How do I either keep the #DIV from showing or write the formula in such a way that it basically says: If I2 is less than, equal to or has no data then leave blank, if not process the above formula. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure how to write the formula
Thanks for your response but this doesn't work. This basically, is giving me
the sum of F2. I am sure it is me and how I am presenting the question. is there a way for me to email it to you so you can see? "Gary''s Student" wrote: =IF(I2="","",F2-J2/I2) -- Gary''s Student - gsnu200773 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure how to write the formula
Okay, I got it. I just had to add this to your formula (f2-j2)/i2. That
made it work. Thank you so very much. Now I can move on to the next dilemna. Here's one for you: If cells c thru o are equal to zero then delete the row? How in the world would I write that? "Caligirl" wrote: I am fairly new to writing formulas. The formula I always use is F2-J2/I2. Pretty simple, except when I2 has no data or is zero then I get the #DIV. How do I either keep the #DIV from showing or write the formula in such a way that it basically says: If I2 is less than, equal to or has no data then leave blank, if not process the above formula. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure how to write the formula
There is no such thing as "cells c through o". All cell names are a
combination row/column reference. You would need VBA code for this. For example Sub DeleteRows() If WorksheetFunction.Sum(Range("C1:O1")) = 0 Then Range("C1").EntireRow.Delete End If End Sub Just some air code so please step through first. It will delete rows with text so be sure to adjust the range as appropriate. HTH, JP On Mar 14, 2:56*pm, Caligirl wrote: Okay, I got it. *I just had to add this to your formula (f2-j2)/i2. *That made it work. *Thank you so very much. *Now I can move on to the next dilemna. * Here's one for you: *If cells c thru o are equal to zero then delete the row? *How in the world would I write that? "Caligirl" wrote: I am fairly new to writing formulas. *The formula I always use is F2-J2/I2. * Pretty simple, except when I2 has no data or is zero then I get the #DIV.. * How do I either keep the #DIV from showing or write the formula in such a way that it basically says: *If I2 is less than, equal to or has no data then leave blank, if not process the above formula.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure how to write the formula
You can't actually delete a row using a formula, so you would either
have to do it manually (with a formula indicating to you the row(s) which need deleting), or with a macro. Hope this helps. Pete On Mar 14, 6:56*pm, Caligirl wrote: Okay, I got it. *I just had to add this to your formula (f2-j2)/i2. *That made it work. *Thank you so very much. *Now I can move on to the next dilemna. * Here's one for you: *If cells c thru o are equal to zero then delete the row? *How in the world would I write that? "Caligirl" wrote: I am fairly new to writing formulas. *The formula I always use is F2-J2/I2. * Pretty simple, except when I2 has no data or is zero then I get the #DIV.. * How do I either keep the #DIV from showing or write the formula in such a way that it basically says: *If I2 is less than, equal to or has no data then leave blank, if not process the above formula.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure how to write the formula
how do I write such a formula?
"Pete_UK" wrote: You can't actually delete a row using a formula, so you would either have to do it manually (with a formula indicating to you the row(s) which need deleting), or with a macro. Hope this helps. Pete On Mar 14, 6:56 pm, Caligirl wrote: Okay, I got it. I just had to add this to your formula (f2-j2)/i2. That made it work. Thank you so very much. Now I can move on to the next dilemna. Here's one for you: If cells c thru o are equal to zero then delete the row? How in the world would I write that? "Caligirl" wrote: I am fairly new to writing formulas. The formula I always use is F2-J2/I2. Pretty simple, except when I2 has no data or is zero then I get the #DIV.. How do I either keep the #DIV from showing or write the formula in such a way that it basically says: If I2 is less than, equal to or has no data then leave blank, if not process the above formula.- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure how to write the formula
this sounds like what I need. When I run it I get this error message:
Unable to get the Sum Property of the Worksheet Function Class Any ideas? "JP" wrote: There is no such thing as "cells c through o". All cell names are a combination row/column reference. You would need VBA code for this. For example Sub DeleteRows() If WorksheetFunction.Sum(Range("C1:O1")) = 0 Then Range("C1").EntireRow.Delete End If End Sub Just some air code so please step through first. It will delete rows with text so be sure to adjust the range as appropriate. HTH, JP On Mar 14, 2:56 pm, Caligirl wrote: Okay, I got it. I just had to add this to your formula (f2-j2)/i2. That made it work. Thank you so very much. Now I can move on to the next dilemna. Here's one for you: If cells c thru o are equal to zero then delete the row? How in the world would I write that? "Caligirl" wrote: I am fairly new to writing formulas. The formula I always use is F2-J2/I2. Pretty simple, except when I2 has no data or is zero then I get the #DIV.. How do I either keep the #DIV from showing or write the formula in such a way that it basically says: If I2 is less than, equal to or has no data then leave blank, if not process the above formula.- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure how to write the formula
You probably have to qualify the range reference with the worksheet
index number or proper name: Sub DeleteRows() If WorksheetFunction.Sum(Worksheets(1).Range("C1:O1") ) = 0 Then Worksheets(1).Range("C1").EntireRow.Delete End If End Sub Change "Worksheets(1)" to the name or index number of your exact sheet, for example if you have a worksheet named "Tables" and it was the third tab in your sheet, you could refer to it by Worksheets("Tables") or Worksheets(3). HTH, JP On Mar 14, 5:12*pm, Caligirl wrote: this sounds like what I need. *When I run it I get this error message: Unable to get the Sum Property of the Worksheet Function Class Any ideas? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not sure how to write the formula
Put this in P2:
=IF(SUM(C2:O2)=0,"delete","") and copy this down. Then you can apply autofilter to column P and select the word "delete" from the filter pull-down. Highlight all the visible rows, then click on Edit | Delete Row. Then select "All" from the filter pull-down to see what remains. Hope this helps. Pete On Mar 14, 9:10*pm, Caligirl wrote: how do I write such a formula? "Pete_UK" wrote: You can't actually delete a row using a formula, so you would either have to do it manually (with a formula indicating to you the row(s) which need deleting), or with a macro. Hope this helps. Pete On Mar 14, 6:56 pm, Caligirl wrote: Okay, I got it. *I just had to add this to your formula (f2-j2)/i2. *That made it work. *Thank you so very much. *Now I can move on to the next dilemna. * Here's one for you: *If cells c thru o are equal to zero then delete the row? *How in the world would I write that? "Caligirl" wrote: I am fairly new to writing formulas. *The formula I always use is F2-J2/I2. * Pretty simple, except when I2 has no data or is zero then I get the #DIV.. * How do I either keep the #DIV from showing or write the formula in such a way that it basically says: *If I2 is less than, equal to or has no data then leave blank, if not process the above formula.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Write formula to look for 'like' value | Excel Discussion (Misc queries) | |||
How do I write this formula | Excel Discussion (Misc queries) | |||
What is the best way to Write this Formula | Excel Discussion (Misc queries) | |||
how to write a formula | New Users to Excel | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) |