Hi All,

Ok I have created a Workbook using 3 pages Tab1=RPM_Main, Tab2=PPS and
Tab3=Laps.

I have the info from cells on Tab2 and Tab3 going to Tab1, However on
Tab1=RPM_Main in certain cells there is nothing to compute and is given me an
error #DIV/0!

The Formula I am using is In Column "D" is =SUM(B7/2/C7). Now the Column
"C" may have a 0=ZERO brought over from the Tab2 worksheet as there is no
data computed and it is giving me the #DIV/0! error. How can I change this
formula to do away with that Error? I have some other issues with this
Spreadsheet and will discuss each one once I get other issues resolved to
help keep my sanity, LOL.

Thanks Much In advance.

Ron

=IF(C7="","",(B7/2/C7))

You don't need the SUM function for your example.


Gord Dibben MS Excel MVP

On Sun, 2 Dec 2007 15:38:00 -0800, LowRider
wrote:

Hi All,

Ok I have created a Workbook using 3 pages Tab1=RPM_Main, Tab2=PPS and
Tab3=Laps.

I have the info from cells on Tab2 and Tab3 going to Tab1, However on
Tab1=RPM_Main in certain cells there is nothing to compute and is given me an
error #DIV/0!

The Formula I am using is In Column "D" is =SUM(B7/2/C7). Now the Column
"C" may have a 0=ZERO brought over from the Tab2 worksheet as there is no
data computed and it is giving me the #DIV/0! error. How can I change this
formula to do away with that Error? I have some other issues with this
Spreadsheet and will discuss each one once I get other issues resolved to
help keep my sanity, LOL.

Thanks Much In advance.

Ron

The Sum() is superfluous, try this to eliminate errors:

=IF(AND(C7<0,ISNUMBER(C7)),B7/2/C7,0)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"LowRider" wrote in message
...
Hi All,

Ok I have created a Workbook using 3 pages Tab1=RPM_Main, Tab2=PPS and
Tab3=Laps.

I have the info from cells on Tab2 and Tab3 going to Tab1, However on
Tab1=RPM_Main in certain cells there is nothing to compute and is given me
an
error #DIV/0!

The Formula I am using is In Column "D" is =SUM(B7/2/C7). Now the Column
"C" may have a 0=ZERO brought over from the Tab2 worksheet as there is no
data computed and it is giving me the #DIV/0! error. How can I change
this
formula to do away with that Error? I have some other issues with this
Spreadsheet and will discuss each one once I get other issues resolved to
help keep my sanity, LOL.

Thanks Much In advance.

Ron

Ok I have entered the formula you provided, BTW Thank You, but I am still
getting the #DIV/0! Error in Column D where this formula is being placed.

Thanks again for the help.

Ron



"Gord Dibben" wrote:

=IF(C7="","",(B7/2/C7))

You don't need the SUM function for your example.


Gord Dibben MS Excel MVP

On Sun, 2 Dec 2007 15:38:00 -0800, LowRider
wrote:

Hi All,

Ok I have created a Workbook using 3 pages Tab1=RPM_Main, Tab2=PPS and
Tab3=Laps.

I have the info from cells on Tab2 and Tab3 going to Tab1, However on
Tab1=RPM_Main in certain cells there is nothing to compute and is given me an
error #DIV/0!

The Formula I am using is In Column "D" is =SUM(B7/2/C7). Now the Column
"C" may have a 0=ZERO brought over from the Tab2 worksheet as there is no
data computed and it is giving me the #DIV/0! error. How can I change this
formula to do away with that Error? I have some other issues with this
Spreadsheet and will discuss each one once I get other issues resolved to
help keep my sanity, LOL.

Thanks Much In advance.

Ron

Hi Ragdyer,

Ok that seemed to work great and Thank You.

Now on to my next issue with this spreadsheet as there are a couple issues
if you don't mind.

In Tab2 = PPS. I have to keep track of Point Penalties for each week. I
need a TOTAL point Penalties and Current Point Penalties Columns.

The Total Point Penalties I have to add any Point Penalty for each week
which I know is say =SUM(C1:J1) entered which will add all points penalties
in those cells together and give a total in the K1 Cell per the formula. But
if I subtract a point penalty or 2 by adding in a cell -1 between c1 and j1
it will subtract that from the total in k1. That is good as thats what I
want it to do, but I need another column (Current Point Penalties) that will
keep ALL +Penalty totals without subtracting any point penalties that is
entered in any given cell as -1, -2 and so on.

