How to make a cell dynamically display an average % according to chosen time period?
I want this cell to output the average value according to the time period chosen. If I choose 7 days, the cell should display the average value for the last week / 7 days. If I choose 6 months, it displays the average of the last 6 months and so on. Is this possible?
Link for editable file: https://docs.google.com/spreadsheets/d/1ExXtmQ8nyuV1o_UtabVJ-TifIbORItFMWjtN6ZlruWc/edit?usp=sharing
Solution
you can change B14 to:
=INDEX(SPARKLINE(QUERY({IFERROR(DATEVALUE(HISTORY!A:A)), HISTORY!D:D},
"select Col2
where Col2 is not null
and Col1 <= "&INT(MAX(HISTORY!A:A))&"
and Col1 > "&INT(MAX(HISTORY!A:A))-C12-1, 0),
{"charttype","line";"color","#1aded1"}))
and for average use in C13:
=INDEX(AVERAGE(QUERY({IFERROR(DATEVALUE(HISTORY!A:A)), HISTORY!D:D},
"select Col2
where Col2 is not null
and Col1 <= "&INT(MAX(HISTORY!A:A))&"
and Col1 > "&INT(MAX(HISTORY!A:A))-C12-1, 0)))
- Codeigniter parentheses in dynamic Active Record query
- How to send cart data in hidden field and fetch it in codeigniter controller
- Sorting an array of objects by a property
- Sorting objects in array
- Codeigniter Insert Array to Database
- How to display an array of values in a table with javascript
- C# ToArray conversion merged into one line with resulting array element change
- accessing values in an ARRAY STRUCT in Google Bigquery
- Sort an object array by a column
- Push a new object into a sorted objectArray at the correct position to avoid re-sorting
- How do you natsort() by a column of an array of objects?
- How to create JSON Schema for an array of dictionaries with different type values
- Naturally sort a flat array without preserving original keys
- Create array of values from a dictionary of array of objects filtered by property
- How can I sort an alphanumerical Multidimensional Array in php?
- Naturally sort the rows of a 2d array by a column of alphanumeric values
- Sort an array of alphanumeric strings naturally
- Sort an array of strings by their 3rd character, then 4th, then 2nd
- Sort array based only on trailing numbers in strings
- Custom sort a flat array of scalar values so that integers are given top priority
- sort() prints 1 instead of the sorted array
- How to naturally sort a flat array of filenames (some ending with numbers, e.g. "-2")?
- Is there a shortcut way to return an array sorted in C#
- How to implement multiple custom sorting rules based on hyphen-delimited substrings?
- How can I access data of object stored in an array?
- How to include OR in Array formula where ONLY SOME criteria need to be TRUE - Excel
- How to fix 'Private field must be declared in an enclosing class'
- How can I write a Zig function that can accept and return strings?
- Search for groups of cells that contain values, calculate the difference, place result in adjacent column
- Including more commands if CSV column is not empty