Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
disperse amount across multiple cells without fractions
I have a spreadsheet that calculates work time by day and job number.
Someone may have multiple entries on one day to different job numbers. We want to take the total overtime for the week (anything over 40) and disperse it over all the jobs. I have done this, but it does it in fractions. Even though I show only 1 decimal, the actual number is several decimal places. 116/13. How can I divide it but make it only have 1 decimal place. Like one can be 8.5 and the other 8.6 for a total of 17.1 instead of both of them being 8.55. start time(A) Stop time(b) total time(c) OT(d) - Total OT for week(d10) Any help would be greatly appreciated. I can send sample spreadsheet if you need it. Becky |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
disperse amount across multiple cells without fractions
Here's one way.
In my example, I put in A1 the number to be divided up, say 8.92307692307692. To divide it into four parts, In B1 put: =ROUND(A1/4,1) In B2 put: =B1 In B3 put: =B1 In B4 put: =ROUND(A1,1)-SUM(B1:B3) Modify to suit the need. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
disperse amount across multiple cells without fractions
For your specific example, suppose you have 17.1 in A1 and you want to
split it into 2 cells B1 and C1. Put this in B1: =ROUND(A1/2,1) and this in C1: =A1-B1 Hope this helps. Pete On Oct 23, 10:53*pm, bkinman wrote: I have a spreadsheet that calculates work time by day and job number. * Someone may have multiple entries on one day to different job numbers. *We want to take the total overtime for the week (anything over 40) and disperse it over all the jobs. I have done this, but it does it in fractions. Even though I show only 1 decimal, the actual number is several decimal places.. * 116/13. How can I divide it but make it only have 1 decimal place. *Like one can be 8.5 and the other 8.6 for a total of 17.1 instead of both of them being 8..55. start time(A) Stop time(b) total time(c) *OT(d) - Total OT for week(d10) * Any help would be greatly appreciated. I can send sample spreadsheet if you need it. Becky |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
disperse amount across multiple cells without fractions
Hello,
From your example I am assuming that the column headings for A thru D are in Row 1, and the data start at Row 2. Furthermore, since you have mentioned that the total OT hours for the week is in D10, I am assuming that the employee job-time data will not exceed Row 9. Make sure that Cell E1 does not contain any non-zero number. It can be blank or contain zero or a column heading. Enter the following formula in E2 and drag the formula down to E9. =IF(ISBLANK(A2),"",ROUND(($D$10-SUM($E$1:E1))/COUNTA(A2:$A$9),1)) The formula will distribute the total OT hours uniformly over all the jobs. The formula will work for ANY number of rows (i.e., jobs) upon a slight modification. Modify the $D$10 in the formula to the cell address that contains the total OT hours for the week. Modify the $A$9 in the formula to reflect a larger row number(e.g., $A$20) to accommodate more rows of job data. If you find this helpful, please give a positive feed-back by clicking the Yes button. With regards, B. R. Ramachandran "bkinman" wrote: I have a spreadsheet that calculates work time by day and job number. Someone may have multiple entries on one day to different job numbers. We want to take the total overtime for the week (anything over 40) and disperse it over all the jobs. I have done this, but it does it in fractions. Even though I show only 1 decimal, the actual number is several decimal places. 116/13. How can I divide it but make it only have 1 decimal place. Like one can be 8.5 and the other 8.6 for a total of 17.1 instead of both of them being 8.55. start time(A) Stop time(b) total time(c) OT(d) - Total OT for week(d10) Any help would be greatly appreciated. I can send sample spreadsheet if you need it. Becky |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy multiple records based on criteria or total amount | Excel Discussion (Misc queries) | |||
formatting cells fractions - HELP | Excel Discussion (Misc queries) | |||
return in fractions with multiple divisors, linear | Excel Worksheet Functions | |||
CONCATENATE Cells Formated as fractions | Excel Worksheet Functions | |||
Disperse one worksheet into several... | Excel Discussion (Misc queries) |