Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

Excel extended OR formula

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.

1 Solution
Arno KosterCommented:
that would be

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

Open in new window

Patrick MatthewsCommented:
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:

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Arno KosterCommented:
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])
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%
Alex_Vaughan87Author Commented:
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.
tks Alex for the grade.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now