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

x
  • 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.

0
Alex_Vaughan87
Asked:
Alex_Vaughan87
1 Solution
 
Arno KosterCommented:
that would be

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

Open in new window


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

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
 
gowflowCommented:
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))

Open in new window

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

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
 
andrewssd3Commented:
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%
0
 
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.
0
 
gowflowCommented:
tks Alex for the grade.
gowflow
0

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