No hard feelings. You did OK. Thanks anyways love.if this still didn't work, I'm done
No hard feelings. You did OK. Thanks anyways love.if this still didn't work, I'm done
try post#12 belowThe formula is giving me a FALSE. I cannot make the formula TRUE no matter what I type in the range or in cell A2.
Please I would like you to make an effort for a formula that can adapt to various Range sizes. Currently the range is not big. For instance A3:A10, in other sheets the range is A3:A40 and many other sheets where range is even larger. All need to have your formula in them. I see an 8 at the end and I assume I will have to manually change it for other ranges? Correct?
same result. unable to get a TRUE no matter what I type in. Tried both Ctrl+Shift+Enter eg. {} and without the curly brackets.try post#12 below
yes 8 can be dynamic as count(A3:A8)
Book1 | |||
---|---|---|---|
A | |||
1 | TRUE | ||
2 | B | ||
3 | 1 | ||
4 | 1 | ||
5 | 1 | ||
6 | -1 | ||
7 | -1 | ||
8 | -1 | ||
9 | 1 | ||
10 | -1 | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | A1 | =OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8)) |
Book1 | |||
---|---|---|---|
A | |||
1 | FALSE | ||
2 | A | ||
3 | 1 | ||
4 | 1 | ||
5 | 1.1 | ||
6 | -1 | ||
7 | -1 | ||
8 | -1 | ||
9 | 1 | ||
10 | -1 | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | A1 | =OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8)) |
Book1 | |||
---|---|---|---|
A | |||
1 | TRUE | ||
2 | A | ||
3 | 1 | ||
4 | 1 | ||
5 | 5 | ||
6 | -1 | ||
7 | -1 | ||
8 | -1 | ||
9 | 1 | ||
10 | -1 | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | A1 | =OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8)) |
Book1 | |||
---|---|---|---|
A | |||
1 | FALSE | ||
2 | |||
3 | 1 | ||
4 | 1 | ||
5 | 5 | ||
6 | -1 | ||
7 | -1 | ||
8 | -1 | ||
9 | 1 | ||
10 | -1 | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | A1 | =OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8)) |
have a look of this test
try to copy below to see if you can produce same results
Book1
A 1 TRUE 2 B 3 1 4 1 5 1 6 -1 7 -1 8 -1 9 1 10 -1 Sheet2
Cell Formulas Range Formula A1 A1 =OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8))
Book1
A 1 FALSE 2 A 3 1 4 1 5 1.1 6 -1 7 -1 8 -1 9 1 10 -1 Sheet2
Cell Formulas Range Formula A1 A1 =OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8))
Book1
A 1 TRUE 2 A 3 1 4 1 5 5 6 -1 7 -1 8 -1 9 1 10 -1 Sheet2
Cell Formulas Range Formula A1 A1 =OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8))
Book1
A 1 FALSE 2 3 1 4 1 5 5 6 -1 7 -1 8 -1 9 1 10 -1 Sheet2
Cell Formulas Range Formula A1 A1 =OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=8),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=8))
Yes, very very close. Only two requirements remain now. Please don't give up now.. hehethis may work
Book1
A 1 TRUE 2 B 3 4 5 6 7 8 -1 9 1 10 -1 Sheet1
Cell Formulas Range Formula A1 A1 =OR(AND(A2="A",SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10>0))+SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<0))=COUNT(A3:A10)),AND(NOT(ISBLANK(A2)),SUMPRODUCT(--((A3:A10)=INT(A3:A10))*(A3:A10<>0))=COUNT(A3:A10)))
For instance when A2 = BIF A2 = A and range A3:A10 consists of negative integer/s then A1 = TRUE
IF A2 = A and range A3:A10 consists of positive integer/s then A1 = TRUE
IF A2 = A and range A3:A10 consists of both positive and negative integer/s then A1 = TRUE
IF A2 = not blank and range A3:A10 consists of negative integer/s then A1 = FALSE
IF A2 = not blank and range A3:A10 consists of positive integer/s then A1 = TRUE
IF A2 = not blank and range A3:A10 consists of both positive and negative integer/s then A1 = FALSE
IF A2 = A or not blank and range A3:A10 consists of exact zero "0" value then A1 = FALSE
IF A2 = blank then A1 = FALSE
I’m away from my laptop now, will have another go when I’m backYes, very very close. Only two requirements remain now. Please don't give up now.. hehe
For instance when A2 = B