Alert someone when \$ value is approaching next increment of \$50,000.00

✭✭✭✭✭
edited 03/08/24

I am creating a 'Project Tracker'; 1 location per row with many different columns one of the columns being '\$ Spend to Date'.

I need to notify program manager when '\$ Spend to Date' is nearing \$50,000.00, then again when '\$ Spend to Date' is nearing \$100,000.00, then again . . . \$150,000.00, then again . . . until we reach the cap of \$5,000,000.00.

I'm wondering what is the best way to do this.

• ✭✭✭✭✭

HI, @PeggyLang,

Here's one approach that assumes your threshold is \$5,000. In other words, "send an alert when spending is less than \$5,000 from the next increment."

This approach uses two column helpers. One column stores the next/nearest increment--Increment. The other column indicates whether or not to send the alert--Alert

1) In the Increment helper, use MROUND() to return the nearest multiple of 50,000.

`MROUND([\$ Spend to Date]@row, 50000)`

2) In the Alert column use the formula below to set the alert flag--"where '1' means 'yes, send alert'".

`IFERROR(IF((50000 - MOD([\$ Spend to Date]@row, 50000)) <= 5000, 1, 0), "")`

3) Configure a workflow automation to run at your desired time. Set the criterion to, Where Alert is equal to 1.

4) Use the placeholder {{Increment}} in your message to show the \$ increment they are nearing.

• ✭✭✭✭✭

HI, @PeggyLang,

Here's one approach that assumes your threshold is \$5,000. In other words, "send an alert when spending is less than \$5,000 from the next increment."

This approach uses two column helpers. One column stores the next/nearest increment--Increment. The other column indicates whether or not to send the alert--Alert

1) In the Increment helper, use MROUND() to return the nearest multiple of 50,000.

`MROUND([\$ Spend to Date]@row, 50000)`

2) In the Alert column use the formula below to set the alert flag--"where '1' means 'yes, send alert'".

`IFERROR(IF((50000 - MOD([\$ Spend to Date]@row, 50000)) <= 5000, 1, 0), "")`

3) Configure a workflow automation to run at your desired time. Set the criterion to, Where Alert is equal to 1.

4) Use the placeholder {{Increment}} in your message to show the \$ increment they are nearing.

• ✭✭✭✭✭

I worked on this prior to receiving your response and I built exactly (minus the 'rounding' function) what you had suggested. Works very nicely.

Thank you!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!