Excel extended OR formula

Posted on 2011-10-03
Last Modified: 2012-05-12
New to Excel formulas, and I can't seem to find a definitive answer over Google so here I am.

I am using:

=IF(OR(Table_Conversion_data[[#This Row],[Interface %]]) >=25%, 4,0)

the cell value is in another worksheet if you are wondering why the complicated value there and is linked. This formula works fine.

However I want to extend it to be something like (i've simplified for the example):
 (OR(A1) >=25%, 4(OR(A1) >=50%, 6)  f
or example...but I can see it doesn't quite work like this. So basically if the value is >=25%, put the value 4 in, or if >=50% put the value 6 in.

Question by:Alex_Vaughan87
    LVL 19

    Expert Comment

    that would be

    = IF (a1 > 25%, 4, if(a1 > 50%, 6, ""))

    Open in new window

    LVL 92

    Expert Comment

    by:Patrick Matthews
    I'd be tempted to use VLOOKUP for this:

    =VLOOKUP(Table_Conversion_data[[#This Row],[Interface %]],MyLookupRange,2)

    That assumes you have a defined range, MyLookupRange, with values such as:

    0                      0
    0.25                  4
    0.5                  6

    For more about VLOOKUP:
    LVL 29

    Accepted Solution

    Well cannot really test it as do not hv the workbooks but is it this your looking for ?
    =IF(OR(Table_Conversion_data[[#This Row],[Interface %]]) >=25%, 4,IF(OR(Table_Conversion_data[[#This Row],[Interface %]]) >=50%, 6,0))

    Open in new window

    LVL 19

    Expert Comment

    look at it from this perspective :

    if (condition, [what to see if condition is met], [what to see if condition is not met])

    the if statement can be nested :

    if (condition 1, [what to see if condition 1 is met], if(condition 2, [what to see if condition 1 is not met and condition 2 is met], [what to see if condition 1 is not met and condition 2 is not met]))


    if (condition 1, if(condition 2, [condition 1 is met and condition 2 is met], [condition 1 is met and condition 2 is not met]), [condition 1 is not met])
    LVL 17

    Expert Comment

    If the intervals you want to reflact in the %ages are not evenly spaced, I'd agree with matthewspatrick and do VLOOKUP.  If they're evenly spaced, say up to 25%, then up to 50% then up to 75% then up to 100%, you could use CHOOSE, which would be quite readable, e.g.

    =CHOOSE(INT(Table_Conversion_data[[#This Row],[Interface %]]*4)+1,4,6,8,9)

    Open in new window

    If you wanted to go up by intervals of 10% you could just multiply by 10 - you could repeat some values in the 'value to choose' part of the function if say you wanted the same value for 50%-60% and 60%-70%

    Author Closing Comment

    For now I will take this as it works, but for the future I will look into the VLOOKUP option.

    Cheers all, just needed to know the syntax.
    LVL 29

    Expert Comment

    tks Alex for the grade.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now