Search code examples
excelcellformula

Excel easy : in what way is my simple formula wrong?

enter image description here

So my understanding of my formula is:

  1. take cells B42,C42, D42 and E42.
  2. in that query, limit search to non blank cells.
  3. find the smallest value out of those cells

As far as I know excel it simply should be "4", not "1". So there must be something wrong with that NOT(ISBLANK(ref)) formula, and I would like to know what.

Basically my whole assignment was, to extend the search from B42 to N42. But it my formula is wrong because I'm searching the smallest number in these cells but formula is calculating something different.

Please help :)

Solution

  • How your formula works (should be evaluted with array entry - CTRL+SHIFT+ENTER):

    =MIN(NOT(ISBLANK(B42:E42)))
    • ISBLANK(B42:E42) returns array {FALSE,FALSE,FALSE,FALSE}
    • NOT({FALSE,FALSE,FALSE,FALSE}) returns array {TRUE,TRUE,TRUE,TRUE}
    • MIN({TRUE,TRUE,TRUE,TRUE}) returns 1 becauseTRUEin excel equals to1`

    if you're using your formula without array entry, you get:

    • ISBLANK(B42:E42) evaluates to ISBLANK(#VALUE!) because it should accept single cell instead range.
    • ISBLANK(#VALUE!) returns FALSE
    • NOT(FALSE) returns TRUE
    • MIN(TRUE) returns 1 because TRUE in excel equals to 1

    You should use 

    =MIN(IF(NOT(ISBLANK(B42:E42)),B42:E42))

    with array entry (CTRL+SHIFT+ENTER)

    or simply:

    =MIN(B42:E42)

    UPD:

    if all cells in target range are blank, above formulas still returns 0, in that case you could use:

    =IF(COUNT(B42:E42),MIN(B42:E42),"")