If Statement

P

Phyllis B.

I have a worksheet with the following columns

A B C
Excess Hrs Hours Wrkd

I want a fornula that will say, if the hours in column B are less than 160,
then I want to enter the difference in cell C, but only to the extent that
there are excess hours available (from cell A). The total of B&C should not
exceed 160 hours. Any suggestions? Thanks.
 
P

Phyllis B.

That works if the three cells always equal 160. Suppose I have this in the
the cells

A B C
88.5 140

I would only want to use 20 of the excess hours, because I don't want to
exceed 160 in cells b & C.

It could also look like this:

A B C
15 85

I would want to put the entire 15 hours in cell C. or

A B C
0 130

Cell C would be 0, because there are no excess hours to use.

The total doesn't have to be 160, it just cannot exceed 160.

thanks.
 
R

RagDyer

Try this:

=IF(160-B1<=0,160,B1+MIN(160-B1,A1))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
W

wjohnson

Not 100% sure what you want but here is an example
=IF(B1<160,A1-B1,0)
Example 1
A B C
160 140 20

Example 2
160 160 0

You can change the "0" to anything you want (i.e. "No Excess Hours"
 
W

wjohnson

Ok - 1 more time

=IF(AND(B3<=160,A3>0,B3+A3<=160),(B3+A3),IF(A3=0,0,"Check"))

If this doesn't work for you
What does Cell A represent
What does Cell B represent
 
Top