Hope I explained that right.

Thanks Much in advance.

Ron



"Ragdyer" wrote:

The Sum() is superfluous, try this to eliminate errors:

=IF(AND(C7<0,ISNUMBER(C7)),B7/2/C7,0)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"LowRider" wrote in message
...
Hi All,

Ok I have created a Workbook using 3 pages Tab1=RPM_Main, Tab2=PPS and
Tab3=Laps.

I have the info from cells on Tab2 and Tab3 going to Tab1, However on
Tab1=RPM_Main in certain cells there is nothing to compute and is given me
an
error #DIV/0!

The Formula I am using is In Column "D" is =SUM(B7/2/C7). Now the Column
"C" may have a 0=ZERO brought over from the Tab2 worksheet as there is no
data computed and it is giving me the #DIV/0! error. How can I change
this
formula to do away with that Error? I have some other issues with this
Spreadsheet and will discuss each one once I get other issues resolved to
help keep my sanity, LOL.

Thanks Much In advance.

Ron

I cannot replicate.

100 in B7

10 in C7

Formula =IF(C7="","",(B7/2/C7)) in D7 returns 5

0 or blank in C7 returns ""


Gord


On Sun, 2 Dec 2007 16:34:00 -0800, LowRider
wrote:

Ok I have entered the formula you provided, BTW Thank You, but I am still
getting the #DIV/0! Error in Column D where this formula is being placed.

Thanks again for the help.

Ron



"Gord Dibben" wrote:

=IF(C7="","",(B7/2/C7))

You don't need the SUM function for your example.


Gord Dibben MS Excel MVP

On Sun, 2 Dec 2007 15:38:00 -0800, LowRider
wrote:

Hi All,

Ok I have created a Workbook using 3 pages Tab1=RPM_Main, Tab2=PPS and
Tab3=Laps.

I have the info from cells on Tab2 and Tab3 going to Tab1, However on
Tab1=RPM_Main in certain cells there is nothing to compute and is given me an
error #DIV/0!

The Formula I am using is In Column "D" is =SUM(B7/2/C7). Now the Column
"C" may have a 0=ZERO brought over from the Tab2 worksheet as there is no
data computed and it is giving me the #DIV/0! error. How can I change this
formula to do away with that Error? I have some other issues with this
Spreadsheet and will discuss each one once I get other issues resolved to
help keep my sanity, LOL.

Thanks Much In advance.

Ron

Not sure why, but I did this on a new line (line 43) and changed the formula
to reflect line 43. Entered 50 on B43 and 0 on C43 with the formula in D43
and still got the error. Not sure why I would get it if you aren't. Crazy.

Again thanks for the help. :-)

Ron

"Gord Dibben" wrote:

I cannot replicate.

100 in B7

10 in C7

Formula =IF(C7="","",(B7/2/C7)) in D7 returns 5

0 or blank in C7 returns ""


Gord


On Sun, 2 Dec 2007 16:34:00 -0800, LowRider
wrote:

Ok I have entered the formula you provided, BTW Thank You, but I am still
getting the #DIV/0! Error in Column D where this formula is being placed.

Thanks again for the help.

Ron



"Gord Dibben" wrote:

=IF(C7="","",(B7/2/C7))

You don't need the SUM function for your example.


Gord Dibben MS Excel MVP

On Sun, 2 Dec 2007 15:38:00 -0800, LowRider
wrote:

Hi All,

Ok I have created a Workbook using 3 pages Tab1=RPM_Main, Tab2=PPS and
Tab3=Laps.

I have the info from cells on Tab2 and Tab3 going to Tab1, However on
Tab1=RPM_Main in certain cells there is nothing to compute and is given me an
error #DIV/0!

The Formula I am using is In Column "D" is =SUM(B7/2/C7). Now the Column
"C" may have a 0=ZERO brought over from the Tab2 worksheet as there is no
data computed and it is giving me the #DIV/0! error. How can I change this
formula to do away with that Error? I have some other issues with this
Spreadsheet and will discuss each one once I get other issues resolved to
help keep my sanity, LOL.

