-
Calculating Duration (showing overages by number of days)
Hello, I am trying to run a duration formula based on the start and end dates on a project and the estimated dates. The estimate dates are how long we think the project will take, the start and end are the actual dates for the projects life. I also need to factor in a break down of each month, so in other words duration…
-
Return value from MATCH function
Hi all, I've got an INDEX/MATCH error that is stumping me. I have a lat/long identifier that I want to bring into a variety of other sheets. I am trying to match a street address to this identifier for ingestion into the target sheet. As you can see in the reference sheet screen shot, variations of input will have the same…
-
Count & If formula
I am trying to create a formula where I will get a count of total Solution Design by Zone. I can get the count for each separately but I can't get it to combine. Column 1 - Zone & Column 2 - "Solution Design" =COUNTIFS(Zone:Zone, ="West", "Solution Design":"Solution Design") Thank you in advance.
-
An "Average" formula which does not consider a cell with "zero" value
Hi Smartsheet Community, I'm looking for an "average" formula for a set of cells. However, some of the cells have a "zero" value and the formula musn't average across those cells, only ones which have a positive value. Please see an example below: Average of only cells which have a number - if the number is "0", then do…
-
Duplicate data formula which references two values
Hello Community, I am trying to find duplicate invoice numbers for the same client within two of my sheets. I need to show if there have been any duplicate invoices for clients using two criteria but am unsure how to create the formula. I want this information to show in a checkbox form. =IF(COUNTIF([Invoice #]:[Invoice…
-
How can I subtract a changing number of days from a date?
Hi there, I have a "Due Date" column and an "Order By Date" column. The Order By needs to reflect a date that is the Due Date minus our lead time (reflected in a third column and listed as a number of days). The issue is that the lead times change and vary from product to product. So I'm trying to create a formula that…
-
Is there a way to combine these two formulas? IF and NETDAYS, use of TODAY function
I would like to return a value based on the total number of Medicare Days used prior to admission, plus the total days elapsed since admission to calculate day 100, or benefit exhaust date. I've worked out the individual pieces but I'm having trouble combining the two. In the Days Remaining Column, I have this: =100 -…
-
Formula that changes status symbol based on check box checked/un-checked
I'm trying to create a formula that changes my status column to the red or green symbols. I looked at some examples on the site, but wasn't able to get it to work on my sheet. I was trying to set up a formula around the following parameters.. If check box is checked then green If check box is un-checked then red I have…
-
Networkday Formula Not Working Correctly
I'm using the networkday formula to gather the number of working days between two dates and then dividing that number by 5 to get the total number of weeks. However, the formula doesn't seem to provide the correct answer unless I change the dividing number to 4.7 (no idea why that would work since there's 5 days in a…
-
sum of parent rows only, below parent and child rows
Itemized budget rows, which are the sum of child rows. All data entered in child rows and totaled in the parent row. At bottom of page, I want to total the parent rows only. Any way to do this? Screen grab attached.