IF(AND)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DJ - 19 Aug 2006 17:01 GMT

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.

Reply to this Message

Jim May - 19 Aug 2006 17:16 GMT

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

> Hopefully, someone can point me in the right direction here. I have entered
> the following eqaution into one of my sheets...
[quoted text clipped - 9 lines]
>
> Thanks in advance.

Reply to this Message

DJ - 19 Aug 2006 17:39 GMT

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...

> Your formula works for me;
> Check your formatting in all 3 cells.
[quoted text clipped - 12 lines]
> >
> > Thanks in advance.

Reply to this Message

Gord Dibben - 19 Aug 2006 18:20 GMT

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

>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
[quoted text clipped - 22 lines]
>> >
>> > Thanks in advance.

Reply to this Message

Dave Peterson - 19 Aug 2006 17:31 GMT

Do you have calculation set to manual?

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

> Hopefully, someone can point me in the right direction here. I have entered
> the following eqaution into one of my sheets...
[quoted text clipped - 9 lines]
>
> Thanks in advance.

Signature

Dave Peterson

Reply to this Message

DJ - 19 Aug 2006 17:43 GMT

Just checked, it's set to automatic.

> Do you have calculation set to manual?
>
[quoted text clipped - 13 lines]
> >
> > Thanks in advance.

Reply to this Message

Toppers - 19 Aug 2006 17:55 GMT

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

> Just checked, it's set to automatic.
>
[quoted text clipped - 15 lines]
> > >
> > > Thanks in advance.

Reply to this Message

Gord Dibben - 19 Aug 2006 17:54 GMT

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

>Hopefully, someone can point me in the right direction here. I have entered
>the following eqaution into one of my sheets...
[quoted text clipped - 9 lines]
>
>Thanks in advance.

Reply to this Message

Toppers - 19 Aug 2006 18:10 GMT

Try:

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

HTH

> Hopefully, someone can point me in the right direction here. I have entered
> the following eqaution into one of my sheets...
[quoted text clipped - 9 lines]
>
> Thanks in advance.

Reply to this Message

Toppers - 19 Aug 2006 18:34 GMT

....wrong post!!!

> Try:
>
[quoted text clipped - 15 lines]
> >
> > Thanks in advance.

Reply to this Message

DJ - 19 Aug 2006 18:51 GMT

Thanks to all. I have it working now.

> Try:
>
[quoted text clipped - 15 lines]
> >
> > Thanks in advance.

Reply to this Message