Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

Please help? Anybody?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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!

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.