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

Paul Newcome

You can also work backwards and skip the AND statements. Not to say Andree is wrong. Just presenting another option/point of view.

 

=IF([Short Shelf Life]@row >= .6, 0, IF([Short Shelf Life]@row >= .4, .05, IF([Short Shelf Life]@row >= .3, .15, IF([Short Shelf Life]@row >= .2, .4, .5))))

Jasmine Hassan

Hi Andree,

 

The formula is working fine.

Thank you so much for taking the time to help me.

 

Regards,

Jasmine

 

 

Jasmine Hassan

Hi Paul,

 

This formula is also working fine.

Thank you so much for showing this option.

 

Regards,

Jasmine

Andrée Starå

Great!

Happy to help!

Best,

Andrée

Paul Newcome

Happy to help. Sometimes having a different take on a formula can make a huge difference and can be the difference between working and not when you start looking at the more complex solutions.