Calculating amount of Jam labels - negative values and roundup/down - excell/GSheets

[tom_mc]

Hi team,

I'm struggling with the little excel knowledge I have to calculate the number of boxes of labels I need based on amount produced. c5 amount produced. (any) a8 number of labels in a box. (1,800) b8 cost per box (any) d8 number of labels left over if less than amount produced. Otherwise display 0. I am using =IF(A8-C5<=0, 0, A8-C5) but if I c5>a8 I get a negative value ?

e8 = number of box(s) needed. Only display if greater than 0.0000 and round up to the next whole integer e.g. 1 box. Otherwise display 0. I am using =IF(D8=0,, (C5/A8)) but need to merge this with something like =IF(e8 > 0, ROUNDDOWN(e8, 0), ROUNDUP(e8, 1))

T

[Pete_UK]

Try this in E8:

=IFERROR(ROUNDUP(C5/A8,0),"")

Hope this helps.

Pete

[Sandtree]

You have referred to a C2 in your formula but not confirmed what C2 is?

The first part of the formula also doesnt make any sense as the number of labels in a box is irrelevant to if you need to order more labels or not.

Attached is what I think what you are trying to achieve is, if not then please attach your own example.

[tom_mc]

Thanks Pete,

This works but how can I tweak it so that when c5 is either less than or equal to a8 rounddown to 0 ?

[tom_mc]

Thanks Sandtree. My appologies for the type. c2 should say c5

[Pete_UK]

… how can I tweak it so that when c5 is either less than or equal to a8 rounddown to 0 ? ...

I'm not sure why you would want it to show zero if those two cells are equal, or indeed if C5 if less than A8. If there are 1800 labels in a box, and if you make 1800 jars of jam, then you will need one box of labels. If you make 1801 jars of jam, or more, then you would need 2 boxes of labels. If you make less than 1800 jars of jam (e.g. 1500), then you will still need 1 box of labels (with 300 left over in this example, but from what you said in your first post, you will have a formula in D8 to give you this amount).

The formula that I gave you correctly returns the number of (full) boxes of labels that you will need.

Pete

[tom_mc]

Thanks Pete, Thinking this through I found a better, but maybe a long winded way due to my inexperienced solution. into d8 I put =IF(C5<A8, A8-C5, IF(C5>A8, A8-C5,0)) and in e8 =IF(C5>A8/2,(ROUNDUP(C5/A8)),0) much more accurate. Your feedback prompted me to think, what if price of the 1st box was the cost of a company car, when in fact none was needed just yet!

[FDibbins]

You have referred to a C2 in your formula but not confirmed what C2 is?

The first part of the formula also doesnt make any sense as the number of labels in a box is irrelevant to if you need to order more labels or not.

Attached is what I think what you are trying to achieve is, if not then please attach your own example.

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!