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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Threads
1,122,867
Messages
5,598,542
Members
414,245
Latest member
allyciv

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
Top