| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF function
I want to use the IF function for working out the time difference between two
cells eg if planned time was A= 09:00 and actual time B=11:00 C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B A+3Hr |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
Since you didn't provide values for in-between values, I'll assume you want
to round, or it's not an issue. =CHOOSE(ROUND((B2-A2)*24+1,0),4,3,2,1) You might like to change the ROUND to INT or ROUNDUP, depending on your situation. Thankfully, both of those use the exact same arguements. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Graham" wrote: I want to use the IF function for working out the time difference between two cells eg if planned time was A= 09:00 and actual time B=11:00 C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B A+3Hr |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
Try,
=LOOKUP(TEXT(B1-A1,"h"),{"0","1","2","3"},{4,3,2,1}) Mike "Graham" wrote: I want to use the IF function for working out the time difference between two cells eg if planned time was A= 09:00 and actual time B=11:00 C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B A+3Hr |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
Hi Luke
Thanks,however How do I incorporate the IF argument base on the senario below where the argument is based on Cell 'C' which is the difference between 'B'-'A' C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B A+3Hr "Luke M" wrote: Since you didn't provide values for in-between values, I'll assume you want to round, or it's not an issue. =CHOOSE(ROUND((B2-A2)*24+1,0),4,3,2,1) You might like to change the ROUND to INT or ROUNDUP, depending on your situation. Thankfully, both of those use the exact same arguements. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Graham" wrote: I want to use the IF function for working out the time difference between two cells eg if planned time was A= 09:00 and actual time B=11:00 C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B A+3Hr |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
Graham,
You put Luke's (or my) formula in column c and drag down as required. Mike "Graham" wrote: Hi Luke Thanks,however How do I incorporate the IF argument base on the senario below where the argument is based on Cell 'C' which is the difference between 'B'-'A' C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B A+3Hr "Luke M" wrote: Since you didn't provide values for in-between values, I'll assume you want to round, or it's not an issue. =CHOOSE(ROUND((B2-A2)*24+1,0),4,3,2,1) You might like to change the ROUND to INT or ROUNDUP, depending on your situation. Thankfully, both of those use the exact same arguements. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Graham" wrote: I want to use the IF function for working out the time difference between two cells eg if planned time was A= 09:00 and actual time B=11:00 C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B A+3Hr |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
Thanks Mike, worked beautifully, If i need any other help I will definately
contact you guys Regards "Mike H" wrote: Graham, You put Luke's (or my) formula in column c and drag down as required. Mike "Graham" wrote: Hi Luke Thanks,however How do I incorporate the IF argument base on the senario below where the argument is based on Cell 'C' which is the difference between 'B'-'A' C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B A+3Hr "Luke M" wrote: Since you didn't provide values for in-between values, I'll assume you want to round, or it's not an issue. =CHOOSE(ROUND((B2-A2)*24+1,0),4,3,2,1) You might like to change the ROUND to INT or ROUNDUP, depending on your situation. Thankfully, both of those use the exact same arguements. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Graham" wrote: I want to use the IF function for working out the time difference between two cells eg if planned time was A= 09:00 and actual time B=11:00 C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B A+3Hr |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
Hi Mike I tried what you said and it worked great , however what happens when
(B-A) is -1 or -2 etc, Here I need the Lookup to return 4 always if the diifference is <=0 "Mike H" wrote: Graham, You put Luke's (or my) formula in column c and drag down as required. Mike "Graham" wrote: Hi Luke Thanks,however How do I incorporate the IF argument base on the senario below where the argument is based on Cell 'C' which is the difference between 'B'-'A' C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B A+3Hr "Luke M" wrote: Since you didn't provide values for in-between values, I'll assume you want to round, or it's not an issue. =CHOOSE(ROUND((B2-A2)*24+1,0),4,3,2,1) You might like to change the ROUND to INT or ROUNDUP, depending on your situation. Thankfully, both of those use the exact same arguements. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Graham" wrote: I want to use the IF function for working out the time difference between two cells eg if planned time was A= 09:00 and actual time B=11:00 C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B A+3Hr |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF function
"Graham" wrote: Hi Mike I tried what you said and it worked great , however what happens when (B-A) is -1 or -2 etc, Here I need the Lookup to return 4 always if the diifference is <=0, "Mike H" wrote: Graham, You put Luke's (or my) formula in column c and drag down as required. Mike "Graham" wrote: Hi Luke Thanks,however How do I incorporate the IF argument base on the senario below where the argument is based on Cell 'C' which is the difference between 'B'-'A' C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B A+3Hr "Luke M" wrote: Since you didn't provide values for in-between values, I'll assume you want to round, or it's not an issue. =CHOOSE(ROUND((B2-A2)*24+1,0),4,3,2,1) You might like to change the ROUND to INT or ROUNDUP, depending on your situation. Thankfully, both of those use the exact same arguements. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Graham" wrote: I want to use the IF function for working out the time difference between two cells eg if planned time was A= 09:00 and actual time B=11:00 C needs to return a value of 4 if B=A ;3 if B=A+1Hr; 2 if B=A+2Hr; 1 if B A+3Hr |
| 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 | |||
Linear Mode
