-
Need cell to populate with fixed percentage amount of previous cell
Hi all, I am having trouble doing something so simple, I think I'm making it harder for myself. I have two columns, "Hard Job Cost" and "Commission", as I get a percent of the job as commission. So, if the "Hard Job Cost" is $30,000, my commission is 1.5%. I can't seem to figure out how the "Commission" column shows the…
-
How to Calculate Percent Complete in Sheet Summary
My sheet is used as a task checklist for projects. It currently has a 'Status' column for each Child task with 4 dropdowns (Not Started, In Process, Complete, N/A). I am trying to create a percentage in Sheet Summary that calculates the number of fields that are marked 'Complete' in the selected cells. Pulled this formula…
-
Trying to flag an item within a date range.
I am using the Asset Management template set and need to have the dashboard update when an item is due within 7 days of the assigned due date. I tried the below formula: =COUNTIF({Sheet - Asset Tracker Range 2}, TODAY() + 7) Asset Tracker Range 2 is the assigned due date. This is giving me everything due exactly 7 days…
-
How do I count the reason for the consult, AND the year it took place?
I tried to amend this formula to report by year. Below is the formula I tried, which didn't work. The reasons are part of a drop down on another sheet. The consult date is on the same sheet, but the full date is in the cell, 10/12/2017, 11/1/2017, etc. My current formula in the TOTALS to DATE is: =COUNTIF({Reason for…
-
Return value if date falls within range
I have researched others' questions on this and I feel this should be right, but it's not. I'm trying to return the generation based on DOB: '=IF(DOB@row <= DATE(1943,12,31), "Traditionalists", IF(AND(DOB@row >= DATE(1944, 1, 1), DOB@row <= DATE(1959,12,31)), "Baby Boomers", IF(AND(DOB@row >= DATE(1960, 1, 1), DOB@row <=…
-
How to write formula to display health based on baseline variance
Hello! I'm trying to write a formula that looks at the baseline variance and displays the health status based on greater than - less than criteria. We need the formula to read if variance is greater than or equal to "0", "green", if the variance is less than 0 but more than or equal to -9, "yellow" and finally if the…
-
How to count date occurrences within a month
I have a sheet that identifies the date of new survey submissions and the date of responses. I am looking to count the dates that occur within specific months/years for a report... Here is what I am using, which is currently giving me a response of 0, though I know there are multiple occurrences for September. The ranges…
-
Sumifs with dynamic criteria from multi-select dropdown
I have Sheet 1, having rows where a record can be assigned values from a Multi-Select Dropdown with Requirement IDs (REQ1, REQ2, REQ5). I have a separate, Sheet 2 with Requirements. Each Requirement has an ID and a Weight. ID, Weight REQ 1, 5 REQ2, 5 ... REQ5, 3 I want to assign REQ1, REQ2, REQ5 to a record in Sheet 1 and…
-
Count if Expiration Date is in the next 3, 14, 30 Days without counting Past Expiration Dates
My Formula is "=COUNTIF({Expiration Date}, <TODAY(3))" switching out the number for the appropriate timeframe. When I do a filter on the sheet it is referencing to there is only 1 expiring in the next 3 days however the formula keeps giving me an answer of 2. I am unsure if it is counting the 2 that are past the expiration…
-
How to pull data from another sheet based on a specific criteria
Hello, I have one sheet that includes all of our projects listed in rows and I need to create specific sheets outlining the tasks for each of the rows. The new sheet should also include specific data (such as dates, names, etc.) from the first sheet. I'd like to be able to link data from specific cells from that first…
