-
Calculating Age from DOB Column
Group, I’m calculating people's age on their birthday this year using this formula: =YEAR(TODAY()) - YEAR(DOB@row) It works for most cases, but for some reason, it returns a negative number for people born in the 1940s. See the screenshot for details. Any idea what I'm doing wrong? As always, thank you.
-
Help with SUMIFS
I am trying to collect the numerical value based on whether or not work was completed between two dates by a specific employee that was assigned to the work, using SUMIFS. =SUMIFS({Numerical Size 1}, {Date Task 1 Complete}, >={Week 1 Start}, {Date Task 1 Complete}, <={Week 1 End}, {Assigned To}, Employee@row) ALL results…
-
Index Match
Hello All, =IFERROR((INDEX((COLLECT({Annabelle Pruned}, {Client Name (Pruning)}, [Client Name]@row, {Annabelle Name}, "Annabelle"),1),0) This formula I've used (with the help of the pro desk) elswhere under the same circumstances as the one above: Anabelle pruned is a Boolean, Annabelle Name is a text box. The error is…
-
How to get my numbers to correctly add up?!
Hello everyone, I am tasked with tracking budget moving forward and would like to be able to track overall budget but also budget for each POs. I would like to have the formula to add the Children's Children's (if that makes sense) and ignore the Total of the PO. This way I can have the total of the PO's in one line but…
-
Confused on Index Collect, Join
i have a sheet with a column with the number of records matching a code number (CodeFM) from another sheet. I have summarized the number of records for each code number (along with the code description in a separate sheet from the large master sheet containing the data). All is well here. At the bottom of the this second…
-
SUM IF / COLLECT / AND all are failing
I have tried several different ways to do what I thought should be a simple calculation. The purpose is to add Hours from one column where the Priority in a second column is either 1, 2, or 3. For perspective there are 300+ rows in the sheet and it grows daily. There are 8 possible priority settings, one is text. I have…
-
What's your Best Error Checking Column Using Formula?
I have discovered multiple ways to create a error checking column that looks in all columns to see if there's a broken formula somewhere. However, all the ways I have found aren't very elegant and don't provide the type of details I want. Example: =IF(ISERROR([First Column in Sheet]@row:[Last Column in Sheet]@row), 1) This…
-
Formula to capture count onto dashboard
I am attempting to create a chart on my dashboard that shows how many reports an individual has completed within the year. I created a new sheet, and use the =countif formula within the cell but keep getting an #UNPARSABLE error. I reference another sheet where the data lives, selecting the column where the assignee is…
-
Formula to make a metrics sheet to build a pipleline dashboard
I have a sales tracker sheet and need to continuously pull data as new quotes are formed to show wins/ losses compared to our target in a dashboard. Sheet has 3 columns that need to create the formula: Account type, Weighted Forecast, and Expected Quarter Close. In real words, I want to say Weighted forescast is $1M for…
-
Formula that returns a value equal to every other day
Date formulas are the most challenging for me. The requirement is to provide a visual calendar that will trigger the quality audit team to complete the audits expected for the current business day. The sheet has three frequency options: Daily, every other day, and weekly. I am struggling to determine the formula for every…