View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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