Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Formatting
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Formatting
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
14 Aug 09: Glenn, I couldn't tell if my reply post saying thank you for your
help was received. Your correction, of my stab at the IF statement is working perfectly. 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","")) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula Formatting
On Aug 14, 11:46�pm, rhg wrote:
14 Aug 09: �Glenn, I couldn't tell if my reply post saying thank you for your help was received. �Your correction, of my stab at the IF statement is working perfectly. �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. One of the great viruses which infects programmers (and you are programming your spreadsheet) is the WT*DIDT (What The *** Did I Do There) virus, and its more deadly variant (Why the *** Did I Do That). If you go on adding conditions you will certainly be infected, and it has a 6 month incubation time. <G I would suggest you use a small array (in a hidden part of your spreadsheet) as a lookup table. Because your limit days are not contigous you need to use a slightly different format than the usual VLOOKUP(Index, Array, constant_offset) function. You should use MATCH () to find the offset Lets start with the table, I show my rows & columns so that you can understand the formula. You would need to change them to suit your spreadsheet. Note that all look-up lists must be in ascending order. . . . Column N O P Q R S T Row 4 -1 8 31 121 1500 5 6 E ALERT ALERT ALERT ALERT 7 R REVIEW REVIEW 8 U ALERT ALERT ALERT Then use the formula for your warning text in row 5 as ... =IF(ISBLANK(C5),"",IF(COUNTIF(J5:K5,"X")=0,VLOOKUP (C5,$O$5:$T$8,MATCH (D5,$P$4:$T$4,1)+1,TRUE),"")) If C5 is blank soak up the error text. Else if C5 is good, Count any X in cols J & K, if there's none then lookup in column O the "E", "R", or "U", and offset into the table by the match into row 4. Note that "empty" cells in the look-up table must have a space character in them, and the MATCH() row must have one more than the limit because if match cannot find a match it takes the column before it (the final 1 match-type parameter). The result of MATCH() also needs the +1 because the VLOOKUP index starts from 1 as the column index. Note the -1 (less than 0) & 1500 (or some big number) to ensure that there is always a number to match. Note the use of "$" to make absolute references (inserted by F4 presses with the cursor in the reference). If you do the above you will always understand what you have done (even after six months), and can readily alter your table to add additional alerts as the users ask for them (...and they _will_<g). Alan Lloyd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula & formatting | Excel Worksheet Functions | |||
"Excel encountered an error and had to remove some formatting toavoid corrupting the workbook. Please re-check your formatting carefully." | Excel Discussion (Misc queries) | |||
Excel Formula using Conditional Formatting | Setting up and Configuration of Excel | |||
Formatting with a Formula | Excel Discussion (Misc queries) | |||
excel formula formatting result | Excel Worksheet Functions |