How to make this neater?

[excelos]

Hello,

I have in Q118, this formula:
[]=IFERROR($Y$18*F111,0)+IFERROR($X$18*G111,0)+IFERROR($W$18*H111,0)+IFERROR($V$18*I111,0)+IFERROR($U$18*J111,0)+IFERROR($T$18*K111,0)+IFERROR($S$18*L111,0)+IFERROR($R$18*M111,0)+IFERROR($Q$18*N111,0)+IFERROR($P$18*O111,0)+IFERROR($O$18*P111,0)+IFERROR($N$18*Q111,0)][

I want to drag it to the left, so that it will go up to A118. Obviously, this gives #REF errors as the cells references go outside the sheet.
Is there a way to make this neater?

Thanks!

 

[Special-K99]

What's the COMPLETE formula ?
Your formula ends with "...,0)]["
That's not a complete formula

If you have < > characters in the formula you may need to add spaces around them when posting to this forum

 

[Aladin Akyurek]

[…]

Is there a way to make this neater?

[…]

Try...

=SUMPRODUCT(N(OFFSET($N$18:$Y$18,0,COLUMNS($N$18:$Y$18)-1,1,-(COLUMN($N$18:$Y$18)-COLUMN($N$18)+1))),F111:Q111)

 

[excelos]

Try...

=SUMPRODUCT(N(OFFSET($N$18:$Y$18,0,COLUMNS($N$18:$Y$18)-1,1,-(COLUMN($N$18:$Y$18)-COLUMN($N$18)+1))),F111:Q111)

Can you explain me please bit by bit?

 

[excelos]

OFFSET still produces #REF if the formula refers to outside of the whole table cells? eg left to A1 etc?

 

[Aladin Akyurek]

OFFSET still produces #REF if the formula refers to outside of the whole table cells? eg left to A1 etc?

How do you mean? The formula expects equally sized ranges, no more.