Excel formula help

g3lo18

New Member
Joined
Nov 2, 2013
Messages
35
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
How about something this?


Excel 2010
ABCDEFGH
1Select 7 numbers:717350248533
2
3
4Numbers Drawn:717350248533
5
6
7Match?TRUE
Sheet97
Cell Formulas
RangeFormula
H7=EXACT(B1:H1,B4:H4)
 
Upvote 0
Or if you prefer 1s and 0s, you can use:


Excel 2010
ABCDEFGH
1Select 7 numbers:717350248533
2
3
4Numbers Drawn:717350248533
5
6
7Match?1
Lotto
Cell Formulas
RangeFormula
H7=EXACT(B1:H1,B4:H4)*1
 
Upvote 0
Also if the cells are blank, I want the formula to ignore it. (For instance if no numbers are inserted, the calculation is set automatically to 0)
 
Upvote 0
How about this?


Excel 2010
ABCDEFGH
1Select 7 numbers:717350248533
2
3
4Numbers Drawn:717350248533
5
6
7Match?1
Lotto
Cell Formulas
RangeFormula
H7=--(SUMPRODUCT((B1:H1)/(B4:H4))=7)
 
Upvote 0
Also if the cells are blank, I want the formula to ignore it. (For instance if no numbers are inserted, the calculation is set automatically to 0)

Does this do what you're looking for?


Excel 2010
ABCDEFGH
1Select 7 numbers:717350248533
2
3
4Numbers Drawn:717350248533
5
6
7Match?1
Lotto
Cell Formulas
RangeFormula
H7=IFERROR(--(SUMPRODUCT((B1:H1)/(B4:H4))=7),0)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top