Some Help Needed on getting info out of a cell from a formula

  • Thread starter Thread starter LowRider
  • Start date Start date
L

LowRider

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
 
The Sum() is superfluous, try this to eliminate errors:

=IF(AND(C7<>0,ISNUMBER(C7)),B7/2/C7,0)
 
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
 
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
 
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
 
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
 
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")
 
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
 
Back
Top