Need help averaging cell that have a variable range

[jazzsam]

Hi all,

Sorry if this has been asked before but i couldn't find it anywhere.

I need to average the cells in column J starting at row 62 but ending at a variable cell on lets say sheet1. The amount of cells below 62 needed to be averaged is a know number and is stored on another worksheet named sheet2 A1. for example i might need to average the next 24 cells below row 62 which would be row 86 but this can change, i cant just simply average the whole column either as the data i need is in between two zeros which skew my results and needs to be precise. i have tried using the index function but im not too familiar how it works and cant seem to find anyway to be able to change the amount of cells averaged.

thanks in advance.

EDIT: attachment Book2.xlsx

[alansidman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[jazzsam]

Thank you, I've edited the OP

[Larry.LeBlanc@O]

In any empty cell such as G57, enter:

="G62:G"&Sheet2!A1

In Sheet2, A1, enter
84

(G57 will now display G62:G84.)

Finally, your average is:
=AVERAGE(INDIRECT(G57))

[jazzsam]

I think i may not have been too clear with my question but i think this will work, i should be able to add into Sheet2 A1 62 + (the variable cell range) and get this to work for me.

Thank you, i never would have thought to do that

[huuthang_bd]

Hi jazzsam!
You can solve your problem simply with the Offset() function. Try this formula.

=AVERAGE(OFFSET(Sheet1!$J$62,,,Sheet2!$A$1))

[jazzsam]

Damn, that offset looks so simple. I wish i knew more about excel, the more i use it the more i realise how amazing it is. I tend to do a lot i VBA but theres so many simple solutions in excel th work wonders. Cheers