| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two cell Comparison with three different Criteria
=(IF(OR('Calculation-Part 46 & 48 Viols.'!O17="No
Likelihood",'Calculation-Part 46 & 48 Viols.'!O17="Unlikely"),60,VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE)),(IF('Calculation-Part 46 & 48 Viols.'!U15<=19,60,VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE)))) I have came upon an obstcale that I cannot over come. What I am trying to accomplish is to write a formula were I am referencing two different cells and comparing three different criteria. The above formula I get a #Vallue error. I have tried multiple formula designs and can not get anything to work. If I have a 104(a) Citation that has a gravity of No Likelihood or Unlikely and also has a violation history of less than or equal to 19 points the value should equate out to $60. Let say the violation history is larger than 19, then the value should equate out to whatever the points total equals. I am using a VLOOKUP formula to search for this value. On the other hand if the gravity is higher, lets say reasonably likely then the formula should lookup the dollar value from my table. I am calculating a dollar penalty amount for what is called a non-S&S single penalty. I have developed this spread sheet to help automate and speed up some calculations. Please review and offer any help to try to arrive at a formula that will work. Thanks Don |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two cell Comparison with three different Criteria
Try this..........
=IF(OR('Calculation-Part 46 & 48 Viols.'!O17="No Likelihood",'Calculation-Part 46 & 48 Viols.'!O17="Unlikely",'Calculation-Part 46 & 48 Viols.'!U15<=19),60,VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE)) All on one line, watch out for email word-wrap Vaya con Dios, Chuck, CABGx3 " wrote: =(IF(OR('Calculation-Part 46 & 48 Viols.'!O17="No Likelihood",'Calculation-Part 46 & 48 Viols.'!O17="Unlikely"),60,VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE)),(IF('Calculation-Part 46 & 48 Viols.'!U15<=19,60,VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE)))) I have came upon an obstcale that I cannot over come. What I am trying to accomplish is to write a formula were I am referencing two different cells and comparing three different criteria. The above formula I get a #Vallue error. I have tried multiple formula designs and can not get anything to work. If I have a 104(a) Citation that has a gravity of No Likelihood or Unlikely and also has a violation history of less than or equal to 19 points the value should equate out to $60. Let say the violation history is larger than 19, then the value should equate out to whatever the points total equals. I am using a VLOOKUP formula to search for this value. On the other hand if the gravity is higher, lets say reasonably likely then the formula should lookup the dollar value from my table. I am calculating a dollar penalty amount for what is called a non-S&S single penalty. I have developed this spread sheet to help automate and speed up some calculations. Please review and offer any help to try to arrive at a formula that will work. Thanks Don |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two cell Comparison with three different Criteria
Biff thanks, I appreciate the help very much. By the way it is MSHA
|
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two cell Comparison with three different Criteria
Thanks Chuck for the help. I appreciate it very much. Don
|
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two cell Comparison with three different Criteria - Need Additional Help
I applied the two formulas that I received and still can not achieve
the outcome that I need. My modified formula is as follows: =IF(OR('Calculation-Part 46 & 48 Viols.'!O17={"No Likelihood","Unlikely"}),60,IF('Calculation-Part 46 & 48 Viols.'!O17<{"No Likelihood","Unlikely"},VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,IF(OR('Calculation-Part 46 & 48 Viols.'!U1519,'Calculation-Part 46 & 48 Viols.'!O17={"No Likelihood","Unlikely"}),VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE))))) What the problem is now is that when I have a violation history greater than 19. The calculation should equate out to VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE. Instead the result I get is 60 when I have a violation history greater than 19 and the gravity is shown to be No Likelihood or Unlikely. For whatever reason Excel will not follow the execution of the formula as wrote. What have I done wrong? Your help is greatly appreciated. Thanks Don wrote: =(IF(OR('Calculation-Part 46 & 48 Viols.'!O17="No Likelihood",'Calculation-Part 46 & 48 Viols.'!O17="Unlikely"),60,VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE)),(IF('Calculation-Part 46 & 48 Viols.'!U15<=19,60,VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE)))) I have came upon an obstcale that I cannot over come. What I am trying to accomplish is to write a formula were I am referencing two different cells and comparing three different criteria. The above formula I get a #Vallue error. I have tried multiple formula designs and can not get anything to work. If I have a 104(a) Citation that has a gravity of No Likelihood or Unlikely and also has a violation history of less than or equal to 19 points the value should equate out to $60. Let say the violation history is larger than 19, then the value should equate out to whatever the points total equals. I am using a VLOOKUP formula to search for this value. On the other hand if the gravity is higher, lets say reasonably likely then the formula should lookup the dollar value from my table. I am calculating a dollar penalty amount for what is called a non-S&S single penalty. I have developed this spread sheet to help automate and speed up some calculations. Please review and offer any help to try to arrive at a formula that will work. Thanks Don |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two cell Comparison with three different Criteria - Need Additional Help
Hi!
Instead the result I get is 60 when I have a violation history greater than 19 and the gravity is shown to be No Likelihood or Unlikely. That's because in the formula below, you're not testing if the violation history is greater than 19 until AFTER you test for No Likelihood or Unlikely. =IF(OR('Calculation-Part 46 & 48 Viols.'!O17={"No Likelihood","Unlikely"}),60, If O17 equals either No Likelihood or Unlikely, the formula returns 60 and no more processing of that formula takes place. An IF formula stops as soon as a condition is found to be TRUE. As I understood the original post: If O17 = No Likelihood or Unlikely OR U15 <19 the formula should return 60. Maybe you need something like this: =IF(AND(U15<19,OR(O17={"No Likelihood","Unlikely"})),60,IF(AND(U1519,OR(O17= {"No Likelihood","Unlikely"})),VLOOKUP(U29,A4:B115,2,0) ,"not_defined")) But what happens if U15 = 19 or O17 < No Likelihood or Unlikely? Maybe we need some more details. Biff wrote in message oups.com... I applied the two formulas that I received and still can not achieve the outcome that I need. My modified formula is as follows: =IF(OR('Calculation-Part 46 & 48 Viols.'!O17={"No Likelihood","Unlikely"}),60,IF('Calculation-Part 46 & 48 Viols.'!O17<{"No Likelihood","Unlikely"},VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,IF(OR('Calculation-Part 46 & 48 Viols.'!U1519,'Calculation-Part 46 & 48 Viols.'!O17={"No Likelihood","Unlikely"}),VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE))))) What the problem is now is that when I have a violation history greater than 19. The calculation should equate out to VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE. Instead the result I get is 60 when I have a violation history greater than 19 and the gravity is shown to be No Likelihood or Unlikely. For whatever reason Excel will not follow the execution of the formula as wrote. What have I done wrong? Your help is greatly appreciated. Thanks Don wrote: =(IF(OR('Calculation-Part 46 & 48 Viols.'!O17="No Likelihood",'Calculation-Part 46 & 48 Viols.'!O17="Unlikely"),60,VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE)),(IF('Calculation-Part 46 & 48 Viols.'!U15<=19,60,VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE)))) I have came upon an obstcale that I cannot over come. What I am trying to accomplish is to write a formula were I am referencing two different cells and comparing three different criteria. The above formula I get a #Vallue error. I have tried multiple formula designs and can not get anything to work. If I have a 104(a) Citation that has a gravity of No Likelihood or Unlikely and also has a violation history of less than or equal to 19 points the value should equate out to $60. Let say the violation history is larger than 19, then the value should equate out to whatever the points total equals. I am using a VLOOKUP formula to search for this value. On the other hand if the gravity is higher, lets say reasonably likely then the formula should lookup the dollar value from my table. I am calculating a dollar penalty amount for what is called a non-S&S single penalty. I have developed this spread sheet to help automate and speed up some calculations. Please review and offer any help to try to arrive at a formula that will work. Thanks Don |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two cell Comparison with three different Criteria - Need Additional Help
Biff, thanks for your help. As per your request "But what happens if
U15 = 19 or O17 < No Likelihood or Unlikely?" In this scenario the value would be equal to the VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE formula . When the violation history is less than 20 and the gravity is not equal to "No Likelihood" or "Unlikely" the penalty will be greater than $60, what ever the VLOOKUP equate out to be. Please let me know if you need any additional information. Thanks Don Biff wrote: Hi! Instead the result I get is 60 when I have a violation history greater than 19 and the gravity is shown to be No Likelihood or Unlikely. That's because in the formula below, you're not testing if the violation history is greater than 19 until AFTER you test for No Likelihood or Unlikely. =IF(OR('Calculation-Part 46 & 48 Viols.'!O17={"No Likelihood","Unlikely"}),60, If O17 equals either No Likelihood or Unlikely, the formula returns 60 and no more processing of that formula takes place. An IF formula stops as soon as a condition is found to be TRUE. As I understood the original post: If O17 = No Likelihood or Unlikely OR U15 <19 the formula should return 60. Maybe you need something like this: =IF(AND(U15<19,OR(O17={"No Likelihood","Unlikely"})),60,IF(AND(U1519,OR(O17= {"No Likelihood","Unlikely"})),VLOOKUP(U29,A4:B115,2,0) ,"not_defined")) But what happens if U15 = 19 or O17 < No Likelihood or Unlikely? Maybe we need some more details. Biff wrote in message oups.com... I applied the two formulas that I received and still can not achieve the outcome that I need. My modified formula is as follows: =IF(OR('Calculation-Part 46 & 48 Viols.'!O17={"No Likelihood","Unlikely"}),60,IF('Calculation-Part 46 & 48 Viols.'!O17<{"No Likelihood","Unlikely"},VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,IF(OR('Calculation-Part 46 & 48 Viols.'!U1519,'Calculation-Part 46 & 48 Viols.'!O17={"No Likelihood","Unlikely"}),VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE))))) What the problem is now is that when I have a violation history greater than 19. The calculation should equate out to VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE. Instead the result I get is 60 when I have a violation history greater than 19 and the gravity is shown to be No Likelihood or Unlikely. For whatever reason Excel will not follow the execution of the formula as wrote. What have I done wrong? Your help is greatly appreciated. Thanks Don wrote: =(IF(OR('Calculation-Part 46 & 48 Viols.'!O17="No Likelihood",'Calculation-Part 46 & 48 Viols.'!O17="Unlikely"),60,VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE)),(IF('Calculation-Part 46 & 48 Viols.'!U15<=19,60,VLOOKUP('Calculation-Part 46 & 48 Viols.'!U29,'Special Assessment Table-AccInv'!A4:B115,2,FALSE)))) I have came upon an obstcale that I cannot over come. What I am trying to accomplish is to write a formula were I am referencing two different cells and comparing three different criteria. The above formula I get a #Vallue error. I have tried multiple formula designs and can not get anything to work. If I have a 104(a) Citation that has a gravity of No Likelihood or Unlikely and also has a violation history of less than or equal to 19 points the value should equate out to $60. Let say the violation history is larger than 19, then the value should equate out to whatever the points total equals. I am using a VLOOKUP formula to search for this value. On the other hand if the gravity is higher, lets say reasonably likely then the formula should lookup the dollar value from my table. I am calculating a dollar penalty amount for what is called a non-S&S single penalty. I have developed this spread sheet to help automate and speed up some calculations. Please review and offer any help to try to arrive at a formula that will work. Thanks Don |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two cell Comparison with three different Criteria - Need Additional Help
Biff, I'm sorry it has taken me so long to reply but due to the
Holidays I was off from work a few days but I'm back now. I thank you very much for your help. The formula worked like a charm. I've worked on this thing for several days to no avail and was becoming very frustrated. Therefore, I thought I would solicit some help. Again thanks for the help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
| Locate a cell, based on a criteria, then use the 'Cell' command... | Excel Discussion (Misc queries) | |||
| Cell color based upon cell value | Excel Discussion (Misc queries) | |||
| cell color index comparison | New Users to Excel | |||
| Using IF when more than one cell contains criteria | Excel Worksheet Functions | |||
Linear Mode
