IF...(Excel 2007)

[Guest]

I have 6 columns N1, N2 thru N6

How do I, in a single field, say IF (N1, N2, N3, N4, N5, N6 = 1 then 1 else
"")

Basically, if any fields = 1, then display that value.

At any time, only 1 of the fields would be 1, if any.

Thanks in advance.

 

[Gary''s Student]

=IF((N1=1)+(N2=1)+(N3=1)+(N4=1)+(N5=1)+(N6=1)>0,1,"")

 

[Niek Otten]

=IF(COUNTIF(N1:N6,1)=0,"",1)

 

[Guest]

Perfect, thanks.

Was determining frequency of FL lotto wining numbers for past 10 years to
current.
Lowest at 97 times was number 53, highest was number 28 at 214 and number 47
at 205.

Complete report at http://eparisek.com/lotto.htm

 

[Dana DeLouis]

Lowest at 97 times was number 53, highest was number 28 at 214 and

number 47 at 205.

Just guessing, but it sounds like another option might be the following.
If we assume your data is in A1:F1000 (named "Data") then...

=COUNTIF(Data,1)
=COUNTIF(Data,2)
....etc

Or in say H1...
=COUNTIF(Data,Row())
and copy down.
= = =
HTH
Dana DeLouis

 

[Shane Devenshire]

Hi,

looks like from the data that

=COUNTIF($B2:$G2,I$1)

copy over the whole range and then choose Tools, Options, View, and uncheck
Zero values.

 

[Guest]

Problem with this is...

The individual drawn numbers are in their own field as N1, N2, N3, N4, N5
and N6

If I use COUNTIF, it will count 1 from 1, 10, 11...21, 31, 41. giving in
accurate counts.

Note: I need it to count ONLY 1, the actual number 1 (one). Not 1 from 14,
or 21, but just 1. For example, the example below, the only row that should
return a result is 7/16/1988, since 1 was drawn as N4. Complete stats as
well as frequency of numbers drawn in the past 10 years can be found here:
http://eparisek.com/lotto.htm


However, I appreciate the comment, the resolution from worked and was
flagged several hours before your post.

Spreadsheet Layout:

Draw Date N1 N2 N3 N4 N5 N6
5/7/1988 30 44 17 49 42 15
5/14/1988 18 15 23 19 11 22
5/21/1988 15 40 8 47 25 22
5/28/1988 36 12 19 13 20 48
6/4/1988 3 19 7 5 13 20
6/11/1988 48 15 44 20 13 17
6/18/1988 4 38 42 23 3 10
6/25/1988 27 17 13 42 47 40
7/2/1988 11 6 31 2 34 14
7/9/1988 13 28 4 46 3 35
7/16/1988 2 48 3 1 13 28
7/23/1988 37 36 26 42 10 23

 

[Dana DeLouis]

If I use COUNTIF, it will count 1 from 1, 10, 11...21, 31, 41. giving

in accurate counts.

Hi. As a side note, if in A1:A4 I have 1,11,21,31
=COUNTIF(A1:A4,1)

returns a count of 1 for me. It does not count '1 in each of the others.

Another option...
On a clean sheet, if I go to Data, Web data..
And give your address...

http://www.flalottery.com/exptkt/l6.htm

I get the sheet populated with the data as you said.
and...

=COUNTIF(B:G,53)
returns 97.

This also checks with the data at the bottom of the sheet.

= = =
HTH
Dana DeLouis

 

[Kemal Beydilli]

You can use or formule with if.

=IF(OR(N1=1;N2=1;N3=1;N4=1;N5=1;N6=1);1;"")

I have tried, it has worked.

 

[Ron Rosenfeld]

I have 6 columns N1, N2 thru N6

How do I, in a single field, say IF (N1, N2, N3, N4, N5, N6 = 1 then 1 else
"")

Basically, if any fields = 1, then display that value.

At any time, only 1 of the fields would be 1, if any.

Thanks in advance.

The following **array-entered** formula will do that.

To **array-enter** a formula, hold down <ctrl><shift> while hitting <enter> If
you do it correctly, Excel will place braces {...} around the formula:

=--OR(N1:N6=1)

--ron