Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Check 2 Cells & Return Various Values Based on Results

1. Member
Join Date
13th July 2004
Posts
70

## 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",""))

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## 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.

Excel Video Tutorials / Excel Dashboards Reports

3. ## 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"))

4. ## Re: Array formulas

5. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Array formulas

Originally Posted by Dave Hawley

=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"))
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.

6. ## 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.

7. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Array formulas

Originally Posted by 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"})

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno