# reverse distribution of excel totals

#### shawnp

##### New Member
I have an Excel problem, that I need help with.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I have the following table. I require 25 man days of work to be done at a given location. I calculate that I will need to make 10 visits to that location based on the frequency of my visits there. Therefore, known information is: 10 visits of different types to be carried out in 25 man-days)<o></o>
<o> </o>
<TABLE class=MsoNormalTable style="MARGIN-LEFT: -1.15pt; WIDTH: 214.35pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=286 border=0><TBODY><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 bgColor=#ccffcc height=16>
Visit type<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 bgColor=#ccffcc height=16>
Visits<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 bgColor=#ccffcc height=16>
Man days<o></o>
</TD></TR><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>1-day<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>
1<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>
1<o></o>
</TD></TR><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>2-day<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>
3<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>
6<o></o>
</TD></TR><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>3-day<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>
6<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>
18<o></o>
</TD></TR><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>4-day<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>
<o> </o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>
<o> </o>
</TD></TR><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>5-day<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>
<o> </o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 height=16>
<o> </o>
</TD></TR><TR style="HEIGHT: 11.75pt" height=16><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 bgColor=#ccffcc height=16>Total<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 bgColor=#ccffcc height=16>
10<o></o>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: medium none; PADDING-LEFT: 5.4pt; BACKGROUND: #ccffcc; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; WIDTH: 71.45pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.75pt" vAlign=bottom noWrap width=95 bgColor=#ccffcc height=16>
25<o></o>
</TD></TR></TBODY></TABLE>
<o> </o>
Is it possible to devise a macro / Excel formula that automatically distributes the visit type and man days as shown, so that totals match i.e 25 man days work out to six 3-days visits + three 6-day visits + one 1-day visit? I just want to put 25 and 10 and get the visit distribution automatically.<o></o>
<o> </o>

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### jbeaucaire

##### Well-known Member
Not without some included explanation the sheet can use giving the logic that is to be used to determine how many of each type of day to generate.

#### shawnp

##### New Member
Not without some included explanation the sheet can use giving the logic that is to be used to determine how many of each type of day to generate.

Actually, its the logic I need to generate. There could be more than 1 correct combination of visits, but the type of visits must total to 10 and the mandays must total to 25. The focus is to have a good visit distribution rather than just one type of visits (eg. all 1-day visits only)

Replies
1
Views
133
Replies
3
Views
921
Replies
0
Views
808
Replies
7
Views
2K
Replies
1
Views
1K

1,191,200
Messages
5,985,236
Members
439,953
Latest member
suchitha

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back