setting up value buckets including negative values

Mary McCabe

Hi all, Im working on a formula in a column labeled "bucket days." Im using a column labeled # days as the reference column. The positive day buckets work fine, but the negative day bucket values are not being placed properly.

For example when the value in # days is -46, its being labeled "-1 to -30 days". The value of -77 is being labeled "-31—60 days".

Those with value less than -91 are turning up blank as requested.

Here is my formula:

=IF([DAYS FROM DUE DATE TO TODAY]@row < -91, "", IF([DAYS FROM DUE DATE TO TODAY]@row <= -90, "-61-90 days", IF([DAYS FROM DUE DATE TO TODAY]@row <= -60, "-31 to-60 days", IF([DAYS FROM DUE DATE TO TODAY]@row <= -1, "-1 to -30 days", IF([DAYS FROM DUE DATE TO TODAY]@row <= 5, "1-5 days", IF([DAYS FROM DUE DATE TO TODAY]@row <= 10, "6-10 days", IF([DAYS FROM DUE DATE TO TODAY]@row <= 15, "11-15 days", IF([DAYS FROM DUE DATE TO TODAY]@row < 60, "16 to 60 days", IF([DAYS FROM DUE DATE TO TODAY]@row > 60, "")))))))))

Paul Newcome

Try adjusting the numbers in your arguments. What you want is (for example) numbers that are less than or equal to -61 (not -90) to be labeled as "-61-90 days".

Mary McCabe

Thank you, Paul! Worked perfectly. I was becoming cross eyed trying to figure it out. Have a great day.

I

Paul Newcome

Happy to help. 👍️