Averaging and using a vlookup

Workbook not included, but I'd use MATCH (instead of VLOOKUP) to get the 'row' of the data, and then either INDEX or OFFSET. to get the correct range for your AVERAGE.

Originally Posted by Pauleyb

Workbook not included, but I'd use MATCH (instead of VLOOKUP) to get the 'row' of the data, and then either INDEX or OFFSET. to get the correct range for your AVERAGE.

Thanks Pauley! The problem is that the data columns differ per line. I tried attaching an image to show what I mean in the original post. Let me know if you can see what I mean.

Thanks!

Capture.JPG

Hello chines1025. Welcome to the forum.

Please upload a representative sample Excel workbook directly to this forum.

Here's how:

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

I can see your pic, but don't understand that the data columns differ by line. Do you mean that, for example, the row you have the box around you want to average from WK43 to WK03, and then the row underneath is WK43 to WK02, and the next is WK43 to WK04? AVERAGE will ignore blank cells in its calculation, so you could just have it average out from WK43 to WKxx. The differing sizes shouldn't matter.

For example, 12 weeks. However, as you can imagine, if I'm doing this for 30 different items, typing in that AVERAGE formula can be tedious as each item doesn't sell during the same weeks. So I've been having to adjust the weeks per line.

What I'd like to do is just be able to enter the style name on a cover page and have the Average pull in automatically based on a CONCATENATE of the style name, color, and the other row details just pulling 12 weeks of visible data for that style.

Yes imagining all that is tedious.

So please upload another workbook with those details laid out the way you envision. It will give us something concrete to work with.

Thank you.

Thank you for that upload. That is much clearer.

To return the concatenated row headers, styles and colors from 'DATA' for all the code "APS" (or what ever is designated in D3) find entered in A4 filled down and across column C until you get blanks

Formula:

Please Login or Register  to view this content.

Then to return the averages in D4 filled down until you get blanks.

Formula:

Please Login or Register  to view this content.

Quick question, is there a way to only use 12 cells of data in the APS row for the average, skipping the first cell of data versus averaging the whole row?

Short answer is "Yes".

Long answer is "It depends ... (which 12 values; always the same 12 values; always contiguous 12 values ...)"