excelNewbie22
Well-known Member
- Joined
- Aug 4, 2021
- Messages
- 510
- Office Version
- 365
- Platform
- Windows
hi.
is it possible for a formula to write down the actual prime numbers in certain range?
and same thing for repeating numbers out of previous rows?
and in third column to compare the last 2 and write down the same one's?
this is what u help me out with:
and this is what i want it to be: changed row 3 for example
is it possible for a formula to write down the actual prime numbers in certain range?
and same thing for repeating numbers out of previous rows?
and in third column to compare the last 2 and write down the same one's?
this is what u help me out with:
22222222222.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
C | D | E | F | G | H | I | J | K | |||
1 | 1 | 2 | 3 | 4 | 5 | 6 | prime | from last 2 | compare equal #'s i+j | ||
2 | 16 | 24 | 28 | 34 | 35 | 36 | 0 | 3 | |||
3 | 15 | 18 | 23 | 25 | 29 | 37 | 3 | 2 | |||
4 | 10 | 16 | 19 | 23 | 24 | 28 | 2 | 1 | |||
5 | 4 | 12 | 20 | 30 | 35 | 37 | 1 | 0 | |||
6 | 2 | 11 | 13 | 27 | 28 | 31 | 4 | 3 | |||
7 | 11 | 13 | 14 | 17 | 21 | 23 | 4 | 1 | |||
גיליון1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I2:I7 | I2 | =SUMPRODUCT(--(MMULT(TRANSPOSE(ROW(INDIRECT("2:"&MAX(C2:H2))))^0,--(MOD(C2:H2,ROW(INDIRECT("2:"&MAX(C2:H2))))=0))=1)) |
J6:J7,J2:J4 | J2 | =SUM(--(COUNTIF(C3:H4,C2:H2)>0)) |
J5 | J5 | =SUM(--(COUNTIF(C6:H7,C5:H5)>0)) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
J:J | Cell Value | contains "0" | text | NO |
and this is what i want it to be: changed row 3 for example
22222222222.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
C | D | E | F | G | H | I | J | K | |||
1 | 1 | 2 | 3 | 4 | 5 | 6 | prime | from last 2 | compare equal #'s i+j | ||
2 | 16 | 24 | 28 | 34 | 35 | 36 | 0 | 3 | |||
3 | 15 | 18 | 23 | 25 | 29 | 37 | 23,29,37 | 23, 37 | 23, 37 | ||
4 | 10 | 16 | 19 | 23 | 24 | 28 | 2 | 1 | |||
5 | 4 | 12 | 20 | 30 | 35 | 37 | 1 | 0 | |||
6 | 2 | 11 | 13 | 27 | 28 | 31 | 4 | 3 | |||
7 | 11 | 13 | 14 | 17 | 21 | 23 | 4 | 1 | |||
גיליון1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I2,I4:I7 | I2 | =SUMPRODUCT(--(MMULT(TRANSPOSE(ROW(INDIRECT("2:"&MAX(C2:H2))))^0,--(MOD(C2:H2,ROW(INDIRECT("2:"&MAX(C2:H2))))=0))=1)) |
J2,J6:J7,J4 | J2 | =SUM(--(COUNTIF(C3:H4,C2:H2)>0)) |
J5 | J5 | =SUM(--(COUNTIF(C6:H7,C5:H5)>0)) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
J:J | Cell Value | contains "0" | text | NO |