How to change the range of which to calculate an average?

[Waimea]

Hi,

I have a data validation dropdown in cell E151, depending on the value of this cell I want to calculate an average of a range of cells.

If the data validation list in empty I want to calculate the average of range H151:L151.

If the data validation list says "1", I want to calculate the average of range H151:K151.

The data validation in cell E151 could have values from 1 to 10. So if the value of the data validation list in 3 I would get the average of H151:M151?

 

[KRice]

Do you mean if the data validation is empty, you want the average of H151:K151 (not L151)...and then for any non-zero selection from the dropdown list, you want to consider that many more additional entries to the right of H151:K151. If so, consider this:
For the dropdown list in some helper cells, then the Data Validation for cell E151 would have a source (in this case) of =G139#

MrExcel_20231208.xlsx
	G
138	Dropdown
139
140	1
141	2
142	3
143	4
144	5
145	6
146	7
147	8
148	9
149	10
Sheet5

Cell Formulas
Range	Formula
G139:G149	G139	=VSTACK("",SEQUENCE(10))
Dynamic array formulas.

MrExcel_20231208.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
150	Selector	Average
151	7	6.363636		5	3	1	7	9	11	13	15	1	2	3	4	5	6
Sheet5

Cell Formulas
Range	Formula
F151	F151	=AVERAGE(TAKE(H151:U151,,4+E151))

Cells with Data Validation
Cell	Allow	Criteria
E151	List	=$G$139#

 

[Waimea]

Thank you for your reply.

It's early here and I need coffee!

I have a list of 1 to 10 in range "=Lista!$D$1:$D$10"

I have drop down list with 1 to 10 in cell E151.

If cell E151 is empty I want to calculate the average of 5 years, from year 2023 to year 2028.

If cell E151 is "1" I want to calculate the average of 6 years, from year 2023 to year 2029. Etc.

=AVERAGE(H151:L151) -> Average(H151:L151 + Cells(1)) ?

Maybe a IF formula combined with the average formula?

 

[KRice]

Have a look at my solution and change the 4 to a 5 so that the first 5 cells of the range are taken initially...and then some number of additional cells are also included in the range depending on the value in E151. So is 2023 to 2028 considered 5 years or 6...are you counting the endpoints of the range?

As this version is laid out, you'll get the first 5 columns by default (with a blank in E151)---which is only 2023 to 2027---and then there are 9 additional columns (not 10) that could potentially be included in the expanding range. So you may need to adjust the default number of columns or the full range (H151:U151) and the dropdown list so that you won't select a value that isn't supported by the data.

MrExcel_20231208.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
150	Selector	Average		2023	2024	2025	2026	2027	2028	2029	2030	2031	2032	2033	2034	2035	2036
151	8	6.076923		5	3	1	7	9	11	13	15	1	2	3	4	5	6
Sheet5

Cell Formulas
Range	Formula
H150:U150	H150	=SEQUENCE(1,14,2023)
F151	F151	=AVERAGE(TAKE(H151:U151,,5+E151))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E151	List	=$G$139#

 

[Waimea]

Have a look at my solution and change the 4 to a 5 so that the first 5 cells of the range are taken initially...and then some number of additional cells are also included in the range depending on the value in E151. So is 2023 to 2028 considered 5 years or 6...are you counting the endpoints of the range?

I get a massive spill error with your solution, I am not sure why yet.

I hope there is a simpler solution. Isn't there any ways of adding cells to the range I'm trying to average?

 

[KRice]

A spill error means that a formula is attempting to display an array of values and there isn't sufficient space on the worksheet to accommodate the entire array. The AVERAGE formula won't generate that error, but the formula I used to create the dropdown helper list of values (a space followed by the numbers 1..10) could if you don't have 11 vertical spaces that are open. Also, the formula I just added to show the years 2023-2036 spills its values. Just move the dropdown helper formula anywhere out of the way so that it spills...and then hide it since it only needs to be referenced as the Data Validation source. And if the 2023-2036 years formula is causing the spill error, just delete it...it's not needed, nor is it referenced anywhere. I only included it for reference to clarify the potential issue regarding your initial default set of 5 or 6? values.

The formula I presented is quite simple. It uses Excel's native AVERAGE function and it controls the range sent to AVERAGE by first specifying the entire potential range of values (H:U) and then taking (the TAKE function) only the first 5 values (if that's what you want with a blank in E151) plus some additional number of values corresponding to the value in E151...that's what the 5+E151 does.

 

[Waimea]

Now I don't have that spill error anymore but it seems that I get the full range for the average, disregarding the value in E151.

So no matter what I put in the E151 cell I get the 10 values in the average?

 

[KRice]

Could you either post a snippet of your worksheet with the XL2BB add-in or a screenshot showing the formula where the average is taken?

 

[Waimea]

KRice, I reread your post and I think it's working now, the text you wrote does explain it in great detail. I think I got confused by the take argument showing the full range.

A bit of scope creeping, but would it be possible to put a border around the cell/cells of the range that is being used by the formula?? Could be a VBA solution as well.

 

[KRice]

Oh good...yes, TAKE extracts so many rows or columns from an array, so we start with the full array and then specify how many columns of it to take from the left side...5+E151 (the value in the dropdown list). To put a border around the cells used, I can think of two options: either VBA or conditional formatting (CF). Here is an approach using CF, which depends on three separate rules: one to draw the upper and lower horizontal border, one for the left-side border on the 1st cell, and one for the right-side border at the end of the used range.

MrExcel_20231208.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
150	Selector	Average		2023	2024	2025	2026	2027	2028	2029	2030	2031	2032	2033	2034	2035	2036
151	4	7.222222		5	3	1	7	9	11	13	15	1	2	3	4	5	6
Sheet5

Cell Formulas
Range	Formula
H150:U150	H150	=SEQUENCE(1,14,2023)
F151	F151	=AVERAGE(TAKE(H151:U151,,5+E151))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H151:U151	Expression	=COLUMNS($H$151:H$151)=1	text	NO
H151:U151	Expression	=COLUMNS($H$151:H$151)=5+$E$151	text	NO
H151:U151	Expression	=COLUMNS($H$151:H$151)<=5+$E$151	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E151	List	=$G$139#

You'll have to look carefully, as the border is rather thin. In my version it is red, but the XL2BB add-in seems to render it in gray.
Important: before you go too far with the CF formulas, confirm what you want for the initial (E151 is blank) range...4, 5, or 6 cells. And then how many more cells could conceivably be included. Otherwise you'll have to redo several formulas.