Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula Help
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. |
#2
|
|||
|
|||
Hi Diana
Try =MIN(40,your_sum_formula) Regards Roger Govier Diana wrote: 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. |
#3
|
|||
|
|||
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. "Roger Govier" wrote: Hi Diana Try =MIN(40,your_sum_formula) Regards Roger Govier Diana wrote: 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. |
#4
|
|||
|
|||
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 Diana wrote: 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. "Roger Govier" wrote: Hi Diana Try =MIN(40,your_sum_formula) Regards Roger Govier Diana wrote: 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. |
#5
|
|||
|
|||
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. "Diana" wrote: 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. |
#6
|
|||
|
|||
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" "Sloth" wrote: 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. "Diana" wrote: 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. |
#7
|
|||
|
|||
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? "Sloth" wrote: 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" "Sloth" wrote: 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. "Diana" wrote: 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. |
#8
|
|||
|
|||
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 "Diana" wrote: 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? "Sloth" wrote: 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" "Sloth" wrote: 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. "Diana" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |