Can't seem to avoid a circular reference

[Space Bar To Jump]

Hi all,

I'm trying to work out a a formula that avoids a circular reference.

I have a list of numbers (hours taken), which is summed to give a total number of hours, converted to the nearest half day (based on an 8 hour day). e.g. if it sums to 9 hours, this rounds up to 1.5 days; 13 hours total rounds up to 2 days, and so on.

The number of days is then rounded up to give me the number of visits required. Thus:

[TABLE="width: 200"]
<tbody>[TR]
[TD]Item 1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Total Hours[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Number of 8 hour days (rounded to 0.5)[/TD]
[TD]1.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Number of visits required (based on 8 hour days)[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


However I need to include travel time, and herein lies the problem; I can't think how to avoid a circular reference.

Say the travel time per visit is 5 hours, this gives 10 hours total travel time for the 2 visits. But... this now brings the total job hours to 19. Which rounds to 2.5 days. Which would require 3 visits. Which now means an additional 5 hours of travel time. Which brings total job hours to 24.... etc

I cannot, for the life of me, work out how to incorporate the travel time into the number-of-visits-required calculation without causing a circular reference. Perhaps there is no way to do it (although I can get to the answer manually calculating, so I'd be disappointed if Excel couldn't do the same thing).

Any help would be greatly appreciated!

Many thanks,

Jamie

 

[Eric W]

Welcome to the forum.

How about:

	A	B	C	D	E	F	G	H
Item	Travel Hours per visit	Hours left for items	Number visits required
Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=8-D2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=ROUNDUP(B6/F2,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]=SUM(B2:B4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Space Bar To Jump]

Hi Eric,

Thank you for taking the time to reply to my query - very much appreciated. You may just have saved my sanity!

Thanks again,

Jamie

 

[Eric W]

You're welcome.