# Check 2 Cells & Return Various Values Based on Results

• I'm trying to shorten a calcuation by using a single formula in one cell as oppose to my current method of multiple calculations over multiple cells.

Sample attached:
I use 3 hidden columns to display my Risk level then another formula to put them in one column.

The formula I'm trying to use generates a #VALUE! error and I can't figure out why.

New Formula:
=OR(IF(AND(A2=0,B2=0),"High Risk",""),IF(AND(A2=0,B2>=1),"Medium Risk",""),IF(AND(A2>=1,B2>=1),"Low Risk",""))

## Files

• Re: Array formulas

Change the formula in G2 to:

=IF(AND(A2=0,B2=0),"High Risk",IF(AND(A2=0,B2>=1),"Medium Risk",IF(AND(A2>=1,B2>=1),"Low Risk","")))

and fill down.

• Re: Array formulas

=IF(SUM(B2:C2)=0,"High Risk",IF(SUM(A2:B2)=1,"Medium Risk","Low Risk"))

or

=IF(A2+B2=0,"High Risk",CHOOSE(A2+B2,"Medium Risk","Low Risk","Low Risk","Low Risk"))

• Re: Array formulas

• Re: Array formulas

Dave,

There is a problem with your formula. For Medium Risk his/her condition is and(a2=0,b2=1).

If you change a2=1, b2=0 your formula returns Medium.

• Re: Array formulas

Yes I know. I made the assumption that it didn't matter which cell had the values, only the combo of both.

• Re: Array formulas

Quote from Dave Hawley

Yes I know. I made the assumption that it didn't matter which cell had the values, only the combo of both.

OK. If that's the case then another option..

=LOOKUP(SUM(A2:B2),{0,1,2},{"High Risk","Medium Risk","Low Risk"})

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!