Can someone help my tired brain with 2 xcel formulas?

Factual Questions
1

I’ve written them out in narrative form as best I could.

Formula 1 (in cell C1)
If either A1 OR B1 are blank, then C1 is blank.
If A1 AND B1 exist, AND A1<120 AND B1<80, then C1=1.
Else C1 is blank.

Formula 2 (in cell D1)
If either A1 OR B1 are blank, then D1 is blank.
If A1 AND B1 exist, AND 120<A1<140 OR 79<B1<90, then D1=1.
Else D1 is blank

2

In cell C1 I put …


=IF(OR((A1=""),(B1="")),"",(IF(AND((A1<120),(B1<80)),1,"")))

And in cell D1 I put …


=IF(OR((A1=""),(B1="")),"",(IF(AND(AND((A1>120),(A1<140)),AND((B1>79),(B1<90))),1,"")))

Which is the basically word for word what you wrote (tho’ should the A1>120 be A1>119 or A1>=120 instead ?)

You could probably simplify it more, and you may need to watch if blank cells are auto-filled with zeros.

This is Excel 2000 on a Win98 box.

SD

3

Gah …

Cell D1:


=IF(OR((A1=""),(B1="")),"",(IF(OR(AND((A1>120),(A1<140)),AND((B1>79),(B1<90))),1,"")))

IF(OR(…),…) in the nested if, not IF(AND(…),…) I misread your post.

I tried looking for a simplification but I can’t really see one, that seems to work fine for me tho’.

Hope that helps.

SD

4

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",IF(AND(A1<120, B1<80),1,""))

Similarly:

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",IF(AND(A1>120, A1<140, B1>79,B1<90),1,""))

But in this case you can actually use:
=IF(AND(A1>120, A1<140, B1>79,B1<90),1,"")

You need the ISBLANK tests in the first formula because (e.g.) A1<120 is actually TRUE for a blank cell. However, in the second formula the blank cell will fail the larger-than tests, and so you do not explicitly need to test its blankness.

5

This is where I go for Dial-A-Genius! Thanks. This is for a spreadsheet I’m working on about blood pressure risk assessments. I showed this thread to my boss as a justiification for getting the SDMB cleared therough our corporate web filter. Probably nothing will change, but you never know unless you try.