Mar 29 2022 06:04 AM
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)}
Mar 29 2022 06:15 AM
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.
Mar 29 2022 07:02 AM - edited Mar 29 2022 07:09 AM
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.
Mar 30 2022 02:26 PM
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.
Apr 04 2022 10:41 AM