Find the value just before Max?

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

Find more posts tagged with

Formulas

inventory management

Accepted answers

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

All comments

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!😁

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.