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

Galadriel975

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)

Matt Lynn#

@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"))))