# Creating formula in smartsheet

Options
✭✭✭✭
edited 12/09/19

Hello,

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

Tags:

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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))))

• ✭✭✭✭
Options

Hi Andree,

The formula is working fine.

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

Regards,

Jasmine

• ✭✭✭✭
Options

Hi Paul,

This formula is also working fine.

Thank you so much for showing this option.

Regards,

Jasmine

• ✭✭✭✭✭✭
Options

Great!

Happy to help!

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!