can't figure out formula

[millermail]

Made inventory program for work (use v9.8)and need it to do calculation which I can't get no matter what have tried. Made field called Cost and another called Retail. I need it to take whatever I enter in the Cost field and have that figure calculated to get retail cost and insert into Retail field.

Example if Cost is $5 or less then take the info in the Cost box and multiply it times 5 and that answer goes in the Retail box but I can't even get it to do that let alone finish all the multipliers (there are like 6) meaning we need if Cost is <5 then Cost x 5, if Cost is <10 then Cost x 4 etc

Can anyone help us on this formula we need to make?
Can it be done in the 1st place?
I needed it to take Retail and discount that 20% for another field and I got that formula to work fine but can't get the other at all

 

[suesloan]

The easiest way to do this is with a calculated field for your complicated formula. It is then a simple algebra-like formula: If(cost <= 5, cost * 5,If(cost <= 10, cost * 4, etc. Just make sure the parentheses match up. This makes it easy to test because you can put the calculated field on a worksheet or form to see if it works.

Then you can put the name of the calculated field in the Field Definition modification formula.

Sue Sloan