arraysif-statementgoogle-sheetsgoogle-sheets-formulagoogle-query-language
Convert blank values to zero when using the Query function
Is there a way to convert data going into a query() function so that blank/null values are converted to zero?
I have a dataset with three columns:
Group AC Plan
Comms 350 [blank]
IT 50,000 85,000
Rent 15,000 15,000
Training [blank] 8,500
I want to query the set to find the difference between AC and Plan,
e.g.
=query('data', "select A, B-C",1). However, since the query omits blank cells when calculating B-C, the result is like this:
Group Diff
Comms [blank]
IT -35,000
Rent 0
Training [blank]
When I wanted this:
Group Diff
Comms 350
IT -35,000
Rent 0
Training -8,500
Before you go wild: I am not able to change the source data (the incoming data is actually another query() using the pivot functionality). And the reason I need this to work is I use a limit clause to confine the results to only the top 25 and bottom 25 differences.
Solution
=ARRAYFORMULA(IF(QUERY(T3:V7)="", 0, QUERY(T3:V7)))
- Displaying binary images on JSP page
- Linear search in a sorted array - Java
- Convert flat array of delimited key path strings to a multidimensional array with each path terminating at an empty array
- How to iterate through an array of pointer in c
- Is there a way to create dynamic ranges given an array of values, and counting how many belong to each range
- Given an array of N integers, return the max sum of 3 NON-adjacent elements
- Parse array of strings composed of dot.separated.keys into a multidimensional array
- Explode each string in a flat array
- Parse a string with three delimiters into an associative array of indexed arrays
- Paginate Javascript array
- Page through an array
- Sum values based on three criteria and transpose result from tabular to visual layout
- Convert a JavaScript statement to a string
- Sort an array of associative arrays by column value
- Parse a multiline string with two delimiters per line into an array of associative rows
- Convert a string with 2 delimiters into a flat associative array
- How do I Split and filter 2 arrays
- PHP Array to List only values
- How to join / combine two arrays to concatenate into one array?
- Split a string by <br> tags wrapped in newlines
- Add prefix to array elements then join into a string
- Parse square brace tagged text into an associative array
- Parse an SQL CALL statement and create an array with its parameters
- How to insert an item at the beginning of an array in PHP?
- Parse inline style declaration string to array
- How to swap the positions of Min and Max in an array?
- Combining two arrays to form a JavaScript object
- Cannot memory-free strings from arbitrary-length array of strings
- Is a char array more efficient than a char pointer in C?
- Split the comma-separated string of a specified column in each row of a multidimensional array