Calculated column with nested IFs and comparisons

Question

I am working on an Aging report and my customer would like to group items by the following values: 0-30, 31-60, 61-90, 90+

I am creating a calculated column that will contain these values and let me group items for a view.

The following calculation works until I hit a callculation that is 61 or greater. In this case it displays No rather than 61-90 or 90+ as desired:

=IF(aging<31
    ,"0-30"
    ,IF(AND(aging>30,aging<61)
        ,"31-60"
        ,IF(AND(aging>60,aging<91)="61-90"
            ,IF(aging>90
                ,"90+"
               )
           )
       )
   )

The aging column is itself a calculated column which provides the age of an item Today()-[Created]. This is a column that was also requested.

so you are not able to use the 'aging' column in formula as its a calculated, is that your problem ?? — Gaurravs, Oct 14 '15 at 16:38

score 1 · Accepted Answer · answered Oct 14 '15 at 16:45

1

It seems that the syntax is incorrect in the IF statements.

Replace:

=IF(aging<31,"0-30",IF(AND(aging>30,aging<61),"31-60",IF(AND(aging>60,aging<91)="61-90",IF(aging>90,"90+"))))

with this:

=IF(aging<31,"0-30",IF(AND(aging>30,aging<61),"31-60",IF(AND(aging>60,aging<91),"61-90",IF(aging>90,"90+"))))

Change was replacing the = with a , in this section IF(AND(aging>60,aging<91)="61-90"

answered Oct 14 '15 at 16:45

Jordan

2,814
8
27
46

Just noticed.. :) right it was just syntax issue.. good catch @Jordan – Gaurravs Oct 14 '15 at 16:53

score 0 · Answer 2 · edited Apr 13 '17 at 12:41

I reformatted your formula with line breaks and spaces, makes it easier to spot problems, you can safely copy/paste to SharePoint because they will be ignored.

Other poster allready noticed the error.

Note that your last IF does not have a false assigned either.. not an error.. but will default to No also.

Also note your Today() most likely does not do what you expect:

How to use Today and Me in Calculated column

Calculated column with nested IFs and comparisons

2 Answers2