Help with IF + MAX formula with OR

Hi, I'd like to make a formula for the following purpose:

We have daily quotas that need to be reached, however it changes depending on the day. Monday through Thursday the goal is 3, and Friday the goal is 1. I'd like an IF formula to do a MAX operation depending on the day. This is so that every day we can see how many more people do we need to reach to meet the daily goal.

I already have a helper column with WEEKDAY to get the day of the week number.

I have an idea of what could work for this, but I get stuck at how to have multiple days (Monday through Thursday) in the IF formula.

IF WEEKDAY is Friday (day "6") then = MAX(0,3 - [Today's daily total]@row) else IF WEEKDAY is Monday through Thursday (days "2, 3, 4 or 5") then MAX(0,1 - [Today's daily total]@row).

I use MAX so that the number doesn't turn negative and instead remains at 0 if the goal is met.

Thanks

Find more posts tagged with

Formulas

IF(

Accepted answers

All comments

Louis1

Hi V,

Would this work ?

Note the Weekday number is not used - only the other two rows are used in my formula - ie for Tuesday (Need/Reached) =IF(Tuesday3 >= Tuesday2, "Reached", Tuesday2 - Tuesday3)

Vicky C.

https://community.smartsheet.com/discussion/comment/479979#Comment_479979

Hi Louis,

Thank you! I should've mentioned this will be used for a dashboard, so I would prefer to have the "Need / Reached" value in a single cell, which is why I thought of maybe using IFs so the formula can change depending on the day.

If there is no other way to do this I will think of rearranging the dashboard to show each day's quota need / reached individually with the method you've provided.

Thank you!

Louis1

V, Show me what you like to see in the dashboard and I see if I can assist further. Thanks, L.

Vicky C.

https://community.smartsheet.com/discussion/comment/480327#Comment_480327

Hi L, please see below.

This worked for us before since we used to have the same goal for each day throughout the week (So on my metrics sheet it is a single cell with a formula, which is what the dashboard is pulling here), but now that the Friday goal changed I can't seem to find a way to show a different "countdown" depending on the day.

Appreciate your help.

Louis1

V, Let's try this method! -

I am assuming your sheet has these like-columns

Date column - consecutive dates (or dates of interest) - manually entered for next x-days, or x-weeks, or etc.

Day # (column formula) =WEEKDAY(Date@row)

Target - formula that returns days target =IF(OR([Day #]@row = 2, [Day #]@row = 3, [Day #]@row = 4, [Day #]@row = 5), 3, IF([Day #]@row = 6, 1, 0)) - note if a weekend is entered then returns 0

Today (formula that compares todays date with date column) =IF(TODAY() = Date@row, "Yes", "No") - can be a column formula. If Yes is returned then I assume this is the day of interest (and relevant data shown on dashboard). Todays date (here) is 14/4/24 thus the "Yes" seen above.

Daily Total - total entered manually (Once sheet is set-up this should be the only value that is entered) - Logically no values seen after 14/4/25 (future)??

Need/Reached (column formula) =IF([Daily Total]@row > Target@row, "Reached", Target@row - [Daily Total]@row) - can be a column formula

The above (I hope) explains the 6 columns created.

In sheet summary I have a simple lookup formula the returns the Need/Reached values (shown as Lookup Todays Target) ie =VLOOKUP("Yes", Today:[Need/Reached], 3,0). THIS is the value that gets populated in the dashboard every time there is a change and when the sheet is saved.

I have changed the heading to reflect yours when setting up the dashboard.

The metric in the dashboard points the sheets summary data ie:-

Hope this gives you what you need or gets you closer.

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.