Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total under certain number?
Hi,
I have a spreadsheet with data from two different units. What I would like to do is to be able to total, for each unit, figures under a certain number and totals for above the same number. Example: Unit Hours A 2 A 2 B 3 A 4 B 1 B 1 and so on..... Totals for: Unit Hours Under 5 hours A 4 B 4 Over 5 hours A 4 B 1 Hope that helps! -- Geo |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total under certain number?
for under 5 hours in D1 and D2 enter:
=MIN(SUM(IF(A2:A7="A",B2:B7)),4) =MIN(SUM(IF(A2:A7="B",B2:B7)),4) for over 5 hours in D3 and D4 enter: =SUM(IF(A2:A7="A",B2:B7))-D1 =SUM(IF(A2:A7="B",B2:B7))-D2 All the above are array formulas installed by CNTRL-SHIFT-ENTER -- Gary''s Student - gsnu200750 "Geo" wrote: Hi, I have a spreadsheet with data from two different units. What I would like to do is to be able to total, for each unit, figures under a certain number and totals for above the same number. Example: Unit Hours A 2 A 2 B 3 A 4 B 1 B 1 and so on..... Totals for: Unit Hours Under 5 hours A 4 B 4 Over 5 hours A 4 B 1 Hope that helps! -- Geo |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total under certain number?
Thanks gary,
What changes to the formula if I dont want to enter it as a array formulas? -- Geo "Gary''s Student" wrote: for under 5 hours in D1 and D2 enter: =MIN(SUM(IF(A2:A7="A",B2:B7)),4) =MIN(SUM(IF(A2:A7="B",B2:B7)),4) for over 5 hours in D3 and D4 enter: =SUM(IF(A2:A7="A",B2:B7))-D1 =SUM(IF(A2:A7="B",B2:B7))-D2 All the above are array formulas installed by CNTRL-SHIFT-ENTER -- Gary''s Student - gsnu200750 "Geo" wrote: Hi, I have a spreadsheet with data from two different units. What I would like to do is to be able to total, for each unit, figures under a certain number and totals for above the same number. Example: Unit Hours A 2 A 2 B 3 A 4 B 1 B 1 and so on..... Totals for: Unit Hours Under 5 hours A 4 B 4 Over 5 hours A 4 B 1 Hope that helps! -- Geo |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total under certain number?
We can use SUMPRODUCT(). In D1 thru D4:
=MIN(SUMPRODUCT(--(A2:A7="A"),B2:B7),4) =MIN(SUMPRODUCT(--(A2:A7="B"),B2:B7),4) =SUMPRODUCT(--(A2:A7="A"),B2:B7)-D1 =SUMPRODUCT(--(A2:A7="B"),B2:B7)-D2 These are NOT array functions. -- Gary''s Student - gsnu200750 "Geo" wrote: Thanks gary, What changes to the formula if I dont want to enter it as a array formulas? -- Geo "Gary''s Student" wrote: for under 5 hours in D1 and D2 enter: =MIN(SUM(IF(A2:A7="A",B2:B7)),4) =MIN(SUM(IF(A2:A7="B",B2:B7)),4) for over 5 hours in D3 and D4 enter: =SUM(IF(A2:A7="A",B2:B7))-D1 =SUM(IF(A2:A7="B",B2:B7))-D2 All the above are array formulas installed by CNTRL-SHIFT-ENTER -- Gary''s Student - gsnu200750 "Geo" wrote: Hi, I have a spreadsheet with data from two different units. What I would like to do is to be able to total, for each unit, figures under a certain number and totals for above the same number. Example: Unit Hours A 2 A 2 B 3 A 4 B 1 B 1 and so on..... Totals for: Unit Hours Under 5 hours A 4 B 4 Over 5 hours A 4 B 1 Hope that helps! -- Geo |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total under certain number?
Thanks very Gary the formulas work.
I was hoping to be able to figure out how to use these formulas, but Im getting confused. The table I gave you was a very simple example of the one Im using. What Im trying to do is keep a running total of the combined units up to the point the hours reaches 40:00 hours then the total continue on the cell below. I then extract in percentage usage of hours for both units under 40:00 hours and over as well. But its giving me a head ache!! Any ideas? -- Geo "Gary''s Student" wrote: We can use SUMPRODUCT(). In D1 thru D4: =MIN(SUMPRODUCT(--(A2:A7="A"),B2:B7),4) =MIN(SUMPRODUCT(--(A2:A7="B"),B2:B7),4) =SUMPRODUCT(--(A2:A7="A"),B2:B7)-D1 =SUMPRODUCT(--(A2:A7="B"),B2:B7)-D2 These are NOT array functions. -- Gary''s Student - gsnu200750 "Geo" wrote: Thanks gary, What changes to the formula if I dont want to enter it as a array formulas? -- Geo "Gary''s Student" wrote: for under 5 hours in D1 and D2 enter: =MIN(SUM(IF(A2:A7="A",B2:B7)),4) =MIN(SUM(IF(A2:A7="B",B2:B7)),4) for over 5 hours in D3 and D4 enter: =SUM(IF(A2:A7="A",B2:B7))-D1 =SUM(IF(A2:A7="B",B2:B7))-D2 All the above are array formulas installed by CNTRL-SHIFT-ENTER -- Gary''s Student - gsnu200750 "Geo" wrote: Hi, I have a spreadsheet with data from two different units. What I would like to do is to be able to total, for each unit, figures under a certain number and totals for above the same number. Example: Unit Hours A 2 A 2 B 3 A 4 B 1 B 1 and so on..... Totals for: Unit Hours Under 5 hours A 4 B 4 Over 5 hours A 4 B 1 Hope that helps! -- Geo |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total under certain number?
I am sure we can come up with a solution. I just need a more complete example.
-- Gary''s Student - gsnu200750 "Geo" wrote: Thanks very Gary the formulas work. I was hoping to be able to figure out how to use these formulas, but Im getting confused. The table I gave you was a very simple example of the one Im using. What Im trying to do is keep a running total of the combined units up to the point the hours reaches 40:00 hours then the total continue on the cell below. I then extract in percentage usage of hours for both units under 40:00 hours and over as well. But its giving me a head ache!! Any ideas? -- Geo "Gary''s Student" wrote: We can use SUMPRODUCT(). In D1 thru D4: =MIN(SUMPRODUCT(--(A2:A7="A"),B2:B7),4) =MIN(SUMPRODUCT(--(A2:A7="B"),B2:B7),4) =SUMPRODUCT(--(A2:A7="A"),B2:B7)-D1 =SUMPRODUCT(--(A2:A7="B"),B2:B7)-D2 These are NOT array functions. -- Gary''s Student - gsnu200750 "Geo" wrote: Thanks gary, What changes to the formula if I dont want to enter it as a array formulas? -- Geo "Gary''s Student" wrote: for under 5 hours in D1 and D2 enter: =MIN(SUM(IF(A2:A7="A",B2:B7)),4) =MIN(SUM(IF(A2:A7="B",B2:B7)),4) for over 5 hours in D3 and D4 enter: =SUM(IF(A2:A7="A",B2:B7))-D1 =SUM(IF(A2:A7="B",B2:B7))-D2 All the above are array formulas installed by CNTRL-SHIFT-ENTER -- Gary''s Student - gsnu200750 "Geo" wrote: Hi, I have a spreadsheet with data from two different units. What I would like to do is to be able to total, for each unit, figures under a certain number and totals for above the same number. Example: Unit Hours A 2 A 2 B 3 A 4 B 1 B 1 and so on..... Totals for: Unit Hours Under 5 hours A 4 B 4 Over 5 hours A 4 B 1 Hope that helps! -- Geo |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total under certain number?
Before I start, thanks for your patience.
Right! sheet(MAIN) has the data that is entered as follows: columns A B C D Date Unit No Time 12/9/07 Med 20 0:30 12/9/07 Pol 10 0:45 13/9/07 Pol 11 0:25 14/9/07 Med 21 0:35 and so on Rows start at 11 and go to 20000 Sheet2 Cell H6 = Total Med hours Cell H9 = Total Med hours that fall under a combined 40 hours of both units. Cell H11= Total Med hours that fall above a combined 40 hours of both units. Cell J9 = Hours used by Med as a percentage under 40 hours Cell J11 = Hours used by Med as a percentage under 40 hours Cell N6 = Total Pol hours Cell N9 = Total Pol hours that fall under a combined 40 hours of both units. Cell N11= Total Pol hours that fall above a combined 40 hours of both units. Cell P9 = Hours used by Pol as a percentage under 40 hours Cell P11 = Hours used by Pol as a percentage over 40 hours Cell H3 = reference time (40 hours) I'v tried to make informative but simple. I hope this helps. The idea is to find out the hours that fall under the figure in cell H3 when the data is inserted by both units and also over the figure in cell H3. -- Geo "Gary''s Student" wrote: I am sure we can come up with a solution. I just need a more complete example. -- Gary''s Student - gsnu200750 "Geo" wrote: Thanks very Gary the formulas work. I was hoping to be able to figure out how to use these formulas, but Im getting confused. The table I gave you was a very simple example of the one Im using. What Im trying to do is keep a running total of the combined units up to the point the hours reaches 40:00 hours then the total continue on the cell below. I then extract in percentage usage of hours for both units under 40:00 hours and over as well. But its giving me a head ache!! Any ideas? -- Geo "Gary''s Student" wrote: We can use SUMPRODUCT(). In D1 thru D4: =MIN(SUMPRODUCT(--(A2:A7="A"),B2:B7),4) =MIN(SUMPRODUCT(--(A2:A7="B"),B2:B7),4) =SUMPRODUCT(--(A2:A7="A"),B2:B7)-D1 =SUMPRODUCT(--(A2:A7="B"),B2:B7)-D2 These are NOT array functions. -- Gary''s Student - gsnu200750 "Geo" wrote: Thanks gary, What changes to the formula if I dont want to enter it as a array formulas? -- Geo "Gary''s Student" wrote: for under 5 hours in D1 and D2 enter: =MIN(SUM(IF(A2:A7="A",B2:B7)),4) =MIN(SUM(IF(A2:A7="B",B2:B7)),4) for over 5 hours in D3 and D4 enter: =SUM(IF(A2:A7="A",B2:B7))-D1 =SUM(IF(A2:A7="B",B2:B7))-D2 All the above are array formulas installed by CNTRL-SHIFT-ENTER -- Gary''s Student - gsnu200750 "Geo" wrote: Hi, I have a spreadsheet with data from two different units. What I would like to do is to be able to total, for each unit, figures under a certain number and totals for above the same number. Example: Unit Hours A 2 A 2 B 3 A 4 B 1 B 1 and so on..... Totals for: Unit Hours Under 5 hours A 4 B 4 Over 5 hours A 4 B 1 Hope that helps! -- Geo |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total under certain number?
Check back later today..
-- Gary''s Student - gsnu200750 "Geo" wrote: Before I start, thanks for your patience. Right! sheet(MAIN) has the data that is entered as follows: columns A B C D Date Unit No Time 12/9/07 Med 20 0:30 12/9/07 Pol 10 0:45 13/9/07 Pol 11 0:25 14/9/07 Med 21 0:35 and so on Rows start at 11 and go to 20000 Sheet2 Cell H6 = Total Med hours Cell H9 = Total Med hours that fall under a combined 40 hours of both units. Cell H11= Total Med hours that fall above a combined 40 hours of both units. Cell J9 = Hours used by Med as a percentage under 40 hours Cell J11 = Hours used by Med as a percentage under 40 hours Cell N6 = Total Pol hours Cell N9 = Total Pol hours that fall under a combined 40 hours of both units. Cell N11= Total Pol hours that fall above a combined 40 hours of both units. Cell P9 = Hours used by Pol as a percentage under 40 hours Cell P11 = Hours used by Pol as a percentage over 40 hours Cell H3 = reference time (40 hours) I'v tried to make informative but simple. I hope this helps. The idea is to find out the hours that fall under the figure in cell H3 when the data is inserted by both units and also over the figure in cell H3. -- Geo "Gary''s Student" wrote: I am sure we can come up with a solution. I just need a more complete example. -- Gary''s Student - gsnu200750 "Geo" wrote: Thanks very Gary the formulas work. I was hoping to be able to figure out how to use these formulas, but Im getting confused. The table I gave you was a very simple example of the one Im using. What Im trying to do is keep a running total of the combined units up to the point the hours reaches 40:00 hours then the total continue on the cell below. I then extract in percentage usage of hours for both units under 40:00 hours and over as well. But its giving me a head ache!! Any ideas? -- Geo "Gary''s Student" wrote: We can use SUMPRODUCT(). In D1 thru D4: =MIN(SUMPRODUCT(--(A2:A7="A"),B2:B7),4) =MIN(SUMPRODUCT(--(A2:A7="B"),B2:B7),4) =SUMPRODUCT(--(A2:A7="A"),B2:B7)-D1 =SUMPRODUCT(--(A2:A7="B"),B2:B7)-D2 These are NOT array functions. -- Gary''s Student - gsnu200750 "Geo" wrote: Thanks gary, What changes to the formula if I dont want to enter it as a array formulas? -- Geo "Gary''s Student" wrote: for under 5 hours in D1 and D2 enter: =MIN(SUM(IF(A2:A7="A",B2:B7)),4) =MIN(SUM(IF(A2:A7="B",B2:B7)),4) for over 5 hours in D3 and D4 enter: =SUM(IF(A2:A7="A",B2:B7))-D1 =SUM(IF(A2:A7="B",B2:B7))-D2 All the above are array formulas installed by CNTRL-SHIFT-ENTER -- Gary''s Student - gsnu200750 "Geo" wrote: Hi, I have a spreadsheet with data from two different units. What I would like to do is to be able to total, for each unit, figures under a certain number and totals for above the same number. Example: Unit Hours A 2 A 2 B 3 A 4 B 1 B 1 and so on..... Totals for: Unit Hours Under 5 hours A 4 B 4 Over 5 hours A 4 B 1 Hope that helps! -- Geo |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total under certain number?
Thanks.
To complete the picture. Basically it`s to with flying hours budgets between Medical and Police. Medical contributes 30% of the costs and the Police the rest up to 40 hours per month and 25% over a combined 40 hours. What I'm trying to do is to know what hours and what percentage each use for the first 40 hours and then over that. The trouble is that not all the hours used by Medical fall under 40 hours, some will fall over that figure. You might have a situation when there is no Medical flights under 40 hours and all of it over. So they should only pay 25% of the running costs. I have manage to sort some formulas. Using the same cell as my last post: Cells: H3 = the reference figure (40) H9 = =MIN((H3),H7) Total Med under H11 = =MAX(H7-(H3),0) Total Med over J9 = =H9/(C18) Percentage over 40 (note C18 is the total of both units) J11 = =H11/(C18) Percentage under 40 H7 = =C6 Total Med hours Polioce are the same but cells start with "N" for the hours and "P" for the percentages I hope that gives you more of an idea. -- Geo "Gary''s Student" wrote: Check back later today.. -- Gary''s Student - gsnu200750 "Geo" wrote: Before I start, thanks for your patience. Right! sheet(MAIN) has the data that is entered as follows: columns A B C D Date Unit No Time 12/9/07 Med 20 0:30 12/9/07 Pol 10 0:45 13/9/07 Pol 11 0:25 14/9/07 Med 21 0:35 and so on Rows start at 11 and go to 20000 Sheet2 Cell H6 = Total Med hours Cell H9 = Total Med hours that fall under a combined 40 hours of both units. Cell H11= Total Med hours that fall above a combined 40 hours of both units. Cell J9 = Hours used by Med as a percentage under 40 hours Cell J11 = Hours used by Med as a percentage under 40 hours Cell N6 = Total Pol hours Cell N9 = Total Pol hours that fall under a combined 40 hours of both units. Cell N11= Total Pol hours that fall above a combined 40 hours of both units. Cell P9 = Hours used by Pol as a percentage under 40 hours Cell P11 = Hours used by Pol as a percentage over 40 hours Cell H3 = reference time (40 hours) I'v tried to make informative but simple. I hope this helps. The idea is to find out the hours that fall under the figure in cell H3 when the data is inserted by both units and also over the figure in cell H3. -- Geo "Gary''s Student" wrote: I am sure we can come up with a solution. I just need a more complete example. -- Gary''s Student - gsnu200750 "Geo" wrote: Thanks very Gary the formulas work. I was hoping to be able to figure out how to use these formulas, but Im getting confused. The table I gave you was a very simple example of the one Im using. What Im trying to do is keep a running total of the combined units up to the point the hours reaches 40:00 hours then the total continue on the cell below. I then extract in percentage usage of hours for both units under 40:00 hours and over as well. But its giving me a head ache!! Any ideas? -- Geo "Gary''s Student" wrote: We can use SUMPRODUCT(). In D1 thru D4: =MIN(SUMPRODUCT(--(A2:A7="A"),B2:B7),4) =MIN(SUMPRODUCT(--(A2:A7="B"),B2:B7),4) =SUMPRODUCT(--(A2:A7="A"),B2:B7)-D1 =SUMPRODUCT(--(A2:A7="B"),B2:B7)-D2 These are NOT array functions. -- Gary''s Student - gsnu200750 "Geo" wrote: Thanks gary, What changes to the formula if I dont want to enter it as a array formulas? -- Geo "Gary''s Student" wrote: for under 5 hours in D1 and D2 enter: =MIN(SUM(IF(A2:A7="A",B2:B7)),4) =MIN(SUM(IF(A2:A7="B",B2:B7)),4) for over 5 hours in D3 and D4 enter: =SUM(IF(A2:A7="A",B2:B7))-D1 =SUM(IF(A2:A7="B",B2:B7))-D2 All the above are array formulas installed by CNTRL-SHIFT-ENTER -- Gary''s Student - gsnu200750 "Geo" wrote: Hi, I have a spreadsheet with data from two different units. What I would like to do is to be able to total, for each unit, figures under a certain number and totals for above the same number. Example: Unit Hours A 2 A 2 B 3 A 4 B 1 B 1 and so on..... Totals for: Unit Hours Under 5 hours A 4 B 4 Over 5 hours A 4 B 1 Hope that helps! -- Geo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Total number of months. | Excel Worksheet Functions | |||
Can I total only a % of a number (cell) base on total of all cel | Excel Discussion (Misc queries) | |||
How do I find the total number of the same number/letter in a row | Excel Discussion (Misc queries) | |||
a number as a percentage out of a total | Excel Worksheet Functions | |||
Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions |