# IF, OR, Statements from non-numerical values

#### oishii

##### New Member
Hi,

I am trying to manipulate data contained in an xls that has been, until now, updated without much formatting. I have updated all the cells in a column named "Duration" to contain a drop list with the following values:

All day
AM only
PM only
Evening

These values actually equal payable time spent completing a task (hours and minutes) so:

All day = 7hrs
AM only = 3hrs 30mins
PM only = 3hrs 30mins
Evening = 7hrs

I want to create a column that determines the appropriate number of hours from the user selection in the column "Duration". For example, user selects "All day" in a cell within "Duration" column, and the next column is updated with "7".

I've attempted an IF statement "=IF(C2="All day", 7)", which worked for 1 value, but I'm having trouble with additional variables - can anyone help?

Cheers!

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Gerald Higgins

##### Well-known Member
Hi oishii and welcome to the board.

Your 4 values can be boiled down to 2.
But if you have more than 7 values, you will need another solution, such as lookup.

Here's an IF solution
Code:
``=if(or(c2="All day",c2="Evening"),7,3.5)``

Or, if the cell can be empty
Code:
``=if(or(c2="All day",c2="Evening"),7,if(c2="",0,3.5))``

#### dk

##### MrExcel MVP
Hi

You could use a formula such as this:

=IF(OR(C3="All Day",C3="Evening"),7,IF(OR(C3="AM only",C3="PM only"),3.5,0))

(assumes that the data is in cell C3).

Perhaps a better way would be to set up a small table that has your possible options along with the times e.g.

<table>
<tr><td>All Day</td><td>7</td></tr>
<tr><td>Evening</td><td>7</td></tr>
<tr><td>AM only</td><td>3.5</td></tr>
<tr><td>PM only</td><td>3.5</td></tr>
</table>

You could call this table something like "TimeMap" and then use a VLOOKUP formula such as this:

=VLOOKUP(C3,TimeMap,2,FALSE)

The benefit of this is that you could add additional times in the future e.g. Half Morning and the formula would still work.

HTH
DK

#### oishii

##### New Member
Excellent stuff!! Thanks to both of you for all your help.....now which will I use

Replies
5
Views
282
Replies
3
Views
131
Replies
4
Views
103
Replies
4
Views
115
Replies
7
Views
284

1,190,677
Messages
5,982,213
Members
439,769
Latest member
trungminh2802

### 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.

### Which adblocker are you using?

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