if multiple values same/others different, return true
 

A challenge:
I have a list that has 48 columns in each row. Each cell has a score of
1-4. I would like a "true" return if specifically and only these cells have
a 1 and others do not have a 1.
For example:
A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
Basically I have 40 cells that need to be a 1 and 8 cells that at least one
has a different number.

I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.

Thank you

if multiple values same/others different, return true
 

=IF(SUMIF([YOUR RANGE],1)=40,"TRUE","FALSE")

This returns the sum of all 1s in the range, and if it equals 40, then TRUE
is returned, else FALSE is returned.

Dave
--
Brevity is the soul of wit.


"Jshendel" wrote:

A challenge:
I have a list that has 48 columns in each row. Each cell has a score of
1-4. I would like a "true" return if specifically and only these cells have
a 1 and others do not have a 1.
For example:
A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
Basically I have 40 cells that need to be a 1 and 8 cells that at least one
has a different number.

I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.

Thank you

if multiple values same/others different, return true
 

Hi Jshendel,

did you try to use

=if(sum(a1:an1)=40,"true","false")

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Jshendel" escreveu:

A challenge:
I have a list that has 48 columns in each row. Each cell has a score of
1-4. I would like a "true" return if specifically and only these cells have
a 1 and others do not have a 1.
For example:
A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
Basically I have 40 cells that need to be a 1 and 8 cells that at least one
has a different number.

I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.

Thank you

if multiple values same/others different, return true
 

I thought of this, too.
But, if cell A1 is a 2 and D1 is a 1, your equation will still return true,
when it is false.
Thanks anyway,
Josh

"Dave F" wrote:

=IF(SUMIF([YOUR RANGE],1)=40,"TRUE","FALSE")

This returns the sum of all 1s in the range, and if it equals 40, then TRUE
is returned, else FALSE is returned.

Dave
--
Brevity is the soul of wit.


"Jshendel" wrote:

A challenge:
I have a list that has 48 columns in each row. Each cell has a score of
1-4. I would like a "true" return if specifically and only these cells have
a 1 and others do not have a 1.
For example:
A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
Basically I have 40 cells that need to be a 1 and 8 cells that at least one
has a different number.

I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.

Thank you

if multiple values same/others different, return true
 

The equation I give only sums cells whose value is 1. It ignores 2.
--
Brevity is the soul of wit.


"Jshendel" wrote:

I thought of this, too.
But, if cell A1 is a 2 and D1 is a 1, your equation will still return true,
when it is false.
Thanks anyway,
Josh

"Dave F" wrote:

=IF(SUMIF([YOUR RANGE],1)=40,"TRUE","FALSE")

This returns the sum of all 1s in the range, and if it equals 40, then TRUE
is returned, else FALSE is returned.

Dave
--
Brevity is the soul of wit.


"Jshendel" wrote:

A challenge:
I have a list that has 48 columns in each row. Each cell has a score of
1-4. I would like a "true" return if specifically and only these cells have
a 1 and others do not have a 1.
For example:
A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
Basically I have 40 cells that need to be a 1 and 8 cells that at least one
has a different number.

I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.

Thank you

if multiple values same/others different, return true
 

Something along these lines:

SUM your 40 cells

Test your 8 cells in the OR function

=IF(AND(SUM(A2:C2,E2,G2:H2 ...etc)=6,OR(D21,F21,J21.etc)),"TRUE","FALSE")

HTH

"Jshendel" wrote:

A challenge:
I have a list that has 48 columns in each row. Each cell has a score of
1-4. I would like a "true" return if specifically and only these cells have
a 1 and others do not have a 1.
For example:
A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
Basically I have 40 cells that need to be a 1 and 8 cells that at least one
has a different number.

I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.

Thank you

if multiple values same/others different, return true
 

It works.
However, in my own boredom, I made my own equation. Long and ugly, but it
works, too.
=IF(AND(BE6=BF6=BG6=BH6=BI6=BJ6=BK6=BL6=BM6=BN6=BO 6=BP6=BQ6=BR6=BS6=BT6=BU6=BV6=BW6=BX6=BY6=BZ6=CA6= CB6=CC6=CD6=CO6=CP6=CQ6=CR6=CS6=CT6=CU6=CV6=CW6=CX 6=CY6=CZ6,(OR(BE6<CF6,BE6<CG6,BE6<CH6,BE6<CI6, BE6<CJ6,BE6<CK6,BE6<CK6,BE6<CL6,BE6<CM6,BE6< CN6))),TRUE,FALSE)

Thanks everyone,
Josh


"Toppers" wrote:

Something along these lines:

SUM your 40 cells

Test your 8 cells in the OR function

=IF(AND(SUM(A2:C2,E2,G2:H2 ...etc)=6,OR(D21,F21,J21.etc)),"TRUE","FALSE")

HTH

"Jshendel" wrote:

A challenge:
I have a list that has 48 columns in each row. Each cell has a score of
1-4. I would like a "true" return if specifically and only these cells have
a 1 and others do not have a 1.
For example:
A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
Basically I have 40 cells that need to be a 1 and 8 cells that at least one
has a different number.

I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.

Thank you

if multiple values same/others different, return true
 

This will not work because a true formula can equal anything from 49 (47 "1"
and 1 "2" up to 72 (40 "1" and 8 "4")
and if a number <1 is in the cell that should be a 1, it will return true
when it is false.
Thanks for you advice,
Josh

"Marcelo" wrote:

Hi Jshendel,

did you try to use

=if(sum(a1:an1)=40,"true","false")

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Jshendel" escreveu:

A challenge:
I have a list that has 48 columns in each row. Each cell has a score of
1-4. I would like a "true" return if specifically and only these cells have
a 1 and others do not have a 1.
For example:
A1, b1, c1, e1, g1, h1, i1, (etc.) have to be a 1 for the line to be true
D1, f1, j1, k1, (etc.) if any or all of these are not a 1, the line is true
Basically I have 40 cells that need to be a 1 and 8 cells that at least one
has a different number.

I could write if a1=b1=c1=e1=g1 etc., but that is a very long equation.

Thank you