Creating formula in smartsheet
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
Comments

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 semicolon to a comma.
Did it work?
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Hi Andree,
The formula is working fine.
Thank you so much for taking the time to help me.
Regards,
Jasmine

Hi Paul,
This formula is also working fine.
Thank you so much for showing this option.
Regards,
Jasmine

Great!
Happy to help!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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.
Help Article Resources
Categories
Check out the Formula Handbook template!