Please help:

I have an Excel 2007 formula problem. I think the problem that I have
requires an IF function and I've been trying to put it together but it's a 7
part formula and they keep canceling each other out somehow. I have several
scenarios that I'd like to create in one formula for one cell: it goes like
this:

D1 should be the result of:

Part 1: If A1 has a numerical value entered, and B1 is blank and C1 is Blank
then D1 should Display 1 (I got this
far:=IF(A1<"",1,""*AND(B1="",1,""*AND(C1="",1,"") )))
Part 2: If A1 is blank, B1 has a numerical value and C1 is blank the D1
should display 2
Part 3: If A1 is blank, and B1 is blank but C1 has a numerical value then D1
should display 3.
Part 4: If A1 has a numerical value and B1 has a numerical value but C1 is
blank then D1 should display 4
Part 5: If A1 has a numerical value, but B1 is blank and C1 has a numerical
value then D1 should display 5
Part 6: If A1 is blank but B1 and C1 each have a numerical value then D1
should display 6
Part 7: If A1, B1, and C1 each have a numerical value in their cells then D1
should display 7.

That's all of it. I would really appreciate the help. Thanks in advance.

=IF(A1="",IF(B1="",IF(C1="","answer
undefined",3),IF(C1="",2,6)),IF(B1="",IF(C1="",1,5 ),IF(C1="",4,7)))

Note that you haven't specified what happens if any of the input cells are
not blank but not numeric (i.e. if they are text). I have assumed that you
want text treated the same as numbers.
--
David Biddulph

"JE" wrote in message
...
Please help:

I have an Excel 2007 formula problem. I think the problem that I have
requires an IF function and I've been trying to put it together but it's a
7
part formula and they keep canceling each other out somehow. I have
several
scenarios that I'd like to create in one formula for one cell: it goes
like
this:

D1 should be the result of:

Part 1: If A1 has a numerical value entered, and B1 is blank and C1 is
Blank
then D1 should Display 1 (I got this
far:=IF(A1<"",1,""*AND(B1="",1,""*AND(C1="",1,"") )))
Part 2: If A1 is blank, B1 has a numerical value and C1 is blank the D1
should display 2
Part 3: If A1 is blank, and B1 is blank but C1 has a numerical value then
D1
should display 3.
Part 4: If A1 has a numerical value and B1 has a numerical value but C1 is
blank then D1 should display 4
Part 5: If A1 has a numerical value, but B1 is blank and C1 has a
numerical
value then D1 should display 5
Part 6: If A1 is blank but B1 and C1 each have a numerical value then D1
should display 6
Part 7: If A1, B1, and C1 each have a numerical value in their cells then
D1
should display 7.

That's all of it. I would really appreciate the help. Thanks in advance.

You were on the right track try the following formula

Here is how I sometimes lay formulas out if they get really complicated.
Below is what you can paste into the cell.
=
if(and(isnumber(a1),b1="",c1=""),1,
if(and(a1="",isnumber(b1),c1=""),2,
if(and(a1="",b1="",isnumber(c1)),3,
if(and(isnumber(a1),isnumber(b1),c1=""),4,
if(and(isnumber(a1),b1="",isnumber(c1)),5,
if(and(a1="",isnumber(b1),isnumber(c1)),6,
if(and(isnumber(a1),isnumber(b1),isnumber(c1)),7,
"-")

=if(and(isnumber(a1),b1="",c1=""),1,if(and(a1="",i snumber(b1),c1=""),2,if(and(a1="",b1="",isnumber(c 1)),3,if(and(isnumber(a1),isnumber(b1),c1=""),4,if (and(isnumber(a1),b1="",isnumber(c1)),5,if(and(a1= "",isnumber(b1),isnumber(c1)),6,if(and(isnumber(a1 ),isnumber(b1),isnumber(c1)),7,"-")


"JE" wrote:

Please help:

I have an Excel 2007 formula problem. I think the problem that I have
requires an IF function and I've been trying to put it together but it's a 7
part formula and they keep canceling each other out somehow. I have several
scenarios that I'd like to create in one formula for one cell: it goes like
this:

D1 should be the result of:

Part 1: If A1 has a numerical value entered, and B1 is blank and C1 is Blank
then D1 should Display 1 (I got this
far:=IF(A1<"",1,""*AND(B1="",1,""*AND(C1="",1,"") )))
Part 2: If A1 is blank, B1 has a numerical value and C1 is blank the D1
should display 2
Part 3: If A1 is blank, and B1 is blank but C1 has a numerical value then D1
should display 3.
Part 4: If A1 has a numerical value and B1 has a numerical value but C1 is
blank then D1 should display 4
Part 5: If A1 has a numerical value, but B1 is blank and C1 has a numerical
value then D1 should display 5
Part 6: If A1 is blank but B1 and C1 each have a numerical value then D1
should display 6
Part 7: If A1, B1, and C1 each have a numerical value in their cells then D1
should display 7.

That's all of it. I would really appreciate the help. Thanks in advance.

You never said what you want if the three conditions are NOT met, so I put in
a 0.

Copy and paste the following:

=IF(AND(ISNUMBER(A1),ISBLANK(B1),ISBLANK(C1)),1,0)
+IF(AND(ISBLANK(A1),ISNUMBER(B1),ISBLANK(C1)),2,0)
+IF(AND(ISBLANK(A1),ISBLANK(B1),ISNUMBER(C1)),3,0)
+IF(AND(ISNUMBER(A1),ISNUMBER(B1),ISBLANK(C1)),4,0 )
+IF(AND(ISNUMBER(A1),ISBLANK(B1),ISNUMBER(C1)),5,0 )
+IF(AND(ISBLANK(A1),ISNUMBER(B1),ISNUMBER(C1)),6,0 )
+IF(AND(ISNUMBER(A1),ISNUMBER(B1),ISNUMBER(C1)),7, 0)

Good Luck.

"JE" wrote:

Please help:

I have an Excel 2007 formula problem. I think the problem that I have
requires an IF function and I've been trying to put it together but it's a 7
part formula and they keep canceling each other out somehow. I have several
scenarios that I'd like to create in one formula for one cell: it goes like
this:

D1 should be the result of:

Part 1: If A1 has a numerical value entered, and B1 is blank and C1 is Blank
then D1 should Display 1 (I got this
far:=IF(A1<"",1,""*AND(B1="",1,""*AND(C1="",1,"") )))
Part 2: If A1 is blank, B1 has a numerical value and C1 is blank the D1
should display 2
Part 3: If A1 is blank, and B1 is blank but C1 has a numerical value then D1
should display 3.
Part 4: If A1 has a numerical value and B1 has a numerical value but C1 is
blank then D1 should display 4
Part 5: If A1 has a numerical value, but B1 is blank and C1 has a numerical
value then D1 should display 5
Part 6: If A1 is blank but B1 and C1 each have a numerical value then D1
should display 6
Part 7: If A1, B1, and C1 each have a numerical value in their cells then D1
should display 7.

That's all of it. I would really appreciate the help. Thanks in advance.

Hi,
One way

=ISNUMBER(A1)+ISNUMBER(B1)*2+ISNUMBER(C1)*4

Regards - Dave

Hi,
Sorry, I thought your criteria was following a pure binary series, but I see
that my formula will give you errors for 3 and 4. You would have to modify it:
=IF((ISNUMBER(A1)+ISNUMBER(B1)*2+ISNUMBER(C1)*4)=3 ,4,IF((ISNUMBER(A1)+ISNUMBER(B1)*2+ISNUMBER(C1)*4) =4,3,ISNUMBER(A1)+ISNUMBER(B1)*2+ISNUMBER(C1)*4))

Regards - Dave

"Dave" wrote:

Hi,
One way

=ISNUMBER(A1)+ISNUMBER(B1)*2+ISNUMBER(C1)*4

Regards - Dave

Thanks to everyone for all of your help. It works beautifully!!!

"JE" wrote:

Please help:

I have an Excel 2007 formula problem. I think the problem that I have
requires an IF function and I've been trying to put it together but it's a 7
part formula and they keep canceling each other out somehow. I have several
scenarios that I'd like to create in one formula for one cell: it goes like
this:

D1 should be the result of:

Part 1: If A1 has a numerical value entered, and B1 is blank and C1 is Blank
then D1 should Display 1 (I got this
far:=IF(A1<"",1,""*AND(B1="",1,""*AND(C1="",1,"") )))
Part 2: If A1 is blank, B1 has a numerical value and C1 is blank the D1
should display 2
Part 3: If A1 is blank, and B1 is blank but C1 has a numerical value then D1
should display 3.
Part 4: If A1 has a numerical value and B1 has a numerical value but C1 is
blank then D1 should display 4
Part 5: If A1 has a numerical value, but B1 is blank and C1 has a numerical
value then D1 should display 5
Part 6: If A1 is blank but B1 and C1 each have a numerical value then D1
should display 6
Part 7: If A1, B1, and C1 each have a numerical value in their cells then D1
should display 7.

That's all of it. I would really appreciate the help. Thanks in advance.