Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return all matching values | Excel Worksheet Functions | |||
Looking up multiple values for a single name | Excel Worksheet Functions | |||
find largest values, then return corresponding row values. | Excel Discussion (Misc queries) | |||
return multiple corresponding values in excel | Excel Worksheet Functions | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions |