-
Conditional Weighted Average
Hello Experts! I see AVGW but I need an average formula to use with an If. Is there such a formula? I have this formula that works great for an average but is not a weighted average. '=AVERAGEIF([Task Name]:[Task Name], "«SPECIFIC TEXT I SEARCH FOR»", [% Complete]:[% Complete]) Anyone have a solution?
-
Formula/Function Help
Hello, I currently have a grid listing different project expenses. I have columns that show CapEx, OpEx, and Customer Funded. I put formulas in the sheet summary to show the total cost of all CapEx, total cost of all OpEx, total cost of all Customer Funded, and then the total cost between all three. All formulas are coming…
-
Can you red flag duplicate information in a report?
I have 12 regional sheets for a client that contains hundreds of data rows. Each row has a specific PO number from a client. Sometimes the client will mis-type a number and a duplicate PO is transcribed into my Smartsheet. Is there any way to red flag that a duplicate PO number has been entered?
-
Formula to get the last child row and apply conditional formatting
I have a sheet conditional formatting has been applied so that Ancestor = 0 is one color, Ancestor = 1 is another. Ideally, I'd like to apply conditional formatting so that all rows with Ancestor = 2 are conditionally formatted to the automatic format style except for the last row which will be a different color. I'm still…
-
Creating a Forumla
Hello, I am needing help with a formula. I have two sheets, one is a sheet where all status's are, the other is where I want the data to live. Is there a way to pull all items with the Accreditation Program and the Complete Status / the total number of items with Accreditation Program to create a % in that cell? I have it…
-
COUNTIF Not Counting Accurately
Good Evening, OBJECTIVE: To count every instance that has been selected with each cell for multi-option drop down Column AGE DISTRIBUTION. I have attempted the following formula's but neither will count all the instances throughout the column or it only outputs #UNPARSEABLE: Thanks taking the time to look at my problem,…
-
Simplify large if then statement.
Hello I have a large portfolio of projects. Each project can be in one 10 or so phases, and each phase can have 1 to 3 or 4 milestones during or at the end of the phase. I am trying to develop a sheet that will look at a project and the current phase, and confirm that the next milestone forecast makes sense. The challenge…
-
Show tasks that are happening today in a report
Hey! So, I'm on a construction company, and I need to make a report that shows the tasks happening today But I don't know how to show tasks that are not starting or finishing today, but are still in the process of completion. With these filters, if a task started yesterday and is finishing tomorrow, it will not show on…
-
Count entries per month and year
I have a dashboard that displays entries per month. Now that we are in a new year, I would like to separate the entries by month/year to do a rolling year display on my dashboard. My formula keeps coming up as unparseable. I'm counting the data in the Created Date column on another sheet. =COUNTIFS({Operations Escalation…
-
Circular Reference Error
I am trying to create a formula showing the utilisation of my Project Managers based on hours against projects and PTO vs. their full time contract hours. I am using the following formula for Project Manager 1: =SUMIF(Person:Person, "PM1", Aug:Aug) / 140 When I apply the same formula for PM 2 (substituting PM1 name for…