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

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
 
G

Gord Dibben

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

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


Gord Dibben MS Excel MVP
 
R

Ragdyer

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

=IF(AND(C7<>0,ISNUMBER(C7)),B7/2/C7,0)
 
L

LowRider

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
 
L

LowRider

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
 
G

Gord Dibben

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
 
L

LowRider

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
 
R

Ragdyer

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")
 
G

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top