-
Average of Pass/Fails, not percentages
Hi, I have a sheet that gives a "score" of Pass or Fail. Multiple rows per user. I originally had percentages as the score and we created a metric sheet that gathered the scores and gave an average per user per month. Since we changed it to pass/fail per score the metric now reads #DIVIDE BY ZERO ...is there a way to still…
-
Pull in only Mondays and count rows for that week
I have a grid with Order data, one order per row. Each order has an order date. In the Orders grid Sheet Summary, I have the following formula for Weekly Total to capture the last full week of data: =COUNTIF(Date:Date, AND((IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1))) In a separate grid or report, there are…
-
@cell Reference for Whole Column
I tried to create a formula with a structure like COUNTIF(column:column, YEAR(@cell)=2021) and was getting an error. I realized that if I changed to COUNTIF(column1:column23, YEAR(@cell)=2021), the formula would work. It seems that the @cell reference does not work when a whole column is selected. Does anyone have a…
-
Nested Formula using BLANK and MONTH lookup
I want my query to look up the Finish column to determine what month it should display, 1 = January, 2 = February, etc... but if its blank, I want a blank to appear. Right now it says "Invalid Data Type". Can you help fix this query: =IF(Finish@row) = " ", " ", IF(MONTH(Finish@row) = 1, "January", IF(MONTH(Finish@row) = 2,…
-
Compare dates in two different sheets and return a value
I have a reporting use case where the project managers have to enter a reporting code so that reporting roll up occurs in the correct fiscal quarter. I want to automate the assignment of the reporting code so project managers do not have enter the code . I created a sheet that contains a reporting code for fiscal years and…
-
How to Force Cell Alignments?
I'm working with SmartSheet to make a spreadsheet and I've noticed that the column formatting controls do not consistently apply formatting such as centering cell content. Please see the attached images for what I'm looking at in some example cells I made. The text in these cells is left-aligned even though you can see in…
-
Copying select information from a row to another sheet
Is there a way to auto copy selected columns to a single budgeting sheet without manual cell linking? We use multiple sheets to plan and track our ongoing projects but need key pieces of information brought over to a master sheet for budgeting. The individual sheets are used by our marketing department for planning events…
-
Formula issue comparing dates
First, this community is GREAT, and has really helped me with understanding formulas, so I'm hoping someone can help with a frustrating error. I have a list of projects where I want to designate "Y" if active in current year. I have start and end date columns and I my formula works when I have both the start and the end…
-
Calculating average time spent in each phase
I am looking to create a sheet that moves through our 5 stages of work and how much time is spent in each phase. What is the best way to go about this?
-
Including NOT(ISBLANK(... In Formula
Hello, I'm having difficulty including in a IF(OR formula that is looking for a not blank column. This column is intended to be a helper to build a report which incorporates both AND and OR statements. I'm trying to indicate a "green" status if any one of the variables are true, but I'm getting stuck on looking for a…
