Creating formula in smartsheet

Jasmine Hassan

Hello,

I have a requirement to create the below formula in smartsheet. Please help.

If short shelf life is between 40% to 59%, then discount is 5% on the invoiced amount,

If short shelf life is between 30%– 39%, then discount is 15% on the invoiced amount,

If short shelf life is between 20 – 29%, then discount is 40% on the invoiced amount,

If short shelf life is 19% & below, then discount is 50% on the invoiced amount 

Thanks,

Jasmine

Andrée Starå

Hi Jasmine,

Try this.

Place the formula below in the discount column (percentage format)

You can then use the discount result to get the discount amount and calculate the total invoice.

=IF([Short Shelf Life]@row <= 0,19; 0,5; IF(AND([Short Shelf Life]@row >= 0,2; [Short Shelf Life]@row <= 0,29); 0,4; IF(AND([Short Shelf Life]@row >= 0,3; [Short Shelf Life]@row <= 0,399); 0,15; IF(AND([Short Shelf Life]@row >= 0,4; [Short Shelf Life]@row <= 0,59); 0,05; 0))))    

The same version but with the below changes for your and others convenience.  

=IF([Short Shelf Life]@row <= 0.19, 0.5, IF(AND([Short Shelf Life]@row >= 0.2, [Short Shelf Life]@row <= 0.29), 0.4, IF(AND([Short Shelf Life]@row >= 0.3, [Short Shelf Life]@row <= 0.399), 0.15, IF(AND([Short Shelf Life]@row >= 0.4, [Short Shelf Life]@row <= 0.59), 0.05, 0))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting