A formula that works with an array as opposed to single data values
Questions tagged [array-formulas]
2576 questions
0
votes
0 answers
Cumulative count of specific results in Google Form Responses
I have set up Google Form through which multiple people record individual items of
data from vehicles. In my Responses sheet I therefore have instances where there is more than one response per vehicle registration number.
I want to add an array…
0
votes
2 answers
Regexmatch if, and, and date combined formula
I am having issues trying to fix an older formula that I used found in this post:
Regematch if, and, and date combined forumula
The issue I am having is that now they changed the "last promotion" or or "f" column to say "never" instead of being…
BrettG
- 41
- 5
0
votes
3 answers
Google sheets array formula running balance stratified by category column and sorted by date column
I am trying to figure out an array formula to calculate a running balance within the categories listed in another column.
Here is an example sheet for…
Pierce
- 81
- 6
0
votes
1 answer
Google Sheets cell info
I am doing a ledger for accounting purposes. I have it set up like a checkbook registry. It has 4 columns: Date, Description, Amount and Balance. The formula set up in the last column (Balance) is set up to add the number in the "Amount" column to…
0
votes
3 answers
Getting ticked header values using formula in Google sheets or excel
I have a table where I need to get the values of the ticked headers as shown. The ALL takes precedence and it will include all headers when ticked.
Here is the working copy of the sheet.,
The expected values are shown in the image below.
The purple…
Code Guy
- 2,205
- 21
- 49
0
votes
1 answer
Google Sheets: ArrayFormula: Add Previous Value Err If First Value Is Text
The ArrayFormula I'm using is doing a cumulative calc, so col D is cumulative... eg =D1+C2 etc. Works fine when I create the ArrayFormula except for the first calc - since D1 is a header (text), not a number.
I tried an IF(), to check IsNumber(),…
maxhugen
- 1,349
- 4
- 15
- 28
0
votes
0 answers
Octave: How do you plot a formula on an axis with a range of curves for a range of variable values?
How can I replicate this image, given this formula?
'Fx' is a range of values plotted on the X axis.
'm' is a static value (i.e. m=6)
'Q' is a variable that we need to input a range of values for so we can compare the curves.
mr danker
- 33
- 3
0
votes
5 answers
ArrayFormula of Average on Infinite Truly Dynamic Range in Google Sheets
as per example:
A B C D E F G ∞
|======|=======|=====|=====|=====|=====|=====|=====
1 | |AVERAGE| | | | | |
|======|=======|=====|=====|=====|=====|=====|=====
2 | xx 1 | …
player0
- 69,261
- 8
- 33
- 67
0
votes
1 answer
Excel [Standard Deviation] of range filtered against another range
Firstly - no VBA if it can be helped please!
I am trying to get the Standard Deviation of a range of cells, filtered against a second range (where I want to list specific exclusions.
I have the data in a table with columns of Batch No, Date, Weight…
ChemEnger
- 71
- 9
0
votes
3 answers
Google Sheets INDEX/MATCH with multiple results
test spreadsheet
Tab 'Raw Data' contains a combination of "attendance sheets" from multiple activities. Students are in multiple activities on the same Date.
Tab 'Result' lists all unique Students and Dates.
Goal: 'Result'!B3:D11 marks P or A if…
Gabe
- 33
- 4
0
votes
1 answer
array formula to calculate range average based on matching criteria of cells with associated values in a table
I'm working to update a google spreadsheet workbook with some additional functionality.
I have various items that flow through departments. I want to gauge the progress based on their status, chosen from a drop-down menu. This way we can see how…
Jeremiah Thies
- 35
- 6
0
votes
1 answer
Array Formula not counting correctly
I have one array formula which is behaving differently to all of my others.
https://docs.google.com/spreadsheets/d/150rfbeuUW9RuG-iX6EGZAP83iOGr0KG_pn1ts-SXKoU/edit?usp=sharing
I just can't get 'Country Rating'!H2 to return the results I want and…
ajw136
- 27
- 3
0
votes
1 answer
Array Formula messing with Query Table
When I add an array formula to a column which is used to generate a query table, the query table doesn't sort the data as expected. When I remove the array formula it displays correctly.
The document is here:…
ajw136
- 27
- 3
0
votes
1 answer
How to add an Array to an AVERAGEIF(S) formula
I'm an absolute novice and have created a Google Sheets for cooking recipes and am trying to create arrays so every time I add a recipe form a new book/chef/country, their score automatically updates.
Arrays are working fine for all formulas except…
ajw136
- 27
- 3
0
votes
1 answer
Dynamic query formula nested inside an array
I have a query formula in Google sheets that updates based on additional columns of data in my Google Sheet seen here =QUERY('Deals List - URL Split'!A:DZ, "select A, C where C contains 'http'",)
So it may add QUERY('Deals List - URL Split'!A:DZ,…
jnjustice
- 83
- 1
- 7