Edit: Question is moot. There was another error in my formula and not the one I suspected and described here. I still don't want to delete the question, because jrc03c took the time to answer it.
I have a big Excel formula. It checks if the minimum value in a range of cells, say B2:N2, is higher than a certain threshold, say 5. The same formula also needs to check if the minimal value in the same range is below another threshold, say -1.
The data goes on for multiple rows, and there is a set of data on each sheet. On some sheets, some data are missing. But if Cell D2 is empty, then MIN(B2:N2)<-1 returns FALSE even if all other cells in the range are below -1, because the empty cell evaluates as having a value of 0 (don't you love VBA?). I cannot just fill the cells with an arbitrary value below -1, because that would break the MIN(B2:N2)>5 check.
I know that the ISEMPTY function exists, but I don't know how to combine it with the range selection. I thought of something like
up to N2, but the actual formula is undebuggable enough without adding this monstrosity four times, each time having to think about whether I need a substitute magical number below or above the threshold. Plus, I have a bad feeling that some day when I've finally forgotten about this nightmare, the thresholds will change and I'll be tasked with updating the Excel sheet.
Please help? Anybody?