IF, OR, Statements from non-numerical values

[oishii]

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!

 

[Gerald Higgins]

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

=if(or(c2="All day",c2="Evening"),7,3.5)

Or, if the cell can be empty

=if(or(c2="All day",c2="Evening"),7,if(c2="",0,3.5))

 

[dk]

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]

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