# Help Converting Average(filter()) Formula to Use ArrayFormula

#### LuminaryXion

##### New Member
Hello!

I'm making an order form for my guild, that will take the market prices input by the members using a google form, and calculate a 7-day average price. There are 6 possible columns the price could be in.

I have a working formula for one row. But I want to use an arrayformula type solution, so that my data can expand dynamically as more entries are submitted in the form.

Here is the working formula:
Excel Formula:
``=ifna(average(filter('Form Responses 1'!\$Q:\$Q,'Form Responses 1'!\$P:\$P=D2,'Form Responses 1'!\$A:\$A>(\$A2-7),'Form Responses 1'!\$A:\$A<=\$A2)),"")``

'Form Responses 1'!\$Q:\$Q is the cost for possible item 1
'Form Responses 1'!\$P:\$P is the name of possible item 1
D2 is the name of the item I am searching for to create the average
'Form Responses 1'!\$A:\$A is the submission date stamp
A2 is the date stamp used as a baseline to search for the past 7 days of prices.

I have this formula in 6 columns, and I am averaging this all with
Excel Formula:
``=arrayformula(iferror((O2:O+P2:P+Q2:Q+R2:R+S2:S+T2:T)/((O2:O<>"")+(P2:P<>"")+(Q2:Q<>"")+(R2:R<>"")+(S2:S<>"")+(T2:T<>"")),""))``

I've come to understand that "edit" permission is the preferred method for helping. So I've opened up a green portion of the sheet I need help with ('Calculator'!N2:T2) to editing.

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

Replies
0
Views
137
Replies
11
Views
825
Replies
3
Views
112
Replies
9
Views
125
Replies
20
Views
416

1,126,985
Messages
5,621,985
Members
415,872
Latest member
ReignEternal

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back