IF(AND)

[DJ]

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.

[Jim May]

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

"DJ" wrote:

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

[Dave Peterson]

Do you have calculation set to manual?

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

DJ wrote:
>
> 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.

--

Dave Peterson

[DJ]

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

"Jim May" wrote:

> Your formula works for me;
> Check your formatting in all 3 cells.
>
> "DJ" wrote:
>
> > 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.

[DJ]

Just checked, it's set to automatic.

"Dave Peterson" wrote:

> Do you have calculation set to manual?
>
> Check under tools|Options|Calculation tab. Try making it automatic.
>
> DJ wrote:
> >
> > 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.
>
> --
>
> Dave Peterson
>

[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

On Sat, 19 Aug 2006 09:01:01 -0700, DJ <[email protected]> wrote:

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

[Toppers]

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

"DJ" wrote:

> Just checked, it's set to automatic.
>
> "Dave Peterson" wrote:
>
> > Do you have calculation set to manual?
> >
> > Check under tools|Options|Calculation tab. Try making it automatic.
> >
> > DJ wrote:
> > >
> > > 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.
> >
> > --
> >
> > Dave Peterson
> >

[Toppers]

Try:

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

HTH

"DJ" wrote:

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

[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

On Sat, 19 Aug 2006 09:39:01 -0700, DJ <[email protected]> wrote:

>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...
>
>"Jim May" wrote:
>
>> Your formula works for me;
>> Check your formatting in all 3 cells.
>>
>> "DJ" wrote:
>>
>> > 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.

[Toppers]

.....wrong post!!!

"Toppers" wrote:

> Try:
>
> =MOD((B1-A1),1)*24
>
> HTH
>
> "DJ" wrote:
>
> > 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.

[DJ]

Thanks to all. I have it working now.

"Toppers" wrote:

> Try:
>
> =MOD((B1-A1),1)*24
>
> HTH
>
> "DJ" wrote:
>
> > 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.