disperse amount across multiple cells without fractions

B

bkinman

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
 
Z

zvkmpw

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.
 
P

Pete_UK

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
 
B

B. R.Ramachandran

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top