+ Reply to Thread
Results 1 to 5 of 5

How to combine multiple functions possibly an if/or OR if/and statment

  1. #1
    Registered User
    Join Date
    04-09-2013
    Location
    Winnipeg, Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    How to combine multiple functions possibly an if/or OR if/and statment

    I am certain that there is an easy way to do this, in my real task I have approx 40 items in column A, so trying to find an efficient formula to return a value in column C

    Here is sample data
    A B C
    apples 100
    orange 200
    pear 300
    grape 400
    banana 500
    kiwi 600

    for column C value
    apples, orange, grape are all to divide column B by 10
    pear, to divide column B by 5
    banana and kiwi, to divide column b 15

    I know I can write this
    =IF(A2="apples",B2/10,IF(A2="orange",B2/10,""))

    But because in my real work I have multiple that have the same dividing factor I am looking for a way to shorten the formula.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,043

    Re: How to combine multiple functions possibly an if/or OR if/and statment

    You can set up a small table somewhere which lists your 40 unique items in one column and next to each you can list the divisor, like this:

    apples ... 10
    orange ...10
    grape .....10
    pear .......5
    banana ..15
    kiwi .......15
    and so on.

    Suppose you put this in columns M and N. Then you can use this formula in C2:

    =IFERROR(B2/VLOOKUP(A2,$M:$N,2,0),"")

    then copy down as required.

    Hope this helps.

    Pete

  3. #3
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    Re: How to combine multiple functions possibly an if/or OR if/and statment

    I would use a vlookup and a reference table.

    Each fruit has it's own value to be divided by, so you can just look that up. This is essentially how I handle most IF statements if I can, because it is SO simple to manage and understand.
    See the attached for a simple example.


    EDIT: See, Pete thinks the same above
    Attached Files Attached Files
    Last edited by mikeTRON; 05-26-2016 at 05:50 PM.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,043

    Re: How to combine multiple functions possibly an if/or OR if/and statment

    Quote Originally Posted by mikeTRON View Post
    ... EDIT: See, Pete thinks the same above ...
    I think that's 3 times in the last 24 hours, Mike - people will start talking !! <bg>

    Pete

  5. #5
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    Re: How to combine multiple functions possibly an if/or OR if/and statment

    Quote Originally Posted by Pete_UK View Post
    I think that's 3 times in the last 24 hours, Mike - people will start talking !! <bg>

    Pete
    I'm just riding your coattail... and/or I need to respond to these simple requests faster haha.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to combine multiple IF functions with a VLOOKUP?
    By clogistics in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-13-2016, 01:01 AM
  2. Nesting Vlookup Functions with IF Functions and possibly more
    By Anitarizzo in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-27-2014, 12:50 AM
  3. [SOLVED] code not working, possibly combine for better results?
    By Russ Fuquay in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-24-2013, 10:42 AM
  4. How to combine data from 2 rows, possibly with IF statement involved.
    By sark1014 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-08-2012, 04:31 PM
  5. How to combine multiple substitute functions?
    By ron_b_michigan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-03-2012, 11:15 AM
  6. Using text functions??? possibly anyway...
    By mburgess08 in forum Excel General
    Replies: 2
    Last Post: 02-19-2009, 07:41 PM
  7. If functions and possibly or
    By facmess1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-03-2006, 02:13 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1