Excel: how to calculate a maximum without taking empty cells into account

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

MAX(IF(NOT(ISEMPTY(B2));B2;-2);IF(NOT(ISEMPTY(C2));C2;-2)...

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.

-

Try something like this:

``````=IF(OR((MIN(B2:N2)>5),(MIN(B2:N2)<-1)), MIN(B2:N2), "MIN VALUE IS BETWEEN -1 AND 5")
``````

Essentially, the syntax looks like this:

``````=IF(OR((CONDITION 1),(CONDITION 2)), RESULT_IF_TRUE, RESULT_IF_FALSE)
``````

If this doesn't solve your problem, let me know and we'll go back to the drawing board!

-
Oh, and by the way, I don't think blank cells cause a problem...at least in Outlook 2007, the above formula worked just fine. – jrc03c Jul 19 '10 at 20:42
I think you misunderstood me, your answer wouldn't have done what I wanted. But after enough debugging, I found out that there is no need to do what I wanted, because Excel isn't assuming a zero value for an empty cell the way I thought at first. So I declare your answer for "good enough" and the question for closed. – rumtscho Jul 20 '10 at 15:58
@rumtscho - it is good use to upvote an answer which helped you, even if not solving. If it doesn't solve directly your problem, but you found the correct solution, I would recommend you to post it yourself, and accept it (you will be able to after a day or two), so that your question gets a correct accepted answer, in case someone else comes with the same issue. – Gnoupi Jul 20 '10 at 19:23
@Gnoupi 1. Nobody can come with the same issue, because there wasn't an issue. I blamed an incorrect result on the assumed Excel default behaviour, and asked for a workaround of this behaviour. Later, I found out that the error was in my own formula, and Excel's behaviour is different than I thought, so no need for an workaround there. 2. I appreciate jrc03c's effort. However, had there been an issue, his proposition wouldn't have helped solve it. This is why I didn't upvote. Still, he took the time to answer. So instead of deleting my dumb question, I accepted his answer, giving him rep. – rumtscho Jul 21 '10 at 6:33
@rum - ok then, no pressure ;) – Gnoupi Jul 21 '10 at 6:54