Thanks Much In advance.

Ron

You can separate the totals for the +'s and the -'s by using Sumif().

For actual total:
=Sum(C1:J1)

For totaling only +'s:
=Sumif(C1:J1,"0")

For totaling only -'s:
=Sumif(C1:J1,"<0")
OR ... if you don't want the minus sign to show:
=Abs(Sumif(C1:J1,"<0"))
Or
=-Sumif(C1:J1,"<0")
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"LowRider" wrote in message
...
Hi Ragdyer,

Ok that seemed to work great and Thank You.

Now on to my next issue with this spreadsheet as there are a couple issues
if you don't mind.

In Tab2 = PPS. I have to keep track of Point Penalties for each week. I
need a TOTAL point Penalties and Current Point Penalties Columns.

The Total Point Penalties I have to add any Point Penalty for each week
which I know is say =SUM(C1:J1) entered which will add all points
penalties
in those cells together and give a total in the K1 Cell per the formula.
But
if I subtract a point penalty or 2 by adding in a cell -1 between c1 and
j1
it will subtract that from the total in k1. That is good as thats what I
want it to do, but I need another column (Current Point Penalties) that
will
keep ALL +Penalty totals without subtracting any point penalties that is
entered in any given cell as -1, -2 and so on.

Hope I explained that right.

Thanks Much in advance.

Ron



"Ragdyer" wrote:

The Sum() is superfluous, try this to eliminate errors:

=IF(AND(C7<0,ISNUMBER(C7)),B7/2/C7,0)
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"LowRider" wrote in message
...
Hi All,

Ok I have created a Workbook using 3 pages Tab1=RPM_Main, Tab2=PPS and
Tab3=Laps.

I have the info from cells on Tab2 and Tab3 going to Tab1, However on
Tab1=RPM_Main in certain cells there is nothing to compute and is
given me
an
error #DIV/0!

The Formula I am using is In Column "D" is =SUM(B7/2/C7). Now the
Column
"C" may have a 0=ZERO brought over from the Tab2 worksheet as there is
no
data computed and it is giving me the #DIV/0! error. How can I change
this
formula to do away with that Error? I have some other issues with
this
Spreadsheet and will discuss each one once I get other issues resolved
to
help keep my sanity, LOL.

Thanks Much In advance.

Ron

<<< 0 or blank in C7 returns ""

Not in my XL97 Gord.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
I cannot replicate.

100 in B7

10 in C7

Formula =IF(C7="","",(B7/2/C7)) in D7 returns 5

0 or blank in C7 returns ""


Gord


On Sun, 2 Dec 2007 16:34:00 -0800, LowRider

wrote:

Ok I have entered the formula you provided, BTW Thank You, but I am still
getting the #DIV/0! Error in Column D where this formula is being placed.

Thanks again for the help.

Ron



"Gord Dibben" wrote:

=IF(C7="","",(B7/2/C7))

You don't need the SUM function for your example.


Gord Dibben MS Excel MVP

On Sun, 2 Dec 2007 15:38:00 -0800, LowRider

wrote:

Hi All,

Ok I have created a Workbook using 3 pages Tab1=RPM_Main, Tab2=PPS and
Tab3=Laps.

I have the info from cells on Tab2 and Tab3 going to Tab1, However on
Tab1=RPM_Main in certain cells there is nothing to compute and is
given me an
error #DIV/0!

The Formula I am using is In Column "D" is =SUM(B7/2/C7). Now the
Column
"C" may have a 0=ZERO brought over from the Tab2 worksheet as there is
no
data computed and it is giving me the #DIV/0! error. How can I change
this
formula to do away with that Error? I have some other issues with
this
Spreadsheet and will discuss each one once I get other issues resolved
to
help keep my sanity, LOL.

Thanks Much In advance.

Ron

Thanks RD

Should have said "blank" in C7 returns ""

Originally read "in certain cells there is nothing to compute" and took that as
blank.

Maybe better would be =IF(OR(C7="",(C7=0)),"",(B7/2/C7))


Gord

On Sun, 2 Dec 2007 18:42:58 -0800, "Ragdyer" wrote:

<<< 0 or blank in C7 returns ""

Not in my XL97 Gord.