Array problem in Excel

New Contributor

Why my Excel is interpreting a formula as an array. When I open my excel all cells are with #VALUE!

When I click a cell there are brackets at the beginning and at the end of the formula. When I click inside the cell the brackets disappear and the cell turns to normal. How can I solve the spreadsheet without clicking inside every cell? I use Excel 365

{=IF(INDIRECT(ADDRESS($A178,COLUMN('Drivers-Data Base'!E$1),,,"Drivers-Data Base"))<>0,INDIRECT(ADDRESS($A178,COLUMN('Drivers-Data Base'!F$1),,,"Drivers-Data Base"))/INDIRECT(ADDRESS($A178,COLUMN('Drivers-Data Base'!E$1),,,"Drivers-Data Base"))-1,0)}

4 Replies

@FigueiredoJoseMauro 

Just click on formula at any place in formula bar and Enter.

#VALUE could be if in A178 you have text or that's blank cell.

@Sergei Baklan 

 

No, the cell is not blank. The problem is that I have hundreds cells with the same problem on the spreadsheet

The problem is that the Excel is interpreting that the formula is an array putting brackets in the cells. As I said, the brackets {} disappear if I click inside the cells e the formula becomes normal and the number appears in the cell.

@FigueiredoJoseMauro 

Most probably that's a bug. If to simplify your formula as

=IF( INDIRECT("'Drivers-Data Base'!E$" & $A178) <>0,
     INDIRECT( "'Drivers-Data Base'!F$" & $A178)/
     INDIRECT( "'Drivers-Data Base'!E" & $A178 )-1,
    0)

and use it as an array formula (i.e. entered with Ctrl+Shift+Enter) it gives the same result as regular formula (i.e. entered with Enter).

 

Good news you don't need here array formula at all. Again, stay on the cell with formula, click on it in formula bar and Enter. Brackets will disappear and formula gives correct result.

Dear Sergei, thanks for your reply. That is my problem. A friend sent me this huge spreadsheet, with hundreds of cells with the same command. And I need to enter cells by cell to make the spreadsheet work. I was trying to see if there is anything I could do to make the spreadsheet work without having to enter in each cell