How to use INDIRECT in ARRAYFORMULA in Google sheets

Question

I am trying to get the Google sheet to use the value of E1 and G1 as a reference to calculate at the moment:

The following works at the moment. But I want to replace N with the value in E1 and S with the value in G1.

=ARRAYFORMULA(IF(B5:B="",,MMULT(N5:S*1, TRANSPOSE(COLUMN(N:S))^0)/SUM(N3:S3)))

I tried to use INDIRECT to reference E1 and G1 for N and S.

I tried the following but it returns the error telling "Function INDIRECT parameter 1 value is 'S'. It is not a valid cell/range reference."

=ARRAYFORMULA(IF(B5:B="",,MMULT(INDIRECT(E1&"5"):INDIRECT(G1)*1,
 TRANSPOSE(COLUMN(INDIRECT(E1):INDIRECT(G1)))^0)/SUM(INDIRECT(E1&"3"):INDIRECT(G1&"3"))))

How can I achieve this?

Answer 1

try:

=ARRAYFORMULA(IF(B5:B="",,MMULT(INDIRECT(E1&5&":"&G1)*1, 
 TRANSPOSE(COLUMN(INDIRECT(E1&":"&G1)))^0)/SUM(INDIRECT(E1&3&":"&G1&3))))