Hi everyone,
I would like to remove a lot of 'False' in my spilled data. Is there any way to do this? So that only my values will be left?
Criterium C = =N(OFFSET(INDIRECT("'"&TRANSPOSE(Sheets)&"'!C1:C30000");ROW(Summary!$1:$30000)-1)
Criterium = =N(OFFSET(INDIRECT("'"&TRANSPOSE(Sheets)&"'!A1:A30000");ROW(Summary!$1:$30000)-1)
ResultE = =N(OFFSET(INDIRECT("'"&TRANSPOSE(Sheets)&"'!E1:E30000");ROW(Summary!$1:$30000)-1)
Thanks in advance!!!
I would like to remove a lot of 'False' in my spilled data. Is there any way to do this? So that only my values will be left?
Criterium C = =N(OFFSET(INDIRECT("'"&TRANSPOSE(Sheets)&"'!C1:C30000");ROW(Summary!$1:$30000)-1)
Criterium = =N(OFFSET(INDIRECT("'"&TRANSPOSE(Sheets)&"'!A1:A30000");ROW(Summary!$1:$30000)-1)
ResultE = =N(OFFSET(INDIRECT("'"&TRANSPOSE(Sheets)&"'!E1:E30000");ROW(Summary!$1:$30000)-1)
Thanks in advance!!!
Test.xlsx | |||||
---|---|---|---|---|---|
N | O | P | |||
25 | FALSE | FALSE | |||
26 | 5.00 | FALSE | |||
27 | 0.00 | FALSE | |||
28 | 0.00 | FALSE | |||
29 | -0.01 | FALSE | |||
30 | -0.02 | FALSE | |||
31 | -0.03 | FALSE | |||
32 | -0.04 | FALSE | |||
33 | -0.05 | FALSE | |||
34 | -0.05 | FALSE | |||
35 | -0.10 | FALSE | |||
36 | -0.15 | FALSE | |||
37 | -0.19 | FALSE | |||
38 | -0.24 | FALSE | |||
39 | -0.25 | FALSE | |||
40 | -0.27 | FALSE | |||
41 | -0.29 | FALSE | |||
42 | -0.30 | FALSE | |||
43 | -0.31 | FALSE | |||
44 | -0.32 | FALSE | |||
45 | -0.32 | FALSE | |||
46 | FALSE | FALSE | |||
47 | FALSE | FALSE | |||
48 | FALSE | FALSE | |||
49 | FALSE | FALSE | |||
50 | FALSE | FALSE | |||
51 | FALSE | FALSE | |||
52 | FALSE | FALSE | |||
53 | FALSE | FALSE | |||
54 | FALSE | FALSE | |||
55 | FALSE | FALSE | |||
Summary |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N25:O30024 | N25 | =IF(CriteriumC=B65,(IF(Criterium>=$B$48,IF(Criterium<=$B$49,ResultE)))) |
Dynamic array formulas. |