-
Help with Average Formula
Hi there - I'd like to ask for a hand to calculate an average formula. This is a cross sheet formula where I want to aggregate an average time duration by Assigned To, by quarter. (I also have instances where my time duration is zero - I think that was causing me a divide by zero error). Metrics sheet/destination: Source…
-
Formula for calculation
Hi Guys, I'm stuck with one critical problem, where I need one formula. I have two sheets Sheet A (contains only applied leaves, not all the dates range) Column 1 - Leave Start Date, eg 01 jan 24 Column 2 - Leave End date, eg 03 jan 24 Column 3 - Employee code, eg ABC001 Sheet B (contains entire range of dates for…
-
Cross-sheet formulas
What formula can I use to look at the data in a cell on another worksheet, then populated the cell with a designated value based on the content? For example, I have a form with multiple choice options such as Excellent, Very good, Fair, Poor, etc…. I need to create a new sheet where the response of "Excellent" is changed…
-
Inspection tracking template - functions
Hello, I am using the Inspection tracking template and it includes the following function: =COUNTIFS({MED1}, "No", {Date}, MONTH(@cell ) = $[Month#]@row) I would like to measure the number of violations per room, rather than per month but I am having difficulty knowing how to change the {Date}, MONTH(@cell ) =…
-
NEED TO ADD DAYS TO THIS FORMULA IF ANOTHER CELL HAS SPECIFIC ITEMS OF A DROP DOWN.
I have this formula that works for this cell when information is missing from reference cell A. However now we want to be able to add additional days if reference cell B has specific items checked in a dropdown. I just can't seem to get it to work. Any suggestions. =IF(ISBLANK([ ENGINEER (tentative start date)]@row), "",…
-
#Divide by zero error for AVG COLLECT
My formula is giving an error. it works fine until I try to add the month. The month calculated field is a number/text field. What else could be the issue? =AVG(COLLECT({DYS to Completion}, {Application}, [Column5]@row, {Month}, =1))
-
INDEX(COLLECT()) function to pull partial data
I'm using an INDEX(COLLECT()) function to pull data from cross sheet reference; however, is there a way to pull partial data from the reference cell? For example, the reference cell is "Molecular Division" but I only want to pull "Molecular" into the target cell.
-
Please help: Timesheet/hour tracker
I'm building a simple hour tracker and struggling with the formula. I want the formula to only produce a value when the a date (column) is = > the start of a pay period (column) and < = the end of a pay period (column). I'm combining an already working formula that takes a row total (column name is Task Total) when a…
-
Showing Totals in Stacked Bar Chart
Hi there - Is there a way to show the total values for data in a stacked bar chart?
-
COUNTIF
I am trying to COUNTIF range 1 is yes then range 2 meets any of 3 criteria. A single criteria works =COUNTIFS([Current Status]:[Current Status], "Active", [Milestone/Deliverable Name]:[Milestone/Deliverable Name], "Parent Project") I need to add two additional criteria for the same second range. I want it to also count if…
