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.

GarysStudent
Fri Mar 14 11:42:00 CDT 2008

=IF(I2="","",F2-J2/I2)
--
Gary''s Student - gsnu200773

BoniM
Fri Mar 14 12:47:00 CDT 2008

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.

Caligirl
Fri Mar 14 13:36:01 CDT 2008

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

Caligirl
Fri Mar 14 13:56:03 CDT 2008

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.

JP
Fri Mar 14 14:22:18 CDT 2008

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")) =3D 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=A0pm, Caligirl <Calig...@discussions.microsoft.com>
wrote:
> Okay, I got it. =A0I just had to add this to your formula (f2-j2)/i2. =A0T=
hat
> made it work. =A0Thank you so very much. =A0Now I can move on to the next
> dilemna. =A0
>
> Here's one for you: =A0If cells c thru o are equal to zero then delete the=

> row? =A0How in the world would I write that?
>
>
>
> "Caligirl" wrote:
> > I am fairly new to writing formulas. =A0The formula I always use is F2-J=
2/I2. =A0
> > Pretty simple, except when I2 has no data or is zero then I get the #DIV=
. =A0
> > How do I either keep the #DIV from showing or write the formula in such =
a way
> > that it basically says: =A0If I2 is less than, equal to or has no data t=
hen
> > leave blank, if not process the above formula.- Hide quoted text -
>
> - Show quoted text -

Pete_UK
Fri Mar 14 14:22:40 CDT 2008

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=A0pm, Caligirl <Calig...@discussions.microsoft.com>
wrote:
> Okay, I got it. =A0I just had to add this to your formula (f2-j2)/i2. =A0T=
hat
> made it work. =A0Thank you so very much. =A0Now I can move on to the next
> dilemna. =A0
>
> Here's one for you: =A0If cells c thru o are equal to zero then delete the=

> row? =A0How in the world would I write that?
>
>
>
> "Caligirl" wrote:
> > I am fairly new to writing formulas. =A0The formula I always use is F2-J=
2/I2. =A0
> > Pretty simple, except when I2 has no data or is zero then I get the #DIV=
. =A0
> > How do I either keep the #DIV from showing or write the formula in such =
a way
> > that it basically says: =A0If I2 is less than, equal to or has no data t=
hen
> > leave blank, if not process the above formula.- Hide quoted text -
>
> - Show quoted text -

Caligirl
Fri Mar 14 16:10:06 CDT 2008

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 <Calig...@discussions.microsoft.com>
> 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 -
>
>

Caligirl
Fri Mar 14 16:12:01 CDT 2008

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 <Calig...@discussions.microsoft.com>
> 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 -
>
>

JP
Fri Mar 14 17:48:58 CDT 2008

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")) =3D 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=A0pm, Caligirl <Calig...@discussions.microsoft.com>
wrote:
> this sounds like what I need. =A0When I run it I get this error message:
> Unable to get the Sum Property of the Worksheet Function Class
>
> Any ideas?
>
>
>

Pete_UK
Fri Mar 14 18:44:29 CDT 2008

Put this in P2:

=3DIF(SUM(C2:O2)=3D0,"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=A0pm, Caligirl <Calig...@discussions.microsoft.com>
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 <Calig...@discussions.microsoft.com>
> > wrote:
> > > Okay, I got it. =A0I just had to add this to your formula (f2-j2)/i2. =
=A0That
> > > made it work. =A0Thank you so very much. =A0Now I can move on to the n=
ext
> > > dilemna. =A0
>
> > > Here's one for you: =A0If cells c thru o are equal to zero then delete=
the
> > > row? =A0How in the world would I write that?
>
> > > "Caligirl" wrote:
> > > > I am fairly new to writing formulas. =A0The formula I always use is =
F2-J2/I2. =A0
> > > > Pretty simple, except when I2 has no data or is zero then I get the =
#DIV.. =A0
> > > > How do I either keep the #DIV from showing or write the formula in s=
uch a way
> > > > that it basically says: =A0If I2 is less than, equal to or has no da=
ta then
> > > > leave blank, if not process the above formula.- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -