-
Average Children they are not 0
Hello - We are building a tracker that rolls up averages for a series of values across child rows. We have a top level value, with 12 children for each month, and each month as children rows of workers. So for example Manager Indent(January) Indent(Indent(Worker 1) We are trying to roll up for the manager, by month, as…
-
How to create a conditional formula with date specifications?
How do I create a formula that counts the number of rows containing [STATUS] “Green” and have [DATE] = Today and [DATE] = Last Seven Days from Today. I do not want to capture rows that are older than seven days from today.
-
Plotting cost over time
Hi- I am trying to either graphically or with subtotals display cost over time as aggregated preferably by month, but also want to consider by week. I am easily able to plot cost for items that begin or end in a calendar month, but not for items that span months. I am pretty sure each cost would need to be broken down into…
-
Help IF/AND formula with RYG conditional formatting
How do you create RYG symbol formula that requires multiple conditions of multiple reference cells? Here are the conditions I'm trying to write a RYG formula for....using reference cells [LEADER NAME] and [RISK FLAG]: If [LEADER NAME] = BLANK, then keep cell empty If [LEADER NAME] = NOT BLANK, AND [RISK FLAG] = CHECKED,…
-
Autopopulate text based on drop down option
Hello! I'm trying to figure out a formula to autopopulate text in a cell based on a drop down option in a previous cell. Basically one column would contain the drop down (Campus) and based on which campus is selected, a different cell (account) would autopopulate. Ideally it would look like this: Campus (drop down) Account…
-
Marking Oldest 5 Projects
Hello! I have been working with some data and I want to eventually be able to pull a report of my "past due" projects and show the oldest 5 projects. To do this, I have created a helper column in my source sheet that using the formula: =IF(Date@row < SMALL(Date:Date, 6), 1, 0) in order to flag the 5 oldest dates in my due…
-
Summing Data From Another Sheet
I have a resource tracker that is set up to sum hours by resource using the =SUM(CHILDREN()) Function. I also need to be able to sum hours by Project Code (Column 4), and Classification (Column 8) by Month. Can someone advise on the best way to approach this? My first thought was to use a SUMIFS formula -- =SUMIFS([Jan…
-
Skip blank cells with YEAR function
I’m endeavoring a roll up sheet that will provide sum totals from a single row on a separate Planning Request sheet. If the source sheet has all cells filled out, the formula seems to work fine: =SUMIFS({Planning Request Range 2}, {Planning Request Range 1}, [Member]1, {Planning Request Range 3}, "Approved", {Planning…
-
Task auto-assignment breaking when projects are manually reassigned
I have a form that the team uses to request work. Once the form is submitted, a vlookup assigns the task based on criteria entered in the form, and that team-member is notified of the work request. Works great. However, the manager of the team sometimes has to manually re-assign work due to work-load issues, etc. When she…
-
COUNTIF EXCEPT
Hello, could you help me find a formula that counts all except "x". I have a Column that has data such as: dates, "ok" and "N/A". I try using the Countif formula but cant seem to figure how to have it count all except the "N/A" information. I´d appreciate the help. Thank you!