IF(AND)

[Guest]

Hopefully, someone can point me in the right direction here. I have entered
the following eqaution into one of my sheets...

=IF(AND(AC3=1,AD3=3),6,0)

Now, even when AC3=1 and AD3=3, I'm still getting 0 as the result when I
want it to be 6.

I actually have 12 different combos to test for, but I'm trying to get just
one working right now. I'll cross that other bridge when I manage to get past
this one.

Thanks in advance.

 

[Guest]

Your formula works for me;
Check your formatting in all 3 cells.

 

[Guest]

Your formula works for me;
Check your formatting in all 3 cells.

 

[Dave Peterson]

Do you have calculation set to manual?

Check under tools|Options|Calculation tab. Try making it automatic.

 

[Dave Peterson]

Do you have calculation set to manual?

Check under tools|Options|Calculation tab. Try making it automatic.

 

[Guest]

Ok, that has to be it then. But, I'm at a loss to how to fix it. Even though
the results in AC3 and AD3 are numbers, they are showing up as if they were
text, i.e. in the left side of the cell instead of the right. Maybe it has to
do with the formulas for each of the cells? AC3 is =LEFT(M3,2) and AD3 is
=IF(G3="G1","1",IF(G3="G2","2",IF(G3="G3","3",IF(G3="Stk","4")))) Or because
the cells that those formulas are pulling their info from are "text" cells.
Hmmmm...I have no idea. Shouldn't the original formula work even if it were
text as long as the answers match to each part of the formula? I don't know,
just thinking out loud...

 

[Guest]

Ok, that has to be it then. But, I'm at a loss to how to fix it. Even though
the results in AC3 and AD3 are numbers, they are showing up as if they were
text, i.e. in the left side of the cell instead of the right. Maybe it has to
do with the formulas for each of the cells? AC3 is =LEFT(M3,2) and AD3 is
=IF(G3="G1","1",IF(G3="G2","2",IF(G3="G3","3",IF(G3="Stk","4")))) Or because
the cells that those formulas are pulling their info from are "text" cells.
Hmmmm...I have no idea. Shouldn't the original formula work even if it were
text as long as the answers match to each part of the formula? I don't know,
just thinking out loud...

 

[Guest]

Just checked, it's set to automatic.

Do you have calculation set to manual?

Check under tools|Options|Calculation tab. Try making it automatic.

 

[Guest]

Just checked, it's set to automatic.

Do you have calculation set to manual?

Check under tools|Options|Calculation tab. Try making it automatic.

 

[Gord Dibben]

Works for me.

Perhaps the numbers in AC3 and AD3 are text values that look like numbers.

Reformat to General and re-enter the numbers or if many, copy an empty cell,
select the range of values and Paste Special>Add>OK>Esc.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Works for me.

Perhaps the numbers in AC3 and AD3 are text values that look like numbers.

Reformat to General and re-enter the numbers or if many, copy an empty cell,
select the range of values and Paste Special>Add>OK>Esc.


Gord Dibben MS Excel MVP

 

[Guest]

Options:

AC3: =VALUE(Left(M3,2))
AD3: =IF(G3="G1",1,IF(G3="G2",2,IF(G3="G3",3,IF(G3="Stk",4))))

OR

leaving AC3/AD3 unchanged:

=IF(AND(VALUE(AC3)=1,VALUE(AD3)=3),6,0)

HTH

 

[Guest]

Options:

AC3: =VALUE(Left(M3,2))
AD3: =IF(G3="G1",1,IF(G3="G2",2,IF(G3="G3",3,IF(G3="Stk",4))))

OR

leaving AC3/AD3 unchanged:

=IF(AND(VALUE(AC3)=1,VALUE(AD3)=3),6,0)

HTH

 

[Guest]

Try:

=MOD((B1-A1),1)*24

HTH

 

[Guest]

Try:

=MOD((B1-A1),1)*24

HTH

 

[Gord Dibben]

Remove the " " from around the numbers 1, 2, 3 and 4 in your formula.

They are causing the numbers to be returned as text.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Remove the " " from around the numbers 1, 2, 3 and 4 in your formula.

They are causing the numbers to be returned as text.


Gord Dibben MS Excel MVP

 

[Guest]

.....wrong post!!!

Try:

=MOD((B1-A1),1)*24

HTH

 

[Guest]

.....wrong post!!!

Try:

=MOD((B1-A1),1)*24

HTH

 

[Guest]

Thanks to all. I have it working now.

Try:

=MOD((B1-A1),1)*24

HTH