Formula needed ( conditional If statement)

ATester

New Member
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)

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
I believe you're looking for the lookup function:
LOOKUP function - Excel

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

What if the value is exactly zero?

What if the value is exactly zero?

Result should also be 0 (which is Excellent).

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"})

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

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.

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

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!

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

Replies
14
Views
662
Replies
3
Views
277
Replies
8
Views
1K
Replies
1
Views
113
Replies
3
Views
423

1,219,915
Messages
6,150,940
Members
450,996
Latest member
darko1515s

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back