Not sure how to write the formula

[Caligirl]

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.

 

[Gary''s Student]

=IF(I2="","",F2-J2/I2)

 

[BoniM]

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]

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?

 

[Caligirl]

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?

 

[JP]

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

 

[Pete_UK]

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

 

[Caligirl]

how do I write such a 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

 

[Caligirl]

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]

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

 

[Pete_UK]

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