Stumped....This way

[dcollierjr]

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

 

[Scott Huish]

I'm not sure I understand the 2nd part of your question, but for the first part, perhaps something like this:

Excel 2010
	A	B	C	D	E
2
3					40
4
5
6	2604		5		204
Sheet1

Cell Formulas
Range	Formula
C6	=INT(A6/(12*$E$3))
E6	=MOD(A6,12*$E$3)

 

[dcollierjr]

I'm not sure I understand the 2nd part of your question, but for the first part, perhaps something like this:

Excel 2010

	A	B	C	D	E
2
3					40
4
5
6	2604		5		204

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C6 =INT(A6/(12*$E$3) E6 =MOD(A6,12*$E$3) <tbody> </tbody>

<tbody>
</tbody>

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.

 

[Scott Huish]

I'm guessing because it's just the remainder of dividing the 204 (in this example) by the 40.

Excel 2010
	A	B	C	D	E	F	G
1
2
3					40
4
5
6	2604		5		204		4
Sheet1

Cell Formulas
Range	Formula
G6	=MOD(E6,E3)

 

[dcollierjr]

I'm guessing because it's just the remainder of dividing the 204 (in this example) by the 40.

Excel 2010

	A	B	C	D	E	F	G
1
2
3					40
4
5
6	2604		5		204		4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G6 =MOD(E6,E3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.

 

[Scott Huish]

Perhaps:

Excel 2010
	A	B	C	D	E	F	G
1
2
3					40
4
5
6	2604		5		200		4
Sheet1

Cell Formulas
Range	Formula
C6	=INT(A6/(12*$E$3))
E6	=MOD(A6,12*$E$3)-G6
G6	=MOD(MOD(A6,12*$E$3),E3)

 

[dcollierjr]

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