How to fix the offset formula in Name Manager

Hi all,
I’m new member.
I have some problem when used offset formula in Name Manager, please see detail as below:
1. The problem is in C7 of sheet 2; C7 in sheet 2 is Average volume of lasted row 4 in sheet Quantities and it is count by the formula :

2. Is named on Name Manager as below formula:



The main problem here is although quantities table in Name Manager has expanded to CJ31 but it still count the value in BL31:


Kindly give me instruct for this problem with many thanks.
I enclosed my file (pass word is VTD2) for you to easy follow the problem.
Sorry if my English is not good and look forward to get your help

I can't test it fully because the access to Name Manager is blocked.

Try =AVERAGE(OFFSET(Quantities!$E$4,0,MATCH(0,$E$4:$CJ$4,0)-4,1,3))

I don't know why the Count in the OFFSET formula is returning 61!

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Originally Posted by David A Coop

I can't test it fully because the access to Name Manager is blocked.

Try =AVERAGE(OFFSET(Quantities!$E$4,0,MATCH(0,$E$4:$CJ$4,0)-4,1,3))

I don't know why the Count in the OFFSET formula is returning 61!

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David,
Thank for your kindly instruct
The problem is fixed now and I also don’t know the reason why the Count in OFFSET is return to 61
61 mean the formula just count for the range from Jan-11 to dec-15. The range from Jan-16 to Dec-17 was expanded in this year and last formula doesn’t apply for new range.
I’ve tried to change the range of quantities on table to new range but the formula still doesn’t work.
Can I have a question, this official file includes lots of VBA code and names manage; is this error affected by VBA code?
Thank you

You're welcome Jenny.

When you said the problem is now fixed, did you do it yourself, or was my suggestion helpful?


Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David,
I've used your suggestion for formula and it ok now, I didn't do it by myself.
Thank you

Good news!

All the best.

David