How to simply excel formula?

Question

I have a query that will generate the following formula in an array from SQL and set into a range in worksheet, but it's difficult to read:

=SUMIF(INDIRECT(ADDRESS(ROW()-1,5)&":"&ADDRESS(ROW()-6,5)),"+",INDIRECT(ADDRESS(ROW()-1,COLUMN())&":"&ADDRESS(ROW()-6,COLUMN())))-SUMIF(INDIRECT(ADDRESS(ROW()-1,5)&":"&ADDRESS(ROW()-6,5)),"-",INDIRECT(ADDRESS(ROW()-1,COLUMN())&":"&ADDRESS(ROW()-6,COLUMN())))

So dose excel contain any method to change only the address part INDIRECT(ADDRESS(ROW()-1,5)&":"&ADDRESS(ROW()-6,5)) to E10:E5 for easy to trace?

Because of the formula is needed to be dynamic generate before set to worksheet (i.e. dynamic column and row), so it should be needed to simplify after set the array to cell.

Any method can do the similar thing like Evaluate function in excel but only apply for INDIRECT and ADDRESS which allow user to read the simply formula like =SUMIF(E10:E5,"+",J10:J5)-SUMIF(E10:E5,"-",J10:J5)

Answer 1

To simplify the formulas, you could enter in RC notation by changing the reference style to R1C1 or using .FormulaR1C1. The advantage of RC notation is that the formula text is consistent down the whole column. For your formula above you could enter either as:

=SUMIF(R[-6]C5:R[-1]C5,"+",R[-6]C:R[-1]C)-SUMIF(R[-6]C5:R[-1]C5,"-",R[-6]C:R[-1]C)

or =SUM(SUMIF(R[-6]C5:R[-1]C5,{"+","-"},R[-6]C:R[-1]C)*{1,-1})

and the formulas should be easy to read in A1 notation.