-
How to add "," between returned results
I'm using the below formula to pull all TR numbers (TRnumber) associated with specific IDs (LIMSID) from my master spreadsheet, but I'm not sure how to add a "," between each TR#. All suggestions appreciated! Thanks! =IF(ISBLANK(JOIN(DISTINCT(COLLECT({TRnumber}, {LIMSID}, [LIMS ID]@row)), CHAR(10))), "",…
-
Finding Average - Multiple Values in Single Cell
Hello! I am trying to find a formula where I can find the average test score of multiple scores entered into a single cell- see screenshot below. I need the average to autopopulate based off of the numbers entered into a single entry in Average Score. Thank you for the help,
-
Creating a mixed-type chart?
I have a large dataset which includes variables measured over time. I would love to be able to recreate the chart I have in Excel which represents the data as both a bar and a line. Here's what I did in Excel: Having one of the variables represented as a bar instead of a line makes for easier reading. Is this something…
-
Find the most recently passed Thursday based on Today's date
I want to count the number of project manager status update sheets that are not updated to the most recently passed Thursday. All the project managers are supposed to update their status update sheets every Thursday. On the following Sunday/Monday, I will check and see which status update sheets are not updated to the most…
-
Lookup from a multi-select column, find (collect) all matching values
Not sure if this is possible, but if it is, this group would know! I'm trying to lookup from my Requesting Department Column, which is a multi-select column, to another sheet that has the department head for each department, so that I can list their names in the "Responsible for" column (contact column). If there's only 1…
-
How can I use SUMIFS (or another function) to sum values if two rows match each other?
I am trying to add together the total quantity made on a certain date and time. For example, in the screenshot, I have two rows that contain 5/18/20 that occur on floorhour 5. Is there a formula I can write to add together the value from a column called QuantityMade only if the date and floor hour match? Thanks!
-
Date Formula Question
Hello, I am creating a formula to pull the month out and then if the date is blank to have the field be blank. Here is the formula I am using: =MONTH([Activity End Date]@row, IF(ISBLANK([Activity End Date]@row, " "))) However, I am receiving an "Invalid Data Type" response. I've attached a screen print. If the Activity End…
-
How would I create a formula which returns the Top 5 most frequent words in a column?
I have been tasked with creating metrics which show the current 'Top 5' most frequent countries in a column. I am struggling to create a formula which returns the correct value as new countries will be added to the list in future so COUNTIF would not work. So imagine the column read as follows: Germany France France…
-
Cells on Report not displaying data from source sheet.
I have a new report that I am working on and am having trouble with it because it is leaving cells blank that have data shown on source sheet. Below is a screenshot of the data displayed on the source sheet. That data is formula driven. PROJECT NAME COLUMN FORMULA: =IF([Parent Row]@row = 0, PARENT([Job Name]@row), [Job…
-
How to use COUNTIF in a Sheet Summary Report
Hello - I am trying to add several COUNTIF formulas for different lines in my Sheet Summary Report. I would like the formula to reference how many times the status Action, Risk, etc. are in the column. I only get INVALID REF or UNPARSEABLE. I have tried the below among others, thank you! =COUNTIF({Log Entry Type},…
