| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choose a specific calculating formula for a cell
How do I get a cell to look at another cell and when a specific
parameter is met it chooses the correct formula i.e. First parameters A1: cell has the value 07/04/2007 9:45 Available at B1: cell has the value 07/04/2007 17:00 Due by Time C1: cell has the value 07/04/2007 14.30 Time delivered Calculation A for this parameter would be =c1-a1 Second parameters A1: cell has the value 07/04/2007 12:30 Available at B1: cell has the value 08/04/2007 12:00 Due by Time C1: cell has the value 08/04/2007 10.30 Time delivered Calculation B for this parameter would be =c1-a1-15/24 HOW DO I GET CELL D1 to choose calculation A when the due by time is 17:00 or calculation B when the due by time is 12:00 Thanks JP |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Choose a specific calculating formula for a cell
Hi
=IF(HOUR(B1)=12,C1-A1-15/24,IF(HOUR(B1)=17,C1-A1,"")) The final null "" is for the situation if B1 is neither 12:00 nor 17:00. Amend to suit. -- Regards Roger Govier wrote in message oups.com... How do I get a cell to look at another cell and when a specific parameter is met it chooses the correct formula i.e. First parameters A1: cell has the value 07/04/2007 9:45 Available at B1: cell has the value 07/04/2007 17:00 Due by Time C1: cell has the value 07/04/2007 14.30 Time delivered Calculation A for this parameter would be =c1-a1 Second parameters A1: cell has the value 07/04/2007 12:30 Available at B1: cell has the value 08/04/2007 12:00 Due by Time C1: cell has the value 08/04/2007 10.30 Time delivered Calculation B for this parameter would be =c1-a1-15/24 HOW DO I GET CELL D1 to choose calculation A when the due by time is 17:00 or calculation B when the due by time is 12:00 Thanks JP |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Choose a specific calculating formula for a cell
On May 8, 10:11 am, "Roger Govier"
wrote: Hi =IF(HOUR(B1)=12,C1-A1-15/24,IF(HOUR(B1)=17,C1-A1,"")) The final null "" is for the situation if B1 is neither 12:00 nor 17:00. Amend to suit. -- Regards Roger Govier wrote in message oups.com... How do I get a cell to look at another cell and when a specific parameter is met it chooses the correct formula i.e. First parameters A1: cell has the value 07/04/2007 9:45 Available at B1: cell has the value 07/04/2007 17:00 Due by Time C1: cell has the value 07/04/2007 14.30 Time delivered Calculation A for this parameter would be =c1-a1 Second parameters A1: cell has the value 07/04/2007 12:30 Available at B1: cell has the value 08/04/2007 12:00 Due by Time C1: cell has the value 08/04/2007 10.30 Time delivered Calculation B for this parameter would be =c1-a1-15/24 HOW DO I GET CELL D1 to choose calculation A when the due by time is 17:00 or calculation B when the due by time is 12:00 Thanks JP- Hide quoted text - - Show quoted text - You are good, thank you very much for your help |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Choose a specific calculating formula for a cell
You're very welcome. Thanks for the feedback
-- Regards Roger Govier wrote in message oups.com... On May 8, 10:11 am, "Roger Govier" wrote: Hi =IF(HOUR(B1)=12,C1-A1-15/24,IF(HOUR(B1)=17,C1-A1,"")) The final null "" is for the situation if B1 is neither 12:00 nor 17:00. Amend to suit. -- Regards Roger Govier wrote in message oups.com... How do I get a cell to look at another cell and when a specific parameter is met it chooses the correct formula i.e. First parameters A1: cell has the value 07/04/2007 9:45 Available at B1: cell has the value 07/04/2007 17:00 Due by Time C1: cell has the value 07/04/2007 14.30 Time delivered Calculation A for this parameter would be =c1-a1 Second parameters A1: cell has the value 07/04/2007 12:30 Available at B1: cell has the value 08/04/2007 12:00 Due by Time C1: cell has the value 08/04/2007 10.30 Time delivered Calculation B for this parameter would be =c1-a1-15/24 HOW DO I GET CELL D1 to choose calculation A when the due by time is 17:00 or calculation B when the due by time is 12:00 Thanks JP- Hide quoted text - - Show quoted text - You are good, thank you very much for your help |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Choose a specific calculating formula for a cell
On May 8, 10:53 am, "Roger Govier"
wrote: You're very welcome. Thanks for the feedback -- Regards Roger Govier wrote in message oups.com... On May 8, 10:11 am, "Roger Govier" wrote: Hi =IF(HOUR(B1)=12,C1-A1-15/24,IF(HOUR(B1)=17,C1-A1,"")) The final null "" is for the situation if B1 is neither 12:00 nor 17:00. Amend to suit. -- Regards Roger Govier wrote in message groups.com... How do I get a cell to look at another cell and when a specific parameter is met it chooses the correct formula i.e. First parameters A1: cell has the value 07/04/2007 9:45 Available at B1: cell has the value 07/04/2007 17:00 Due by Time C1: cell has the value 07/04/2007 14.30 Time delivered Calculation A for this parameter would be =c1-a1 Second parameters A1: cell has the value 07/04/2007 12:30 Available at B1: cell has the value 08/04/2007 12:00 Due by Time C1: cell has the value 08/04/2007 10.30 Time delivered Calculation B for this parameter would be =c1-a1-15/24 HOW DO I GET CELL D1 to choose calculation A when the due by time is 17:00 or calculation B when the due by time is 12:00 Thanks JP- Hide quoted text - - Show quoted text - You are good, thank you very much for your help- Hide quoted text - - Show quoted text - Just one more tiny favour, if the hour is not defined by 12 or 17 can a formula still be achieved. I see you given me the none "" =IF(HOUR(B1)=12,C1- A1-15/24,IF(HOUR(B1)=17,C1-A1,"")) Let say I have orders that do not need this, example a difference service level A1: cell has the value 07/04/2007 12:30 Available at B1: cell has the value 07/04/2007 14:30 Due by Time C1: cell has the value 07/04/2007 13.30 Time delivered |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Choose a specific calculating formula for a cell
Hi
The formula I gave would insert a null in the cell, if the value in B1 was neither 17:00 nor 12:00. With your latest example, 14:30 would come into either category, hence there would not be a calculation showing in the C column. Is there some other situation or some other calculation that should prevail if the time is 14:30? Can you give a list of the conditions, and what time calculation you want to show up? -- Regards Roger Govier wrote in message ups.com... On May 8, 10:53 am, "Roger Govier" wrote: You're very welcome. Thanks for the feedback -- Regards Roger Govier wrote in message oups.com... On May 8, 10:11 am, "Roger Govier" wrote: Hi =IF(HOUR(B1)=12,C1-A1-15/24,IF(HOUR(B1)=17,C1-A1,"")) The final null "" is for the situation if B1 is neither 12:00 nor 17:00. Amend to suit. -- Regards Roger Govier wrote in message groups.com... How do I get a cell to look at another cell and when a specific parameter is met it chooses the correct formula i.e. First parameters A1: cell has the value 07/04/2007 9:45 Available at B1: cell has the value 07/04/2007 17:00 Due by Time C1: cell has the value 07/04/2007 14.30 Time delivered Calculation A for this parameter would be =c1-a1 Second parameters A1: cell has the value 07/04/2007 12:30 Available at B1: cell has the value 08/04/2007 12:00 Due by Time C1: cell has the value 08/04/2007 10.30 Time delivered Calculation B for this parameter would be =c1-a1-15/24 HOW DO I GET CELL D1 to choose calculation A when the due by time is 17:00 or calculation B when the due by time is 12:00 Thanks JP- Hide quoted text - - Show quoted text - You are good, thank you very much for your help- Hide quoted text - - Show quoted text - Just one more tiny favour, if the hour is not defined by 12 or 17 can a formula still be achieved. I see you given me the none "" =IF(HOUR(B1)=12,C1- A1-15/24,IF(HOUR(B1)=17,C1-A1,"")) Let say I have orders that do not need this, example a difference service level A1: cell has the value 07/04/2007 12:30 Available at B1: cell has the value 07/04/2007 14:30 Due by Time C1: cell has the value 07/04/2007 13.30 Time delivered |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Choose a specific calculating formula for a cell
On May 8, 11:35 am, "Roger Govier"
wrote: Hi The formula I gave would insert a null in the cell, if the value in B1 was neither 17:00 nor 12:00. With your latest example, 14:30 would come into either category, hence there would not be a calculation showing in the C column. Is there some other situation or some other calculation that should prevail if the time is 14:30? Can you give a list of the conditions, and what time calculation you want to show up? -- Regards Roger Govier wrote in message ups.com... On May 8, 10:53 am, "Roger Govier" wrote: You're very welcome. Thanks for the feedback -- Regards Roger Govier wrote in message groups.com... On May 8, 10:11 am, "Roger Govier" wrote: Hi =IF(HOUR(B1)=12,C1-A1-15/24,IF(HOUR(B1)=17,C1-A1,"")) The final null "" is for the situation if B1 is neither 12:00 nor 17:00. Amend to suit. -- Regards Roger Govier wrote in message groups.com... How do I get a cell to look at another cell and when a specific parameter is met it chooses the correct formula i.e. First parameters A1: cell has the value 07/04/2007 9:45 Available at B1: cell has the value 07/04/2007 17:00 Due by Time C1: cell has the value 07/04/2007 14.30 Time delivered Calculation A for this parameter would be =c1-a1 Second parameters A1: cell has the value 07/04/2007 12:30 Available at B1: cell has the value 08/04/2007 12:00 Due by Time C1: cell has the value 08/04/2007 10.30 Time delivered Calculation B for this parameter would be =c1-a1-15/24 HOW DO I GET CELL D1 to choose calculation A when the due by time is 17:00 or calculation B when the due by time is 12:00 Thanks JP- Hide quoted text - - Show quoted text - You are good, thank you very much for your help- Hide quoted text - - Show quoted text - Just one more tiny favour, if the hour is not defined by 12 or 17 can a formula still be achieved. I see you given me the none "" =IF(HOUR(B1)=12,C1- A1-15/24,IF(HOUR(B1)=17,C1-A1,"")) Let say I have orders that do not need this, example a difference service level A1: cell has the value 07/04/2007 12:30 Available at B1: cell has the value 07/04/2007 14:30 Due by Time C1: cell has the value 07/04/2007 13.30 Time delivered- Hide quoted text - - Show quoted text - I have figured out the rest of formula required, by removing the none, it works fine. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| choose last specific nonblank cell from different worksheets | Excel Discussion (Misc queries) | |||
| formula to choose a hyperlink from a cell | Excel Worksheet Functions | |||
| calculating a date with the CHOOSE function | Excel Worksheet Functions | |||
| How to indicate in formula to choose the entire column starting from a specified cell? | Excel Discussion (Misc queries) | |||
| CALCULATING A FORMULA TO USE A SPECIFIC CELL'S VALUES | Excel Worksheet Functions | |||
Linear Mode
