explain formula

[sohaila]

PLEASE EXPLAIN THIS FORUMLA
=IFERROR(INDEX(Sheet1!R3C[-3]:R1000C[-3],SMALL(IF(MONTH(Sheet1!R3C1:R1000C1)=MONTH(TODAY()),ROW(Sheet1!R3C[-3]:R1000C[-3])-2),ROW(Sheet1!R[-3]C[-3]))),"""")"

[Richard Buttrey]

Hi,

It seems an odd formula. What do you think it's doing?

I suspect it's an array formula that is entered with Ctrl-Shift-Enter (i.e. it should be encapsulated within {}

It's always going to be an Error since there is no second part for if the IF test is False.

However
The =IFERROR wrapped around simply returns " if the formula evaluates to an error.
otherwise it's using the INDEX function to find a value in rows 3:1000 of the column three columns to the left of the column that contains the formula.

There is no column parameter to the Index function si by default it uses the column three to the left.

The row number in that column that it's indexing is the nth smallest number in one of two ranges that's determined by the IF test. It's the ROW(Sheet1!R[-3]C[-3]) bit which determines the value n and this is always the row number 3 rows above the formula.

As far as I can see the fist bit of the IF test MONTH(Sheet1!R3C1:R1000C1)=MONTH(TODAY()) is always going to return a False value since the function MONTH() when used with a range is illogical and when this is compared to the month number of today's date it's going to be a FALSE if test and hence because it doesn't contain a definition of what it expects when the IF test fails then the result is an error.