Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
I'm trying to use the If function for a schedule.
If cell F5 = 11a-7p, then 7.5, if F5=6a-1:30p, then 7,If 6:30a-1:30p, then 6.5. I keep getting the INVALID error. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
On Tue, 4 Dec 2007 18:30:01 -0800, Sunshine wrote:
I'm trying to use the If function for a schedule. If cell F5 = 11a-7p, then 7.5, if F5=6a-1:30p, then 7,If 6:30a-1:30p, then 6.5. I keep getting the INVALID error. First, you will have to give some different order to your overlapping conditions. If they are in order you wrote, then you will never get 6.5 as a result (because the middle condition includes all of the later). Same thing with time between 11am and 13:30pm - first condition takes it first. Besides that, the correct formula would be (in one row, of course): =IF(AND(F5=TIME(11,0,0),F5<=TIME(19,0,0)),7.5,IF( AND(F5=TIME(6,0,0),F5<=TIME(13,30,0)),7,IF(AND(F5 =TIME(6,30,0),F5<=TIME(13,30,0)),6.5,0))) If you look at it differently, this is the logic of it: =IF(AND(F5=TIME(11,0,0),F5<=TIME(19,0,0)), 7.5, IF(AND(F5=TIME(6,0,0),F5<=TIME(13,30,0)), 7, IF(AND(F5=TIME(6,30,0),F5<=TIME(13,30,0)), 6.5, 0) ) ) hth B. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |