Solved

# Excel extended OR formula

Posted on 2011-10-03
188 Views
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.

0
Question by:Alex_Vaughan87

LVL 19

Expert Comment

that would be

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

0

LVL 92

Expert Comment

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

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2637-Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html
0

LVL 29

Accepted Solution

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

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]))

or

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])
0

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)
``````

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%
0

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

LVL 29

Expert Comment

gowflow
0

## Featured Post

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