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

LuminaryXion

New Member
Joined
Jul 23, 2012
Messages
6
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.
Buy Form With API

TYIA for your advice! <3
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,223,355
Messages
6,171,615
Members
452,411
Latest member
sprichwort

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top