How to get a formula to count only negatives or only positives

I have two formulas for a helper column to a metrics sheet. I made a column that counts the number of days until an order is due to arrive, and then there are two helper columns. the first needs to take those numbers and return a value based on the number. So if the order is set to arrive in 5 days, then the value would be "1-7 days". The formula I have works, but it's counting negatives along with the positives. This is the formula I'm using:

=IF([Days to Delivery]@row < 7, "1-7 Days", IF([Days to Delivery]@row < 30, "8-30 Days", IF([Days to Delivery]@row < 60, "31-60 Days", IF([Days to Delivery]@row > 61, "61+ Days"))))

The second formula is to count the number of days overdue an order is and do the same thing, but it's counting the positives as well as the negatives.

=IF([Days to Delivery]@row > -6, "1-6 Days", IF([Days to Delivery]@row > -30, "8-30 Days", IF([Days to Delivery]@row > -60, "31-60 Days", IF([Days to Delivery]@row < -61, "61+ Days"))))

(I've added a screenshot, but am getting an error, so I don't know if it'll show up)

Find more posts tagged with

Formulas

Support

Dashboards

Sheet Summary

Accepted answers

All comments

Matt Lynn ACT

@Galadriel975 I think you should just add an and() function inside your if() with a second criteria for < or > 0.

Example:

=IF(and([Days to Delivery]@row < 7,[Days to Delivery]@row >0), "1-7 Days", IF(and([Days to Delivery]@row < 30,[Days to Delivery]@row >0), "8-30 Days", IF(and([Days to Delivery]@row < 60,[Days to Delivery]@row >0), "31-60 Days", IF(and([Days to Delivery]@row > 61,[Days to Delivery]@row >0), "61+ Days"))))

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.