# Formula returning wrong result

#### vik1987

##### New Member
Very difficult to explain, but I have a formula (presented below) which is being used for 1000+ cells (it was simply dragged across all the cells, so there are no differences except relative references). In all cases, except for 2, it is returning the correct results. On those 2 results, the formula builder suggests it is being correctly calculated, however, I know the result of all the "IF" statements therein are FALSE, so the return should be 0. Pressing F9 (on keyboard) shows that when it gets to the IF being FALSE in the final stage, the possible results (on these 2 anomalies only) are not "0" as required (as in all other cells) but the array of values which are possible when IF statement is TRUE. The 'Sheet1' references are simply to the grid of possible results (when there is a TRUE). I have no idea what is going wrong having examined the formula multiple times.

=IFERROR(IF(AT69="top 5",INDEX('Sheet1'!\$E\$69:\$E\$75,SUMPRODUCT(--(AG69<='Sheet1'!\$B\$69:\$B\$75), --(AG69>='Sheet1'!\$A\$69:\$A\$75), ROW(\$A\$1:\$A\$7))),IF(AT69="top 10",INDEX('Sheet1'!\$D\$69:\$D\$75,SUMPRODUCT(--(AG69<='Sheet1'!\$B\$69:\$B\$75), --(AG69>='Sheet1'!\$A\$69:\$A\$75), ROW(\$A\$1:\$A\$7))),IF(AT69="top 3",INDEX('Sheet1'!\$F\$69:\$F\$75,SUMPRODUCT(--(AG69<='Sheet1'!\$B\$69:\$B\$75), --(AG69>='Sheet1'!\$A\$69:\$A\$75), ROW(\$A\$1:\$A\$7))),IF(AT69="> top 10",INDEX('Sheet1'!\$C\$69:\$C\$75,SUMPRODUCT(--(AG69<='Sheet1'!\$B\$69:\$B\$75), --(AG69>='Sheet1'!\$A\$69:\$A\$75), ROW(\$A\$1:\$A\$7))),0)))),0)

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Replies
3
Views
251
Replies
8
Views
415
Replies
15
Views
472
Replies
2
Views
227
Replies
3
Views
841

Threads
1,196,010
Messages
6,012,840
Members
441,733
Latest member
MartijnB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back