Excel formula help

[g3lo18]

Hi,

I am working with excel as a hobby and creating a lottery checker for fun to gain some experience with excel and its formula's.

I am trying to figure out how to do the following.

I have two tables set up.

In one you have 7 numbers you enter and in another table you have 7 numbers that are drawn. I am trying to figure out how tell excel to compare the 7 numbers drawn to the numbers you entered. If all 7 numbers match, than I want excel to produce a 1. If not I want excel to produce a 0.

Any help is greatly appreciated. Each number is in its own cell so I have 7 cells across.

 

[g3lo18]

I tried this to make it work.

=IF(--(SUMPRODUCT((C4:I4)/(C41:I41))=7)=1,1,0)

However, if there is nothing in the cell it displays #div might work for what I need it for!

 

[bschwartz]

What version of Excel are you using?

 

[bschwartz]

This IF statement that you posted above would accomplish the same thing as the IFERROR for earlier versions:

=IF(--(SUMPRODUCT((C4:I4)/(C41:I41))=7)=1,1,0)

Is it working for you?

 

[g3lo18]

Yeah it worked thank you!

In regards to numbers not being entered. If using the formula it displays #DIV/0! is there a way to display 0 instead of that if no numbers are entered?

 

[g3lo18]

Wait that idea does not work. Because if the lottery numbers are different, but the total of them are the same, it would display 1. However, I need it to display 0 when numbers do not match. I need to find a way to compare each number so it matches exactly.

 

[bschwartz]

Can you post an example of when it does not work? I'm having trouble replicating that.

 

[g3lo18]

What I had before is =IF(AND(A1=A41,B1=C41,D1=D41,E1=E41,F1=F42,G1=G42),1,0)

That works, however if A1:G1 cells are blank, and A41:G41 cells are blank it still provides me with a value 1. When I want it to provide me with a value 0.

 

[g3lo18]

I got it!

=IF(AND(C41=0,D41=0,E41=0,F41=0,G41=0,H41=0,I41=0),0,IF(AND(C4=C41,D4=D41,E4=E41,F4=F41,G4=G41,H4=H41,I4=I41),1,0))

 

[sdsurzh]

Hi g3lo18,

Which version are you using actually IFERROR will work on 2007 & above.

Thank You,
SK

 

[Aladin Akyurek]

What I had before is =IF(AND(A1=A41,B1=C41,D1=D41,E1=E41,F1=F42,G1=G42),1,0)

That works, however if A1:G1 cells are blank, and A41:G41 cells are blank it still provides me with a value 1. When I want it to provide me with a value 0.

Does this...

=IF(COUNTBLANK(A1:G1)+COUNTBLANK(A41:G41),0,
    IF(AND(A1=A41,B1=C41,D1=D41,E1=E41,F1=F42,G1=G42),1,0))

satisfy your requirements?