Stumped....This way

dcollierjr

New Member
Joined
Jul 23, 2018
Messages
4
Thank you for all of your topics. I skimmed over "some" of them looking for an answer for this sheet. I can and do have a sheet where I can make the formulas work using basic skills.
ie...(B6*12*$E$3)+(C6*$E$3)+D6 to give me the break-down of the desired sum. I want to do it in reverse of this order.


When a number is placed in A6, "2604" I want C6 to populate the solution in the box in this case it will be 5 because 5 *12 = 2400 It can't be a higher number because it would be too many papers if a complete layer was multiple of 12. So 6*12*40 would be 2880, more than the original total in A6.

then

Anything that is left out of the C6 total should populate into E6. Meaning as the total was less that 1 layer (12 bundles) So it can be 11 bundles to at least 1 bundle. In this case 5 bundles of the bundle size of 40 (A2) would populate to 200 papers or 5.

Then

G6 should be a number between 39 and 1, because it is less than the bundle size in A2.

A2 is 40.

What I am trying to do is for someone to give my night foreman a total and once she puts the data in the "Total Papers" box the rest is done, taking the human factor out of the picture. I hope I have explained the scenario well enough.

I have another spreadsheet needing some "guru" touch. As a self taught novice, I can only scratch my head until I start bleeding. I can go into greater detail on that sheet later. I even asked someone "friend of a friend" to help, but he never got back with me.

Thanks in advance.
Don
 
Last edited by a moderator:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
I'm not sure I understand the 2nd part of your question, but for the first part, perhaps something like this:


Excel 2010
ABCDE
2
340
4
5
626045204
Sheet1
Cell Formulas
RangeFormula
C6=INT(A6/(12*$E$3))
E6=MOD(A6,12*$E$3)
 
Upvote 0
I'm not sure I understand the 2nd part of your question, but for the first part, perhaps something like this:

Excel 2010
ABCDE

<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]4[/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: center"]6[/TD]
[TD="align: right"]2604[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]204[/TD]

</tbody>
Sheet1
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=INT(A6/(12*$E$3)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6[/TH]
[TD="align: left"]=MOD(A6,12*$E$3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Cool so far. For the formula in E6, there is a remainder of what we call odd papers. In this case 4. How can you tell G6 to account for the odd papers in E6? Anything less than E3 should populate in G6. How would you account for those papers?

THANK YOU, THANK YOU so much.
 
Upvote 0
I'm guessing because it's just the remainder of dividing the 204 (in this example) by the 40.


Excel 2010
ABCDEFG
1
2
340
4
5
6260452044
Sheet1
Cell Formulas
RangeFormula
G6=MOD(E6,E3)
 
Upvote 0
I'm guessing because it's just the remainder of dividing the 204 (in this example) by the 40.

Excel 2010
ABCDEFG

<colgroup><col style="width: 25pxpx"><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: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/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"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]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: 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: center"]6[/TD]
[TD="align: right"]2604[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]204[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/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] "]G6[/TH]
[TD="align: left"]=MOD(E6,E3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Yes Sir that is correct. Thank you.
Is there a way to make the 4 or any number between 1/39 (in this scenario) papers to go away in E6? Because whatever the odd number is (not divisible evenly) will populate in G6. If you add C5 2400 + 204 +4 =2608. 4 too many, I am sorry, I hope I am making sense. I feel like I'm stepping all over myself.
 
Upvote 0
Perhaps:


Excel 2010
ABCDEFG
1
2
340
4
5
6260452004
Sheet1
Cell Formulas
RangeFormula
C6=INT(A6/(12*$E$3))
E6=MOD(A6,12*$E$3)-G6
G6=MOD(MOD(A6,12*$E$3),E3)
 
Upvote 0
Thank you so much Mr. Scott. This works great. I appreciate your time. I have a much more complex attendance sheet with issues when you have more time. That one will cost me...lol
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top