Skip to content

Excel’s #NULL! Error and the Intersect Operator

    Excel’s NULL error will appear for one of two reasons: you have either used an incorrect range operator or you are attempting to use the intersect operator where there is no intersection of ranges.

    Incorrect Range Operator

    When you are performing a calculation on a range you separate the cell addresses with a colon, for example A1:A2

    However if you leave out the colon and put a space in its place you will get the #NULL! error.  Equally if you are performing a calculation on multiple ranges, you need to separate each of the ranges with a comma eg =SUM(A1:A5,M7:M10,Q10:Q13). If you omit the comma and leave a space in its place you will get the #NULL! error.

    The reason the space causes the #NULL! error is that it is used as the intersection operator, which is actually very useful – see below.

    The Intersect Operator

    The intersect operator allows you to perform calculations on ranges that intersect. Take the example below where we might want Excel to perform any number of calculations based on the intersection of one or more columns and rows, for example Bob’s sales in January and May.

    null error

    To use the intersection operator to calculate Bob’s sales in January you would write the following formula: = Bob Jan I am using named ranges but this is not necessary, watch the video if you need an explanation on how to set them up.

    Notice the space between the two named ranges – this is the intersect operator. The formula will return 366 which is the value in the cell that intersects the two ranges.

    null error

    To calculate Bob’s sales in January and May you could use =SUM(Bob Jan, Bob May)

    null error

    If you try to use the intercept operator where the ranges don’t intercept you will get the #NULL error.  For example the formula below would cause the error.

    null error

    Leave a Reply