DonkeyOte
MrExcel MVP
- Joined
- Sep 6, 2002
- Messages
- 9,124
In keeping with the Predict the Output series I came across something I'd never noticed before which I found interesting and given it's a non-VBA test figured that perhaps more people would indeed be able to "predict the output" than usual whilst perhaps still finding the challenge (vaguely) interesting...?
I was surprised by some of the outputs but then I'm quite thick.
So... based on the screen shot and formulae in each cell can you predict what the various results will be...
Formulae in rows 5 through 7 are Arrays and would require confirm with CTRL + SHIFT + ENTER
(note: I'm not saying we'd use these formulae )
I was surprised by some of the outputs but then I'm quite thick.
So... based on the screen shot and formulae in each cell can you predict what the various results will be...
Formulae in rows 5 through 7 are Arrays and would require confirm with CTRL + SHIFT + ENTER
(note: I'm not saying we'd use these formulae )
Excel Workbook | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Values | 0.1 | 1.01 | ||
2 | |||||
3 | Last Column | B | C | ||
4 | Function 1 | =MIN(1.2,INDIRECT("B1:"&B$3&"1")) | =MIN(1.2,INDIRECT("B1:"&C$3&"1")) | ||
5 | Function 2 | =MIN(1.2,IF(INDIRECT("B1:"&B$3&"1")>0,INDIRECT("B1:"&B$3&"1"))) | =MIN(1.2,IF(INDIRECT("B1:"&C$3&"1")>0,INDIRECT("B1:"&C$3&"1"))) | ||
6 | Function 3 | =MIN(1.2,IF(INDIRECT("B1:"&B$3&"1")>0.5,INDIRECT("B1:"&B$3&"1"))) | =MIN(1.2,IF(INDIRECT("B1:"&C$3&"1")>0.5,INDIRECT("B1:"&C$3&"1"))) | ||
7 | Function 4 | =MIN(IF(INDIRECT("B1:"&B$3&"1")>0.5,INDIRECT("B1:"&B$3&"1"),1.2)) | =MIN(IF(INDIRECT("B1:"&C$3&"1")>0.5,INDIRECT("B1:"&C$3&"1"),1.2)) | ||
8 | |||||
9 | Function 5a | =MIN(1.2,B1>0,C1>0) | |||
10 | Function 5b | =MIN(1.2,B1>0.5,C1>0.5) | |||
Sheet1 |