I have the table below, looking to create a spill formula which will simply tell me if a numeric value is in any of the three columns B,C,D.
However with the spill formula currently in use as soon as one row is true then all become true. Is this a limitation to spill formulas or is there a solution to this?
However with the spill formula currently in use as soon as one row is true then all become true. Is this a limitation to spill formulas or is there a solution to this?
Book1 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | NAME | DATE | HOURS | CYCLES | TRUE/FALSE | What I expect to see | ||
2 | abc | 10 | TRUE | TRUE | ||||
3 | def | 10 | TRUE | TRUE | ||||
4 | ||||||||
5 | ghi | TRUE | FALSE | |||||
6 | jkl | 28/09/2023 | TRUE | TRUE | ||||
7 | mno | TRUE | FALSE | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E10 | E2 | =IF(A2:A10>"",IF(OR(ISNUMBER(B2:B10),ISNUMBER(C2:C10),ISNUMBER(D2:D10)),TRUE,FALSE),"") |
Dynamic array formulas. |