Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How does this formula work ?
Why does the following formula work the way it does ?
=MIN(IF({0,1,2,3}0,{0,1,2,3})) Using the Evaluate Formula dialog box, you see the IF test resolve to {FALSE,TRUE,TRUE,TRUE}. But the IF TRUE result returns {FALSE,1,2,3}. How does this formula replace the 0 with FALSE and leave the numbers unaffected ? - Ronald K. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How does this formula work ?
On Jun 10, 11:26*pm, kittronald wrote:
Why does the following formula work the way it does ? * * * =MIN(IF({0,1,2,3}0,{0,1,2,3})) [....] How does this formula replace the 0 with FALSE and leave the numbers unaffected ? Every IF expression has two parts: value if true, and a value if false. If the value-if-false is missing, it is implicitly FALSE. So your MIN expression is effectively: =MIN(IF({0,1,2,3}0,{0,1,2,3},{FALSE,FALSE,FALSE,F ALSE})) Now, the operation of that expression is as if you wrote (but this is not legal syntax): =MIN({IF(00,0,FALSE),IF(10,1,FALSE},IF(20,2,FAL SE), IF((30,3,FALSE)}) Since 00 is false, that conceptual IF expression is replaced with the value-if-false part, which is FALSE. Since 10, 20 and 30 are all true, those conceptual IF expressions are replaced with the value-if- true part, which 1, 2 and 3 respectively. Thus, you effectively get the formula =MIN({FALSE,1,2,3}). Now, looking at the MIN help page, you will see that MIN ignores logic values. So the effect of the original MIN expression is return the MIN of the values greater than zero, namely the MIN of 1, 2 and 3. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How does this formula work ?
Joe,
Thanks for the detailed explanation - now I understand a bit more. However, I've been trying for days, with no avail, to get this concept to work with the formula below: Search for my post in this group for "Getting smallest number 0 in an array constant". - OR - http://groups.google.com/group/micro...8835d828ae629a -Ronald K. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to work in a 3-d reference with 2 work books | Excel Worksheet Functions | |||
Sorting the cells of a formula causes the formula to not work | Excel Worksheet Functions | |||
why doesn't this formula work? | Excel Worksheet Functions | |||
A search for $ in a formula use to work now it does not work | Excel Discussion (Misc queries) | |||
formula won't work | Excel Worksheet Functions |