Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Formatting - How to Add a STOP Statement
Now that we are using the spreadsheet. I have been faced
with the question: How do we stop the formula from processing if the patient has either had an appointment or surgery? The simple answer in my mind lies in Columns F & G or J & K. Column F & G asks does patient have an appointment and we just place an x in the Yes or No column. The same is true in J & K except there we ask is the patient having surgery Yes or No. It would seem if we put a "X" in one of those columns then we could stop the clock so to speak. So I'm thinking, I need a STOP statement stating if column J or K has a text entry "X" then stop processing formula in column E, Alert Status. =IF(OR(AND(C4="E",D4=7),AND(C4="U",D4=30)),"ALER T",IF(AND(C4="R",D4=120),"Review",""),AND(J4="X", STOP E4),OR(K4="X", STOP E4)) Any guidance is greatly appreciated. Thank you for all your help rhg "Glenn" wrote: rhg wrote: I'm building a tracking program that monitors medical consults against time and urgency. Column A Column B Column C Column D Column E Tracking # Date Urgency Time Elapsed Status g7186072409 7/24/2009 R 14 s2234072009 7/20/2009 E 18 ALERT a4590071809 7/18/2009 R 20 z0002071409 7/14/2009 U 24 Columns A, B & C are manual data entries Column D =Now()-B4 (B4 is Row that contains first date) Column E Is where my problem lies, here is my formula: =IF(OR(C4=E,&D4=7, "ALERT", " ")+OR(C4=U,&D4=30, "ALERT", " ")+OR(C4=R,&D4=120, "Review", " ")) I think I have too many conditions for IF to handle and I need to create a Macro but herein lies my lack of knowledge. Any assistance would be greatly appreciated, because we are trying to get a handle on our neurosurgery consults. Thank you randy =IF(OR(AND(C4="E",D4=7),AND(C4="U",D4=30)),"ALER T",IF(AND(C4="R",D4=120),"Review","")) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Formatting - How to Add a STOP Statement
No one here knows what "the spreadsheet" is. You have to explain what you
have (ie, the current formula), and what you want it changed to. Examples are the best way of explaining what you want. Regards, Fred "rhg" wrote in message ... Now that we are using the spreadsheet. I have been faced with the question: How do we stop the formula from processing if the patient has either had an appointment or surgery? The simple answer in my mind lies in Columns F & G or J & K. Column F & G asks does patient have an appointment and we just place an x in the Yes or No column. The same is true in J & K except there we ask is the patient having surgery Yes or No. It would seem if we put a "X" in one of those columns then we could stop the clock so to speak. So I'm thinking, I need a STOP statement stating if column J or K has a text entry "X" then stop processing formula in column E, Alert Status. =IF(OR(AND(C4="E",D4=7),AND(C4="U",D4=30)),"ALER T",IF(AND(C4="R",D4=120),"Review",""),AND(J4="X", STOP E4),OR(K4="X", STOP E4)) Any guidance is greatly appreciated. Thank you for all your help rhg "Glenn" wrote: rhg wrote: I'm building a tracking program that monitors medical consults against time and urgency. Column A Column B Column C Column D Column E Tracking # Date Urgency Time Elapsed Status g7186072409 7/24/2009 R 14 s2234072009 7/20/2009 E 18 ALERT a4590071809 7/18/2009 R 20 z0002071409 7/14/2009 U 24 Columns A, B & C are manual data entries Column D =Now()-B4 (B4 is Row that contains first date) Column E Is where my problem lies, here is my formula: =IF(OR(C4=E,&D4=7, "ALERT", " ")+OR(C4=U,&D4=30, "ALERT", " ")+OR(C4=R,&D4=120, "Review", " ")) I think I have too many conditions for IF to handle and I need to create a Macro but herein lies my lack of knowledge. Any assistance would be greatly appreciated, because we are trying to get a handle on our neurosurgery consults. Thank you randy =IF(OR(AND(C4="E",D4=7),AND(C4="U",D4=30)),"ALER T",IF(AND(C4="R",D4=120),"Review","")) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Formatting - How to Add a STOP Statement
rhg wrote,
Fred I'm sorry I included the whole string but I can see now why you are confused. Earlier I asked about =IF statements based on my spread sheet example. rhg wrote: I'm building a tracking program that monitors medical consults against time and urgency. Column A Column B Column C Column D Column E Column F Column J Tracking # Date Urgency Time Elapsed Status Appointment Surgery g7186072409 7/24/2009 R 14 Yes or No Yes or No s2234072009 7/20/2009 E 18 ALERT a4590071809 7/18/2009 R 20 z0002071409 7/14/2009 U 24 Columns A, B & C are manual data entries Column D =Now()-B4 (B4 is Row that contains first date) Column E Is my formula: =IF(OR(AND(C4="E",D4=7),AND(C4="U",D4=30)),"ALER T",IF(AND(C4="R",D4=120),"Review","")) This formula calculates the time elapsed criteria and when its exceeded the warning labels pop up. This part is all working beautifully. Now comes the realization when you want the process to stop because two conditions are met one the patient has had his appointment or he has had surgery. So I need to tell my formula in Col E to STOP if there is a manual entry of an "X" in either Col F or Col J Here is my attempt to modify my formula: =IF(OR(AND(C4="E",D4=7),AND(C4="U",D4=30)),"ALER T",IF(AND(C4="R",D4=120),"Review",""), AND(F4="X",STOP E4),OR(J4="X", STOP E4)) Any guidance is greatly appreciated. rhg |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Formatting - How to Add a STOP Statement
See my advice on this in my reply to your previous posting Alan Lloyd |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Formatting - How to Add a STOP Statement
Your problem is that Excel doesn't understand what "STOP E4" means, and
neither do I. You need to tell us what you want displayed when the conditions are met. Do you want "STOP" displayed? Do you want nothing displayed? Something else? My first guess is you want something like this: =IF(OR(F4="X",J4="X"),"",IF(OR(AND(C4="E",D4=7),A ND(C4="U",D4=30)),"ALERT",IF(AND(C4="R",D4=120), "Review",""))) If that's not what you want, post back. It also appears that you don't understand the makeup of this group. It's a worldwide group of volunteers. It has nothing to do with Microsoft or the US government. You'll get better and quicker responses when you post assuming people know nothing of your situation, and have no past history of any of your posts. Regards, Fred "rhg" wrote in message ... rhg wrote, Fred I'm sorry I included the whole string but I can see now why you are confused. Earlier I asked about =IF statements based on my spread sheet example. rhg wrote: I'm building a tracking program that monitors medical consults against time and urgency. Column A Column B Column C Column D Column E Column F Column J Tracking # Date Urgency Time Elapsed Status Appointment Surgery g7186072409 7/24/2009 R 14 Yes or No Yes or No s2234072009 7/20/2009 E 18 ALERT a4590071809 7/18/2009 R 20 z0002071409 7/14/2009 U 24 Columns A, B & C are manual data entries Column D =Now()-B4 (B4 is Row that contains first date) Column E Is my formula: =IF(OR(AND(C4="E",D4=7),AND(C4="U",D4=30)),"ALER T",IF(AND(C4="R",D4=120),"Review","")) This formula calculates the time elapsed criteria and when its exceeded the warning labels pop up. This part is all working beautifully. Now comes the realization when you want the process to stop because two conditions are met one the patient has had his appointment or he has had surgery. So I need to tell my formula in Col E to STOP if there is a manual entry of an "X" in either Col F or Col J Here is my attempt to modify my formula: =IF(OR(AND(C4="E",D4=7),AND(C4="U",D4=30)),"ALER T",IF(AND(C4="R",D4=120),"Review",""), AND(F4="X",STOP E4),OR(J4="X", STOP E4)) Any guidance is greatly appreciated. rhg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to get Excel to stop formatting text as a date? | Excel Discussion (Misc queries) | |||
How do I get Excel to stop auto-formatting? | Excel Discussion (Misc queries) | |||
how to stop excel formatting | Excel Discussion (Misc queries) | |||
How do I stop Excel auto formatting the text 3-4 as 04 Apr? | Excel Discussion (Misc queries) | |||
=IF Statement formula or conditional formatting | Excel Worksheet Functions |