Several formula errors

[oxicottin]

Hello I have this form that works to a point. What I mean is all the formulas work except on the txtScrapPercent text box its not putting the correct decimal in the spot I need It needs the decimal moved up two numbers also, when I click the clear button I get even more errors and then the form doesnt work? I attached the form...

Thanks!

 

[georgedwilkinson]

Some of the errors are caused by dividing a number by 0, you can't do that. You see, txtMasterCoilWidth is never getting set to anything so, I assume, defaults to "0". As such, your Scrap Percent calculation will always fail, unless you have entered the Width.

Try this as your formula: =([txtMasterCoilWidth]/[txtTotalWidthPerSetUp])-1

That'll take care of the inaccurate calculation. Try incorporating an Iif to check the width before doing the division.

 

[KenHigg]

Part of your problem is of the divide by zero type. I think I would write one pc of code and place it behind a 'Calculate' button that handled all of this instead of trying to manage all of the posibilities behind each control...

 

[oxicottin]

Thanks, Im just learning so Im not able to write the code behind the form "VBA" but I will try alterating the formula with the -1 and get back to Tonight.... "Quiting time at work"

Thanks,
Chad

 

[KenHigg]

Put this in txtPIW:

=IIf(nz([txtMasterCoilWidth])=0,0,[txtTotalMasterWeight]/[txtMasterCoilWidth])

And this in txtScrapPercent:

=IIf(([txtMasterCoilWidth]=0) Or ([txtTotalWidthPerSetUp]=0),0,100-(([txtTotalWidthPerSetUp]/[txtMasterCoilWidth])*100))

 

[oxicottin]

Ken, that works great!!! It took my errors out but Im still getting the decimal in the wrong place though? Example:

1) txtCoilWeight1 = 700
2) txtMasterCoilWidth = 36.8
3) cboSlitWidth1 = .75
4) txtNumberOfCuts1 = 8

Now my txtScrapPercent box will give a total of 8369.57% which is right but I need the decimal here 83.69%. How do I fix this?

Thanks,
Chad

 

[georgedwilkinson]

Chad, read my post above. It correctly calculates the scrap %.

 

[oxicottin]

Sorry George I didnt recieve an email that someone posted to my question! Im not sure how to incorperate the formula you gave me in your post. The formula Ken gave me works but the decimals are wrong. Im also having trouble clearing the form. It clears everything but after clearing it gives errors in my control boxes and nothing will work after that. I would like to clear all boxes on the form if I could without errors. I uploaded an updated version of the form.

Thanks,
Chad

 

[georgedwilkinson]

Put this in txtScrapPercent:

=IIf(([txtMasterCoilWidth]=0) Or ([txtTotalWidthPerSetUp]=0),0,([txtTotalWidthPerSetUp]/[txtMasterCoilWidth])-1)

I tested it out on your form and it works, giving a correct percentage of scrap under various conditions.

 

[oxicottin]

George, it works! but its giving me a negative? Im just going by the excel sheet my boss gave me mabe its incorect? I will attach it. Also how would I clear the form without getting all the errors?

Thanks,
Chad

 

[georgedwilkinson]

That's because I typed it in wrong. Put this in txtScrapPercent:

=IIf(([txtMasterCoilWidth]=0) Or ([txtTotalWidthPerSetUp]=0),0,([txtMasterCoilWidth]/[txtTotalWidthPerSetUp])-1)

 

[oxicottin]

George, It now gives me 513.33% with the:

) txtCoilWeight1 = 700
2) txtMasterCoilWidth = 36.8
3) cboSlitWidth1 = .75
4) txtNumberOfCuts1 = 8

Thanks,
Chad

 

[georgedwilkinson]

Chad,

It has been a while since I looked at your DB and I can't find a copy. I don't have time right now to re-figure it out but here is how you calculate waste:
Divide the amount of stock used by the amount of stock you started with and subtract 1 from that value. You may have to multiply the entire value by -1 to get the sign right (I assumed that a loss was negative). If that is true, you get the same answer by subtracting the original value from 1.

So: "(stock used / original stock amount) - 1" should give you the correct answer. Or you can use "1 - (stock used / original stock amount)". I tried it out with values that I could calculate in my head and it gave the right answers. For instance, I got the total slit width (I think) equal to 7.5, got the master coil width (I think) equal to 10 and the calculation showed a 25% waste, exactly what I expected. When I increased the master coil width to 15, the waste was 50%, again, exactly what I expected. In my mind, both of those answers are correct. If you need the exact formula I used, please see my first post in this thread as my mind was freshest at that point and totally dedicated to your problem. Right now, I'm working on some other problems that have me distracted.

I guess the real answer to your question is the answer you would give to someone who asked you how you calculate waste with a manual method. The answer is about the same here.

 

[oxicottin]

Ok George I will try to figure it out! Thanks for your help....

 

[oxicottin]

This is what I have so far. I am still having trouble with getting rid of a negative number for my scrap percentage. I am also geting an error if I try to clear the cboSlitWidth1,2,3,4,5 by using the code below in my clear button.

    Forms!frmslitterSetup.cboSlitWidth1 = ""
    Forms!frmslitterSetup.cboSlitWidth2 = ""
    Forms!frmslitterSetup.cboSlitWidth3 = ""
    Forms!frmslitterSetup.cboSlitWidth4 = ""
    Forms!frmslitterSetup.cboSlitWidth5 = ""

Thanks!