How to eliminate the OFFSET funtion from excel equations

D9

Please Login or Register  to view this content.

F9

Please Login or Register  to view this content.

G9

Please Login or Register  to view this content.

I have no idea what are you doing around with OFFSET, but with your desired results, try:
F9:
=IF([@H2]=0,"",SUM(D9:$D$41))
G9:
=IFERROR(INDEX([H1],MATCH(LARGE([H3],ROWS($1:1)),[H3],0)),"")
Drag both down.

By the way, if [H2] and [H3] is helper columns and they can be removed, one formula in G9 to refer to [H1] only

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Originally Posted by bebo021999

I have no idea what are you doing around with OFFSET, but with your desired results, try:
F9:
=IF([@H2]=0,"",SUM(D9:$D$41))
G9:
=IFERROR(INDEX([H1],MATCH(LARGE([H3],ROWS($1:1)),[H3],0)),"")
Drag both down.

By the way, if [H2] and [H3] is helper columns and they can be removed, one formula in G9 to refer to [H1] only

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Hi, bebo021999, if the data in rows increased (when updating data) your formula in F9 (Column F) will not update automatically.

Originally Posted by nflsales

Hi, bebo021999, if the data in rows increased (when updating data) your formula in F9 (Column F) will not update automatically.

Correct! Maybe use D9:$D$42 instead of D9:$D$41.