Alex_Vaughan87
asked on
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_da ta[[#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.
I am using:
=IF(OR(Table_Conversion_da
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.
I'd be tempted to use VLOOKUP for this:
=VLOOKUP(Table_Conversion_ data[[#Thi s 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:
https://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
=VLOOKUP(Table_Conversion_
That assumes you have a defined range, MyLookupRange, with values such as:
0 0
0.25 4
0.5 6
For more about VLOOKUP:
https://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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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])
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])
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.
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%
=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%
ASKER
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.
Cheers all, just needed to know the syntax.
tks Alex for the grade.
gowflow
gowflow
Open in new window