Formula Help

D

Diana

I was wondering if someone could help me. I was needing a formula that could
count how many hours someone has but for it to stop counting at 40. I hope
someone understands me. Thank You.
 
D

Diana

Thank you Roger for a quick answer but I don't understand the formula very
well. I'm sorry, I just don't use excel very much.
 
R

Roger Govier

Hi Diana

Supposing your formula where you are adding up the hours worked is =SUM(B1:B50)

Then change it to
=MIN(40,SUM(B1:B50)

That means that Excel will give a result of 40, if sum(B1:B50) is more than
40, or it will give the result of your formula, if SUM(B1:B50) is less than 40.

Hope this makes it a little clearer, if not post back with some examples of
your data and formula and I will have another go.

Regards

Roger Govier
 
S

Sloth

Give an example of what format the data is in.

Here is an example how I would handle it.

This is data that is inputed by you...
A1: 8:00 AM
A2: 7:30 AM
A3: 8:00 AM
B1: 5:00 PM
B2: 6:00 PM
B3: 5:45 PM

And the output formulas would be like this...
C1: =24*(B1-A1)
C2: =24*(B2-A2)
C3: =24*(B3-A3)
C4: =SUM(C1:C3)

The output would look like this (you need to change the format to numbers)
C1: 9.00
C2: 10.50
C3: 9.75
C4: 29.25

By adding the MIN function to C4 you are saying "ouput either 40 or the sum
of these numbers"

Example:
C4: =MIN(20,SUM(C1:C3))
outputs
C4: 20
because 20 is less than 29.25
But
C4: =MIN(40,SUM(C1:C3))
outputs
C4: 29.25

In your case you would need to use 40 in the MIN function.

Hope this makes things clear.
 
S

Sloth

By adding the MIN function to C4 you are saying "ouput either 40 or the sum
of these numbers"

EDIT: Sorry this comment wasn't finished. It is supposed to be...

By adding the MIN function to C4 you are saying "ouput either 40 or the sum
of these numbers, WHICHEVER IS smaller"
 
D

Diana

Okay, here is what i am trying to do, for everyday of the week, the people
put how many hours they attended.

A1:8
B1:8
C1:8
D1:8
E1:8

I want cell H1 to add those to 40 hours. Now, some people attend more than
just those 40 hours. So other cells have more hours.
F1:6
G1:6

What I need is a formula that only counts their hours and stops at 40. Is
this explanation better?
 
S

Sloth

This is easier than I thought. I think :)

In cell H1 type in this formula...
=MIN(40,SUM(A1:G1))

This will add all the cells from A1 to G1. If the sum of all of the cells
is more than 40, then H4 will output 40 instead of the sum. If F1 or G1 is
blank it will count that cell as 0. The Min function is just a clever way of
doing things; you could also do it like this...
=IF(SUM(A1:G1)<40,SUM(A1:G1),40)

This says if the sum is less than 40 output the sum, otherwise output 40.
Both options give exactly the same result. With the MIN Function you are
simply telling excell to choose the smaller of two numbers (one being 40, and
one being the sum of the list). In your example the output would be 40, in
this example the output is 39.

A1:8
B1:8
C1:8
D1:8
E1:4
F1:3
G1:0
 
Top