Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 7 of 7

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

  1. #1
    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",""))
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    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. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: Array formulas

    How about

    =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. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: Array formulas

    slick225, please use Thread Title to state what you think is your answer. Only use it to describe your problem.

  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Array formulas

    Quote Originally Posted by Dave Hawley
    How about

    =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. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    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. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Array formulas

    Quote 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"})

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Check Cell & Return 1 of 2 Values
    By ekalaycioglu in forum Excel General
    Replies: 7
    Last Post: August 6th, 2008, 13:18
  2. Check Values of Cells & Return Value Accordingly
    By hortiz in forum Excel General
    Replies: 9
    Last Post: September 13th, 2007, 03:24
  3. Replies: 8
    Last Post: July 24th, 2007, 05:45
  4. Return Multiple Results Based on Criteria
    By Eric Lurie in forum Excel General
    Replies: 11
    Last Post: December 9th, 2006, 06:20
  5. Return Results Based on Referenced Cell
    By mfubib in forum Excel General
    Replies: 3
    Last Post: November 22nd, 2006, 17:25

Bookmarks

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