Formula help

[Chad]

Hello, I have the formula below and it gives me the correct number and the
decilal is in the correct spot but im getting a negative number when it
shouldnt be negative. any sugestions? thanks!

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

 

[Wayne-I-M]

You have not given any details of what you are trying to get with this but
with this formula- - - - - (I don't understand the - 1 )

What are you trying to get ?
Can you give examples of the data and the results you expect


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

txtMasterCoilWidth]=0
txtTotalWidthPerSetUp=0
Answer = 0

txtMasterCoilWidth]=4
txtTotalWidthPerSetUp=2
Answer = 2

Or (with the -1)

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

txtMasterCoilWidth]=0
txtTotalWidthPerSetUp=0
Answer = 0

txtMasterCoilWidth]=4
txtTotalWidthPerSetUp=2
Answer = 1

txtMasterCoilWidth]=4
txtTotalWidthPerSetUp=0
Answer = (0-1) = -1

It seems that your forumla is working (as it should).

 

[Wayne-I-M]

Sorry there was an error in the last answer

txtMasterCoilWidth]=4
txtTotalWidthPerSetUp=0
Answer = 0

BUt the formula still is working (it was my typeing the was wrong)

 

[Chad]

Yes im getting the correct answer but it has a - in front of it? I posed in
access forums and someone helped me get the formula I have but no further and
I still needed assistance. Here is where I posted. Thanks!

http://www.access-programmers.co.uk/forums/showthread.php?p=705472#post705472

Sorry there was an error in the last answer

txtMasterCoilWidth]=4
txtTotalWidthPerSetUp=0
Answer = 0

BUt the formula still is working (it was my typeing the was wrong)



--
Wayne
Manchester, England.

Hello, I have the formula below and it gives me the correct number and the
decilal is in the correct spot but im getting a negative number when it
shouldnt be negative. any sugestions? thanks!

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

 

[Wayne-I-M]

In the other website you have extra "stuff".

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

Sorry don't have time at the moment (really stressed). I hope someone else
will jump in with the answer (I have not opened your zipped DB yet).

If not I will do it later today - hope thats OK


--
Wayne
Manchester, England.

Yes im getting the correct answer but it has a - in front of it? I posed in
access forums and someone helped me get the formula I have but no further and
I still needed assistance. Here is where I posted. Thanks!

http://www.access-programmers.co.uk/forums/showthread.php?p=705472#post705472

Sorry there was an error in the last answer

txtMasterCoilWidth]=4
txtTotalWidthPerSetUp=0
Answer = 0

BUt the formula still is working (it was my typeing the was wrong)



--
Wayne
Manchester, England.

Hello, I have the formula below and it gives me the correct number and the
decilal is in the correct spot but im getting a negative number when it
shouldnt be negative. any sugestions? thanks!

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

 

[Chad]

Sure Wayne! Thats fine...... I will check back tonight! I posted an updated
version with what I had so far if you wanted to DL it. I just cant figure
out the frmSlitterSetup!

 

[John Spencer]

If the answer is correct, other than for the negative sign, then use the abs
function to strip off the negative.

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

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Wayne-I-M]

Sorry about ths - don't oftern say this. I'm lost with the whole thing.

Sorry I can't be of any help.

I opened your DB and input various numbers into the boxes and to say I was
baffled would be an understatement. I underfully full the formulas and what
they do and when and how. But what the results should be ?? I tried
inputting the numbers you gave in the other forum and the answer (on your
orginal post) were nothing like what you said they should be (maybe the
formulas have been changed)

Maybe someone with more expereince of manufacturing proceeses will jump in.





--
Wayne
Manchester, England.

If the answer is correct, other than for the negative sign, then use the abs
function to strip off the negative.

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

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Sure Wayne! Thats fine...... I will check back tonight! I posted an updated
version with what I had so far if you wanted to DL it. I just cant figure
out the frmSlitterSetup!

 

[Chad]

John that takes away the negitive which is great but I need the negative to
tell me im trying to get too many cuts of steel from one master coil.
Origionaly the formula started out as this =100-((D32/J9)*100) for a percent
which was taken from a excel workbook. I had posted the workbook named book1
in the forum mentioned above with wayne.

