-
@column alternative / is there an @column function?
Hi everyone, I've created a formula that I'd like to replicate over many columns. I know I can paste it and change the column number, but is there a way to instruct the formula use a numbered row (in this case, 20) and the current column? Something like @column =COUNTIFS({April/May 2021 Boot Raw Data Range 1}, [Primary…
-
Percentage of a number within a cell
In Excel, I can utilize the following formula to find the percentage of a number. Input =H5*30.1% Output $11,371.11 When H5=$37,777.78 However, in Smartsheet, the formula does not copy over and when the formula is manually entered in Smartsheet, it tells me that it is unparseable. Manually doing the calculation defeats the…
-
Countifs Formula where one value may be a fragment or a word (but I only want it counted as word)
Hello, I've written a countifs formula for the number of times certain words show up in a column on another sheet based on a date column in that sheet. The formula works fine for most words such as "Recruitment" or "Compliance". However, I have one value that is "NCE" and that's where I'm having the problem. I need to…
-
IF AND formula help
Hello, How do I use an "IF(AND(" formula to get a count of two conditional columns? I am working on calculating a weekly average and I have a date received column which I broke down by "week number:" and "year" in separate columns. However, I have two separate Smartsheets, one being the current task and the other being the…
-
If / then statements with subtraction problem
Hello everyone, I was hoping some of the pro's on here could help me figure something out. I'm creating a hours tracker for employees to use remotely and I need to calculate the overtime hours as anything over 8 or anything over 10 depending on the answer given in a column. So if [certified] = "Yes" then subtract 8 from…
-
Formula to automate a dashboard and reference previous / next cell
Hi, I am trying to build a formula to remove a manual step in a report. I'd need some guidance on how to do. In the screenshot below, I want to automate the % of change calculation. % of change = last week SLO - this week SLO. I think I can use the "Used in Dashboard?" column to build a formula but not really sure how to…
-
Two columns are checked then want separate column to show green
If one column is checked and one is not want to show yellow if both columns are unchecked want to show red.
-
SUMIF failure
I'm trying to improve a labor intensive process (40+ spreadsheets that do not interact) to calculate a a weekly production metric by Examiner. Basic workflow is that Examiners enter time worked in various categories daily, then once a week production numbers are added and the metric will be calculated. I created the sheet…
-
Is it possible? Have an IF formula to check certain boxes.
I have two columns Target Age and Target Grade. Can you have a formula that will look at the Target Age column and select the corresponding Target Grade. Both of them are multiple select drop down columns. If 5-7 is selected, then check the box K-2nd grade, 8-10 is selected, then check the box 3rd-5th grade, 11-13 is…
-
Formula is not Generating the correct value
So we have a formula in column labeled "Section 1 ended" - =IF([Column A]@row > 20000000, "10", IF([Column A]@row < 1000000, "2.5", IF([Column A)]@row > 10000000 < 20000000, "7.5"))) and it generates correctly. However, when I try to then use the value from that formula in Column B to multiply by 8 in another formula, it…