Excel formula / IF function

[JE]

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.

 

[JNW]

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="",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,"-")

 

[ND Pard]

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.

 

[Dave]

Hi,
One way

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

Regards - Dave

 

[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

 

[JE]

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