Two cell Comparison with three different Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Braenovich.Don@DOL.GOV - 21 Dec 2005 18:45 GMT

=(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

Reply to this Message

Biff - 21 Dec 2005 19:07 GMT

Hi!

Sounds like OSHSA/MSHA ?

Try this:

=(IF(OR('Calculation-Part 46 & 48 Viols.'!O17={"No
Likelihood","Unlikely"},'Calculation-Part 46 & 48
Viols.'!U15<=19),60,VLOOKUP('Calculation-Part 46 & 48
Viols.'!U29,'Special Assessment
Table-AccInv'!A4:B115,2,FALSE))

Biff

raenovich.Don@DOL.GOV> wrote in message
news:1135190753.347633.32740@g14g2000cwa.googlegroups.com...

> =(IF(OR('Calculation-Part 46 & 48 Viols.'!O17="No
> Likelihood",'Calculation-Part 46 & 48
[quoted text clipped - 25 lines]
> Please review and offer any help to try to arrive at a formula that
> will work. Thanks Don

Reply to this Message

Braenovich.Don@DOL.GOV - 22 Dec 2005 15:08 GMT

Biff thanks, I appreciate the help very much. By the way it is MSHA

Reply to this Message

CLR - 21 Dec 2005 19:25 GMT

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

Reply to this Message

Braenovich.Don@DOL.GOV - 22 Dec 2005 15:09 GMT

Thanks Chuck for the help. I appreciate it very much. Don

Reply to this Message

Braenovich.Don@DOL.GOV - 22 Dec 2005 19:07 GMT

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.'!U15>19,'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

Reply to this Message

Biff - 22 Dec 2005 23:39 GMT

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(U15>19,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

>I applied the two formulas that I received and still can not achieve
> the outcome that I need. My modified formula is as follows:
[quoted text clipped - 46 lines]
>> Please review and offer any help to try to arrive at a formula that
>> will work. Thanks Don

Reply to this Message

Braenovich.Don@DOL.GOV - 23 Dec 2005 01:08 GMT

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

> Hi!
>
[quoted text clipped - 79 lines]
> >> Please review and offer any help to try to arrive at a formula that
> >> will work. Thanks Don

Reply to this Message

Braenovich.Don@DOL.GOV - 28 Dec 2005 15:33 GMT

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 to this Message