What's wrong with this formula?

  • Thread starter Thread starter Anna
  • Start date Start date
A

Anna

Hi,
I'm trying to create a complicated spreadsheet for Course Enrollment
and calculating final grades for students. I can't figure out HOW to
do OR.

I need this formula to work correctly, but it is not. What am I doing
wrong?
=IF(L3=0 or Q3=0,"",T3/4)

The formula works correctly if I just use =IF(Q3=0,"",T3/4). But when
I want to add another column, L3, to this formula and I keep get an
error.

I know I am not be using OR correctly.

Basically I want to say IF L3 is blank AND Q3 is blank, then don't
divide T3/4 and leave the cell blank.

Thanks for any help with this you can give me. I mayl have more
questions on this same spreadsheet too.
Sharon
 
PERFECT. THANKS!!!

Now I need one more formula.

If L3 =0 and P3 =0, then display nothing in my cell (X3). But if P3
has a value, then V3+U3 will be displayed in my X3 cell.

So I tried something like this:
=IF(L3=0,p3=0),"'',U3+V3)

But I have an error. How should this formula work?
 
If L3 =0 and P3 =0
So I tried something like this:
=IF(L3=0,p3=0),"'',U3+V3)
Click to expand...

Do the same thing with AND that you did with OR:

=IF(AND(L3=0,P3=0),"",U3+V3)


--
Biff
Microsoft Excel MVP


PERFECT. THANKS!!!

Now I need one more formula.

If L3 =0 and P3 =0, then display nothing in my cell (X3). But if P3
has a value, then V3+U3 will be displayed in my X3 cell.

So I tried something like this:
=IF(L3=0,p3=0),"'',U3+V3)

But I have an error. How should this formula work?
 
OK... thanks for the quick reply. I see what you mean about putting in
AND.. that helps me understand better. But this formula didn't do
exactly what I need. I think I explained it wrong.

IF there is a value of any kind in L3, then my X3 cell will be blank.
A value in L3 means that I don't want it to calculate anything in X3.
I just want it blank.

And If there is NO value in L3 AND NO value in P3, then I still want
X3 to be blank.

BUT when there is a value entered into P3, then U3+V3 will be
displayed in X3 cell.

The formula below actually puts a value in X3 when there is a value
entered in L3

What do you think?
 
Place the argument in quotes if you want to see it as text.

=IF(AND(L3=0,P3=0),"","U3+V3")


Gord Dibben MS Excel MVP
 
Ok.. ALMOST what I want.

=IF(AND(L3=0,P3=0),"","U3+V3")

I don't want text, I want the value, so I took out the "" around the
U3+V3. And I got the right value in my X3 cell..YEAH.

BUT..... IF there is a number typed into L3 (whether or not there is
a number in p3), my cell(X3) needs to be empty and it is not. If there
is a number in L3 I am getting the U3+V3 value)

It is like this:
If a number is in L3, then don't calculate anything in the X3 cell. I
want it blank.
But if there is NO number in L3, then I want Excel to check and see if
there is a number in P3.
IF P3 is blank, then I want my cell (X3) to be empty too.
But if P3 has a value and L3 is blank, then I want the value of U3+V3.

You see if there ia number in L3 and P3, then I have a value in W3...
and this is working ok. In real life terms, if L3 has an EOC score,
then the formula must be calculated one way and it appears in the W
column. And if there is NO EOC score in L3, then the formula is
calculatedted another way and I have it appearing in the X column. In
either case, I have a grade in one column and blank in the other.

Sorry to be so confusing. It is complicated, but I know there is some
way to do it. Thanks for not giving up on me.




Sorry to be so confusing... I hope this makes sense.
 
Ok.. ALMOST what I want.

=IF(AND(L3=0,P3=0),"","U3+V3")

I don't want text, I want the value, so I took out the "" around the
U3+V3. And I got the right value in my X3 cell..YEAH.

BUT..... IF there is a number typed into L3 (whether or not there is
a number in p3), my cell(X3) needs to be empty and it is not. If there
is a number in L3 I am getting the U3+V3 value)
Click to expand...

In X3 a partial solution is
=IF(L3=0,"L3 is blank","")

Now whenever the above would write "L3 is blank" you want
either if P3 is blank. a blank or if not blank U3+V3 which is IF(P3=0,"",U3+V3)

So substituting
=IF(L3=0,IF(P3=0,"",U3+V3),"")
 
=IF(L3=0,IF(P3=0,"",U3+V3),"")

Yes... that works!!! I kept putting in all these values, but I didn't
have them in the right order. I never thought of using the "" twice.

Thanks so much!
 
I think the logic can be written a little more simply:

=IF(AND(L3=0,P3<>0),U3+V3,"")

You may also be able to get away with this shorter version

=IF(AND(L3=0,P3),U3+V3,"")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


Sharon said:
=IF(L3=0,IF(P3=0,"",U3+V3),"")

Yes... that works!!! I kept putting in all these values, but I didn't
have them in the right order. I never thought of using the "" twice.

Thanks so much!
Click to expand...
 
Yes, both of the formulas work. I understand the 2nd formula, but what
does the <> do? less than, greater than... not sure I understand that.
I am guesing it means if P3 has a value that is not 0, but why
wouldn't P3>0 do the same thing?
 
On a second try, the 2nd formula returns the value of u3+v3 even
though P3 is blank. So the first formula you sent works. Still would
like to understand the <> symbols a lttle better.

Thanks so much. Here are the formulas that work:

Dave's formula:
=IF(L3=0,IF(P3=0,"",U3+V3),"")

your formula:
=IF(AND(L3=0,P3<>0),U3+V3,"")

I appreciate everyone's help.
 
equal =
NOT equal<>
greater than>
less than <


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
On a second try, the 2nd formula returns the value of u3+v3 even
though P3 is blank. So the first formula you sent works. Still would
like to understand the <> symbols a lttle better.

Thanks so much. Here are the formulas that work:

Dave's formula:
=IF(L3=0,IF(P3=0,"",U3+V3),"")

your formula:
=IF(AND(L3=0,P3<>0),U3+V3,"")

I appreciate everyone's help.
 
Ok, I have one more idea for my spreadsheet. It would be even better
IF I could add to one of the two formulas below

IF L3=0 OR K3 does not contains the symbol ~
with the rest of the value the same.

The K3 cell might contain ~Algebra_1 or it might contact English_2.
The cells that contain the courses with the ~ will have an EOC score
in the L3 column and therefore calculate one way in my w3 cell. So
both the absences of ~ and a blank L3 must be present for the grade to
calculate correctly.

1st formula
=IF(L3=0,IF(P3=0,"",U3+V3),"")
or
2nd formula
=IF(AND(L3=0,P3<>0),U3+V3,"")
Click to expand...
 
Back
Top