Find the value just before Max?

KT_H

I am trying to use Smartsheet to keep inventory of products I use incrementally but I am running into a formula issue that I am unable to solve. I have been using MAX(COLLECT()) to pull the most recent inventory check for an item, but I want to be able to pull the previous check as well so that I can compare how much product was used between checks.

Does anyone have a recommendation for the best way to pull both checks?

Unique values I can currently reference:

• Date of inventory check
• Inventory check number
• Part number

jmyzk_cloudsmart_jp

Hi @KT_H

To get the "Date of inventory check," which is "Just before Max," I would use a formula like this.😀

=MAX(COLLECT([Date of inventory check]:[Date of inventory check], [Date of inventory check]:[Date of inventory check], <MAX([Date of inventory check]:[Date of inventory check])))

https://app.smartsheet.com/b/publish?EQBCT=95ac61a7517042858b8918d2ee47bb30

jmyzk_cloudsmart_jp

Hi @KT_H

To get the "Date of inventory check," which is "Just before Max," I would use a formula like this.😀

=MAX(COLLECT([Date of inventory check]:[Date of inventory check], [Date of inventory check]:[Date of inventory check], <MAX([Date of inventory check]:[Date of inventory check])))

https://app.smartsheet.com/b/publish?EQBCT=95ac61a7517042858b8918d2ee47bb30

KT_H

@jmyzk_cloudsmart_jp thank you! That helped me push through!

jmyzk_cloudsmart_jp

Happy to help!😁