Formula is #unparseable and not cleat why, help?

Katherine Camacho

=IFERROR(AVG(COLLECT({Score BA}, {Employee Email BA}, $Variable@row, {Date of Review BA}, ISDATE(@cell), {Date of Review BA}, YEAR(@cell) = $[Last Year]$102, {Date of Review BA}, MONTH(@cell) = [Last Year Feb]102), COLLECT({Archive Score}, {Archive Employee email}, $Variable@row, {Archive date of review}, ISDATE(@cell), {Archive date or review}, YEAR(@cell) = $[Last Year]$102, {Archive date of review}, MONTH(@cell) = [Last Year Feb]102))"")

Kelly Moore

@Katherine Camacho

Copy your formula in another cell or save your sheet so that your formula is in recorded in your Activity Log (right hand ribbon) so you can retrieve the first half of your formula later. Remove everything from your formula except the Archive section. You have indicated this is where your problem is. You should be working with this:

=AVG(COLLECT(just the archive ranges and criteria))

The colored formula should show up when you click inside the formula. Match the colored parentheses to make sure you have opening and closing parentheses. Your final parenthesis should be blue (always). Click in each cross sheet reference range (used Edit reference). Verify that the entire column is selected and it is the correct column. It is important to get this Archive section of the formula working before you attempt to add it back to the rest of the formula.

Kelly Moore

Hey @Katherine Camacho

Try adding a comma before your double-quotes at the end of your formula

Does that work?

Kelly

Katherine Camacho

Morning @Kelly Moore , It switched it to #incorrect argument

Kelly Moore

@Katherine Camacho

Did you try your AVG formula first without adding your IFERROR to it? One by one, test both of your COLLECTs with the AVG function and without the IFERROR. This will see which of the COLLECTS is throwing the error.

Also, is your formula column a text/number column? Screenshots of your source sheets will be helpful (with no sensitive info)

Kelly

Katherine Camacho

It wasnt unparseable until i added the second half of the formula (archive info) the first half worked fine prior. and yes it is in text/number @Kelly Moore

Kelly Moore

@Katherine Camacho

Copy your formula in another cell or save your sheet so that your formula is in recorded in your Activity Log (right hand ribbon) so you can retrieve the first half of your formula later. Remove everything from your formula except the Archive section. You have indicated this is where your problem is. You should be working with this:

=AVG(COLLECT(just the archive ranges and criteria))

The colored formula should show up when you click inside the formula. Match the colored parentheses to make sure you have opening and closing parentheses. Your final parenthesis should be blue (always). Click in each cross sheet reference range (used Edit reference). Verify that the entire column is selected and it is the correct column. It is important to get this Archive section of the formula working before you attempt to add it back to the rest of the formula.