Thanks,
Chad

If the answer is correct, other than for the negative sign, then use the abs
function to strip off the negative.

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

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Sure Wayne! Thats fine...... I will check back tonight! I posted an updated
version with what I had so far if you wanted to DL it. I just cant figure
out the frmSlitterSetup!

 

[John Spencer]

That LOOKS fairly straightforward.

IIF([J9]=0 or [D32]=0,Null,100-([D32]/[J9])*100)

Replace J9 and D32 with the names of your fields.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John that takes away the negitive which is great but I need the negative to
tell me im trying to get too many cuts of steel from one master coil.
Origionaly the formula started out as this =100-((D32/J9)*100) for a percent
which was taken from a excel workbook. I had posted the workbook named book1
in the forum mentioned above with wayne.

Thanks,
Chad

If the answer is correct, other than for the negative sign, then use the abs
function to strip off the negative.

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

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Sure Wayne! Thats fine...... I will check back tonight! I posted an updated
version with what I had so far if you wanted to DL it. I just cant figure
out the frmSlitterSetup!



:

In the other website you have extra "stuff".

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

Sorry don't have time at the moment (really stressed). I hope someone else
will jump in with the answer (I have not opened your zipped DB yet).

If not I will do it later today - hope thats OK

 

[Chad]

John, I have tried the formula and it gives me a #Name? error and doesnt
change from that? Thanks!

That LOOKS fairly straightforward.

IIF([J9]=0 or [D32]=0,Null,100-([D32]/[J9])*100)

Replace J9 and D32 with the names of your fields.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John that takes away the negitive which is great but I need the negative to
tell me im trying to get too many cuts of steel from one master coil.
Origionaly the formula started out as this =100-((D32/J9)*100) for a percent
which was taken from a excel workbook. I had posted the workbook named book1
in the forum mentioned above with wayne.

Thanks,
Chad

If the answer is correct, other than for the negative sign, then use the abs
function to strip off the negative.

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

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Chad wrote:
Sure Wayne! Thats fine...... I will check back tonight! I posted an updated
version with what I had so far if you wanted to DL it. I just cant figure
out the frmSlitterSetup!



:

In the other website you have extra "stuff".

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

Sorry don't have time at the moment (really stressed). I hope someone else
will jump in with the answer (I have not opened your zipped DB yet).

If not I will do it later today - hope thats OK

 

[John Spencer]

It helps if you post what you tried.

If you are doing this on a control on a form or report did you include the =
sign in front of the expression?

Are the fields available in the underlying query?

Is the control name different from the field names?

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John, I have tried the formula and it gives me a #Name? error and doesnt
change from that? Thanks!

That LOOKS fairly straightforward.

IIF([J9]=0 or [D32]=0,Null,100-([D32]/[J9])*100)

Replace J9 and D32 with the names of your fields.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John that takes away the negitive which is great but I need the negative to
tell me im trying to get too many cuts of steel from one master coil.
Origionaly the formula started out as this =100-((D32/J9)*100) for a percent
which was taken from a excel workbook. I had posted the workbook named book1
in the forum mentioned above with wayne.

Thanks,
Chad


:

If the answer is correct, other than for the negative sign, then use the abs
function to strip off the negative.

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

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Chad wrote:
Sure Wayne! Thats fine...... I will check back tonight! I posted an updated
version with what I had so far if you wanted to DL it. I just cant figure
out the frmSlitterSetup!



:

In the other website you have extra "stuff".

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

Sorry don't have time at the moment (really stressed). I hope someone else
will jump in with the answer (I have not opened your zipped DB yet).

If not I will do it later today - hope thats OK

 

[Chad]

Ok John im going to start from the begining. Everything on this form has the
control with a formula. I am not saving any data what so ever on this form.
Ok, I have a row of text box's named txtCoilWeight1, txtCoilWeight2,
txtCoilWeight3, txtCoilWeight4, txtCoilWeight5 and lets say I put the number
700 in txtCoilWeight1. Now I have a text box named txtTotalMasterWeight which
gives me the total weight of all the boxes mentioned above. That formula in
its control source is
=NZ([txtCoilWeight1])+NZ([txtCoilWeight2])+NZ([txtCoilWeight3])+NZ([txtCoilWeight4])+NZ([txtCoilWeight5])
and it gives the total of 700 since I only entered one number in one of the
text boxes.

