Formula needed for calculating an average based on other cells.

[NamssoB]

Confusing title, I know...here's what I need:

I have a spreadsheet with a label in Column A, and the data is added each month in the columns to the right. For example:

What I want:

Cell A22: The label (Scheduled), then appended to this label is the Average of the last 6 values in that row. So A22 *should* say "Scheduled, 94.5 Avg", A27 should say "Consults, 47 Avg".

Using this formula below, I'm getting the wrong average and can't figure out why. What am I doing wrong on this, OR is there a better way to do this?

=CONCATENATE("Scheduled ",AVERAGE(OFFSET($A$22,0,COUNTA($B$22:$Z$22)-6,1,1)))

Also, what happens when I get more than "Z" columns of data? I can change it to ZZ, but that's temporary. is there an absolute way to just find the last column with data?

 

[NamssoB]

Yay! Thanks all, there were numerous solutions that worked, although some had some issues with formatting (beyond 1 decimal place for example). In the end, I like the options presented by @Dave Patton.

Question for my own knowledge, what's happening in the Match() function with the 10^35 exponent?

Also for other, using Take(), not super familiar with this!

 

[Dave Patton]

Question for my own knowledge, what's happening in the Match() function with the 10^35 exponent?

Also for other, using Take(), not super familiar with this!

Suggestions:
- review the formulas with Formulas Formula Evaluate
- use Excel's help to explain the functions
- search the forum and/or internet for examples and explanations
- try 10^35 in cell It is a very large number that is much larger than the number of columns.