Please explain this excel formula

[kinkate18nic]

Someone please explain me this formula...what exactly it will do.

=IF(COUNTBLANK(Table1[@[L1-Uber]:[L9-Account Type]]) = 18,2, MIN(Table1[@[L1-Uber]:[L9-Account Type]]))


L1-Uber and L9-Account Type being column names.

Thanks

 

[steve the fish]

Hi. I presume there are 18 columns between L1-Uber and L9-Account Type inclusive? If all of those cells in the same row as the formula is housed are blank it will produce a 2. If not it will produce the minimum value in those cells.

 

[kinkate18nic]

Hi. I presume there are 18 columns between L1-Uber and L9-Account Type inclusive? If all of those cells in the same row as the formula is housed are blank it will produce a 2. If not it will produce the minimum value in those cells.

Thanks for the reply.

Actually, there are 35 columns between L1-Uber and L9-Account. If I make the complete row blank, the result comes as 0. If i change any cell value to 0 then the formula gives 0 which seems to be correct. But, it is the rest of the formula I am not understanding i.e 18,2, what does it accomplish?

 

[Jonmo1]

there are 35 columns between L1-Uber and L9-Account.
....
what does it accomplish?

Apparently nothing.

Best guess is that at the time the formula was written, there were only 18 columns between Uber and Account.
Therefore it 'was' testing if ALL columns are blank, then return 2, otherwise give the minimum value.

So maybe you need to update the formula by changing 18 to 35

 

[Jonmo1]

Or perhaps instead of needing to count the columns and hard code that number in the formula.
We can reverse the logic.

Instead of counting blanks, we count NON Blanks.
And if at least 1 cell is NOT blank, then do the MIN, otherwise return 2

=IF(COUNTIF(Table1[@[L1-Uber]:[L9-Account Type]],"<>")>0,MIN(Table1[@[L1-Uber]:[L9-Account Type]]),2)


And counting blanks or non-blanks has always been tricky, given the difference between truely empty cells, and cells containing a formula returning ""
Since the end result is returning the MIN function, which depends on numbers, it's more accurate to count numbers instead of blanks.

=IF(COUNT(Table1[@[L1-Uber]:[L9-Account Type]])>0,MIN(Table1[@[L1-Uber]:[L9-Account Type]]),2)

 

[kinkate18nic]

Thank you for the simplified version. I understood now how the formula is working.

 

[Jonmo1]

You're welcome.