-
Can you check / help with my IF, AND, NOT ISBLANK formula
Hi all, This is my first post :-) Hoping for some help with a long IF formula. I have sheet that has columns with dates in them Launch Date, then Nomination Deadline, then Survey Deadline - in that timeline, so there won't be a survey report deadline after a launch deadline. Sometimes these dates are not filled in. I am…
-
Formula for a PASS or FAIL
I would like to add a formula to the sheet that shows a Pass or a Fail depending on three other columns. If Mold Present column say "No" & the Seeds per 100g column says "None" or & the Foreign Matter column says "No" mark the cell as a PASS. If Mold Present column says "Yes - Beige" or "Yes-Grey" or "Yes-Black" & the…
-
Can I copy rows based on a formula updating?
Hi All - I'm working on new process, and have everything working except one small (seemingly) problem. I have two sheets that talk to each other, and they work fine. In Sheet 1, I have a status field (drop down), and in Sheet 2 there is corresponding Status field. When Sheet 1's Status field gets changed, Sheet 2's Status…
-
Calculate the right average for the columns
I need the average collect to calculate the right total % complete for the columns Each column that has a date is = 100% If there is no date is = 0% but the formula still counts the 0% to give the total % average complete on "Quote % Complete" column. If there is "N/A" either in "1st Circuit Quote Status or 2nd Circuit…
-
Does INDEX(MATCH) work on a column with a formula?
I am attempting to use the INDEX(MATCH) formula with the range being a column that is calculated with a formula. I keep getting a #NOMATCH message - can MATCH only work on a column that has actual values (i.e., not values calculated from a formula)? I am trying to use this formula: =INDEX({Billing Code}, MATCH([Identifier…
-
Need formula help.
I have the following formula: =IFERROR(IF([Date Complete]@row = [Due Date]@row, 0, IF([Date Complete]@row > [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) - 1, IF([Date Complete]@row < [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) + 1, ""))), "") How do I add a contingency for if the…
-
Subtracting 1 month from TODAY, previous year error
=DATE(YEAR(day13), MONTH(day13) - 1, DAY(day13)) I am using the above formula to subtract one month from today's date for a rolling 12 month report. However, the formula errors above 15/01/22 (I expected it to revert to 15/12/21) is there a way I can build the formula so it does not error and continues into the previous…
-
Trying to check a box using OR
I want a box to be checked IF Sales Status is "Verbal Agreement" or "Deployment Prep Call - Scheduling" or "Deployment Prep Call - Scheduled" =IF([Sales Status]@row = OR("Verbal Agreement", "Deployment Prep Call - Scheduling", "Deployment Prep Call - Scheduled", 1)) I'm getting the error INCORRECT ARGUMENT SET. Thanks for…
-
Find the average of hours, minutes, seconds
Hi there, I am having difficulties finding the average of hours, minutes and seconds. I have 12 months of data for timings, and the format is in hours, minutes, seconds how it is in excel - 00:00:00. I have to find the average for each month, for an entire years worth of data, and it gives me an error when I look for the…
-
Joining multiple COUNTIFS statements
Hi there. I'm trying to create a single formula that will count items in a column on sheet A if they fall between two dates and also count items on sheet B if they fall between two dates. The item name is the same in both sheets and the date ranges are the same in both sheets. The formula I have (which does not work) looks…
