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:
'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
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
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