Formula needed ( conditional If statement)

[ATester]

Hi. I would like to create a calculated column which looks at the values in one column and returns the values in another column based on a formula.

So, for example if the value in cell a<0 then return "Excellent" , if the value is cell a >0 but less than 5 then return "Good", if the value in cell a is >5 then return "poor".

=if([ColumnName]<0,0);([ColumnName]>1,1) ;([ColumnName]>5,3); ([ColumnName]>10,4)

 

[ghrain22]

I believe you're looking for the lookup function:
LOOKUP function - Excel

=Lookup(A1, {-1, 0, 5},{"Excellent", "Good", "Poor"})

 

[Scott Huish]

What if the value is exactly zero?

 

[ATester]

What if the value is exactly zero?

Result should also be 0 (which is Excellent).

 

[ghrain22]

Result should also be 0 (which is Excellent).

Either of these should work although I created a bound of -5 for Excellent.

=LOOKUP(A1, {-5,"Excellent";0,"Good";5,"Poor"})
=LOOKUP(A1, {-5,0,5},{"Excellent","Good","Poor"})

 

[ATester]

Either of these should work although I created a bound of -5 for Excellent.

=LOOKUP(A1, {-5,"Excellent";0,"Good";5,"Poor"})
=LOOKUP(A1, {-5,0,5},{"Excellent","Good","Poor"})

Actually the LOOKUP formula is not available in BI. All I can see is LOOKUPVALUE

 

[ghrain22]

Actually the LOOKUP formula is not available in BI. All I can see is LOOKUPVALUE

I know nothing about the functions available in BI or whether they support arrays like that.

 

[scottsen]

I really need to talk to the mods about taking this forum off "search for unanswered questions". The well meaning folks just waste their time

OP, for a calc column, something like these "nested" if's... typically.

=if([ColumnName] <= 0, "Excellent", if([ColumnName] < 5, "Good", "Poor"))

 

[ATester]

I really need to talk to the mods about taking this forum off "search for unanswered questions". The well meaning folks just waste their time

OP, for a calc column, something like these "nested" if's... typically.

=if([ColumnName] <= 0, "Excellent", if([ColumnName] < 5, "Good", "Poor"))

This worked thanks!

 

[ATester]

I really need to talk to the mods about taking this forum off "search for unanswered questions". The well meaning folks just waste their time

OP, for a calc column, something like these "nested" if's... typically.

=if([ColumnName] <= 0, "Excellent", if([ColumnName] < 5, "Good", "Poor"))

That's great.

What if I complicate things further and need to get the results of the if statement to be "Y" or "N" from two different tables. So that if both conditions are true, flag yes otherwise flag no.

= if (([ColumnNameA] = "A") and If ([ColumnNameB] = 2013), "Y", "N")