Next, I have a row of combo box's named cboSlitWidth1, cboSlitWidth2,
cboSlitWidth3, cboSlitWidth4, cboSlitWidth5 with no formul for thier control
source. Lets say I entered .75 in cboSlitWidth1.

Next, I have a row of test boxes named txtNumberOfCuts1, txtNumberOfCuts2,
txtNumberOfCuts3, txtNumberOfCuts4, txtNumberOfCuts5 and lets say I put the
number 8 in txtNumberOfCuts1. Now I have a text box named txtTotalCuts which
gives me the total number of cuts of all the boxes mentioned above. That
formula in its control source is
=NZ([txtNumberOfCuts1])+NZ([txtNumberOfCuts2])+NZ([txtNumberOfCuts3])+NZ([txtNumberOfCuts4])+NZ([txtNumberOfCuts5])
and it gives the total sum of 8 since I only entered one number in one of the
text boxes.

Next I have a row of text boxes named txtTotalWidthPerSize1,
txtTotalWidthPerSize2, txtTotalWidthPerSize3, txtTotalWidthPerSize4,
txtTotalWidthPerSize5 and each ones control source has the formula
=[cboSlitWidth1]*[txtNumberOfCuts1] which in txtTotalWidthPerSize1 it would
have given automaticly given me the number 6. Now at the bottom of that row I
have a text box named txtTotalWidthPerSetUp which gives me a sum of the
mentioned boxes above and in its contol source I have the formula
=NZ([txtTotalWidthPerSize1])+NZ([txtTotalWidthPerSize2])+NZ([txtTotalWidthPerSize3])+NZ([txtTotalWidthPerSize4])+NZ([txtTotalWidthPerSize5])
and it gives the total sum of 6.

Next I have a text box named txtMasterCoilWidth where you enter a number
lets say 36.8 and it has no formula in its control source.

Next I have a combo box named cboGauge where you choose a steel guage. Lets
say I chose .010.

Next I have a text box named txtPIW and in its control source I have the
formula
=IIf(nz([txtMasterCoilWidth])=0,0,[txtTotalMasterWeight]/[txtMasterCoilWidth]) it would have automaticly given me the number 19.02.

Next I have a row of text boxes named txtTotalWeightPerSize1,
txtTotalWeightPerSize2, txtTotalWeightPerSize3, txtTotalWeightPerSize4,
txtTotalWeightPerSize5 and in each ones control source it has the formula
=[txtTotalWidthPerSize1]*[txtPIW] which in txtTotalWeightPerSize1 it would
have given automaticly given me the number 114.13. Now at the bottom of that
row I have a text box named txtTotalWeightPerSetUp which gives me a sum of
the mentioned boxes above and in its contol source I have the formula
=NZ([txtTotalWeightPerSize1])+NZ([txtTotalWeightPerSize2])+NZ([txtTotalWeightPerSize3])+NZ([txtTotalWeightPerSize4])+NZ([txtTotalWeightPerSize5]).
and it gives the total sum of 114.13.

Last but not least I have a text box named txtScrapPercent and in its contol
sourc I have the formula =IIf(([txtMasterCoilWidth]=0) Or
([txtTotalWidthPerSetUp]=0),0,([txtTotalWidthPerSetUp]/[txtMasterCoilWidth])-1)
and it automaticly gives me a toal of 83.70 and this percent is correct bot
it gives a negative in front of the number and its not negative. I will
however run into negatives and that will tell me that I nead to change my
amount of cuts I can get out of a master coil ect. so here is what I have.

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

total sould end up as 83.70 which it does but with a negative.


Thanks,
Chad

 

[Chad]

John, I have posted the same question in Utter and someone has answered the
puzzle! This formula works in the percent text box. Thanks for your help!!!

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