excel formula

M

manickmj

I am working on a bonus chart and this is what I am trying to do:

If an employee keeps his liquid tempatures under 40 degrees the
recieve 100 points. If they are higher than 40 degrees they get
points. the problem is they have two temperature check per week. I
they are over on one of the checks they will not get the 100 points.
can I have one formula to check both cells (Both checks) without usin
an average between the two temps? I have tried playing with "IF
formulas until I am blue in the face. Please help

Thanks
Mike Manick
Longmont, Colorad
 
M

Mike

manickmj > said:
I am working on a bonus chart and this is what I am trying to do:

If an employee keeps his liquid tempatures under 40 degrees they
recieve 100 points. If they are higher than 40 degrees they get 0
points. the problem is they have two temperature check per week. If
they are over on one of the checks they will not get the 100 points.
can I have one formula to check both cells (Both checks) without using
an average between the two temps? I have tried playing with "IF"
formulas until I am blue in the face. Please help

Thanks
Mike Manick
Longmont, Colorado
Click to expand...


Mike,

Here is one way to do it: IF(MAX(A1,A2)>40,0,100)

Change A1 and A2 to the appropriate cells. Replace the > with >= if
you don't want to award points for exactly 40 degrees.


Hope this helps,

Mike
 
M

Max

Assume an example set-up in cols A to D
col headers in row1, data from row2 down:

Ecode..Wk1Temp..Wk2Temp..Points
Emp1.....39.......40.......??
etc

Put in D2: =IF(AND(B2<40,C2<40),100,"")
Copy D2 down

Col D will return 100 points only if
*both* week1's and week2's temperatures are below 40
 
M

Max

.. problem is they have two temperature check per week

Oops, sorry, misread this detail
which affects the descriptives ..
but the principle's the same <lucky me!>

Amendments:

In cols A to D:

Ecode..Temp1..Temp2..Points
Emp1.....39.....40....??
etc

Put in D2: =IF(AND(B2<40,C2<40),100,"")
Copy D2 down

Col D will return 100 points only if
*both* temperature readings (Temp1 and Temp2)
are below 40
 
Top