Double Quote IF bug - Infinitely large number

[Ryan C]

I'm running a MAX(IF( on a range of cells where the IF range has to be over a certain number. The IF range is calculated with a lot of double quotes "" to represent blank cells.

For some reason when running an if test on a "" cell excel counts the "" cell as an infinitely large number.
For example:
=IF(CELL>9999999999999999999999999999999,"yes","no")
Returns a "yes" result.

Firstly: What, why is excel doing this?

Secondly:
My actual equation is: (ctrl+shift+enter) =MAX(IF(F4:F142631>3.9,D4:D142631))
Is there a solution better than converting all the numbers in the F range to negative and modifying the equation to: =MAX(IF(F4:F142631<3.9,D4:D142631))?
While this works the information in the F range is useful and would likely be confusing to people as to why they are negative values.

Thanks for your help

 

[Ryan C]

Correction
modified equation should have been =MAX(IF(F4:F142631<-3.9,D4:D142631))

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

There is no bug involved here.

Firstly: What, why is excel doing this?

21 10 18.xlsm
	A
1	cat
2	4
3	dog
4	2
Sample

If you were to manually sort the values above would you say cat, dog, 2, 4 or would you say 2, 4, cat, dog?
Without some sort of 'rule' about comparing text and numbers, either could be correct.
Excel has simply adopted the rule that (any) text is always greater than (any) numbers. Therefore in your formula "" (being a text value) is greater than 3.9

Is there a solution better than converting all the numbers in the F range to negative

Yes, one way is to try this (C+S+E unless using Microsoft 365)

=MAX(IF(F4:F142631<>"",IF(F4:F142631>3.9,D4:D142631)))