MEDIAN() Column formula keeps breaking.

tcal780

Hello Smart people,

I have a solution that uses four sheets.

The first, is an export of vehicle assignments from a vehicle management system. Data Shuttle overwrites all data in this sheet at 12:00AM each day.

The second sheet (Daily Rental Summary), is a sheet to aggregate daily totals. A workflow adds a new row to this sheet each day:

The third sheet (Daily Rental Summary Median Values), calculates a weekly Median value of the daily totals in the Daily Rental Summary sheet.

Example formula (Scissor Lift Column):

=MEDIAN(COLLECT({Daily Rental Summary - Scissor Lifts}, {Daily Rental Summary - Week Starting}, [Week Starting]@row))

The fourth sheet (Rental Metrics Reporting) is used to support a rolling 2 year timeframe graph.

Example formula (Scissor Lift Column):

=INDEX({Daily Rental Summary Median Values - Scissor Lift}, MATCH([Week Starting]@row, {Daily Rental Summary Median Values - Week Starting}, 0))

The fourth sheet data is presented in a graph on a dashboard:

Recently, the graph started breaking each day. On the third sheet, the Median() columns started showing #UNPARSEABLE:

After 5-10 seconds, the values would appear.

I'd like to have this run by itself, once again.

A couple things I've tried:

• Adding workflows to force an update on every row with today's date in a "Today" column.
• Combining the Sheet 3 MEDIAN() columns to Sheet 2.
• Revising the daily summary formulas from COUNTIFS() to COUNT(COLLECT())) as shown above.

At this point, I'm stuck. Any suggestions would be greatly appreciated.

Thanks!

Todd

Julio S.

Hi @tcal780,

It seems like something in your formula seems to be causing it to throw UNPARSEABLE errors which then propagates to the Dashboard Widget. If you can't find the formula or the references within being altered in any possible way that could be causing these errors, I'd suggest opening a ticket with our Support team via this form. If you do this, please be sure to specify the names or sheet IDs of each affected item including your Dashboard. Screenshots of the Cell History in any of the affected cells would also be of great help.

I hope that this can be of help.

Cheers!

Julio

Julio S.

Hi @tcal780,

It seems like something in your formula seems to be causing it to throw UNPARSEABLE errors which then propagates to the Dashboard Widget. If you can't find the formula or the references within being altered in any possible way that could be causing these errors, I'd suggest opening a ticket with our Support team via this form. If you do this, please be sure to specify the names or sheet IDs of each affected item including your Dashboard. Screenshots of the Cell History in any of the affected cells would also be of great help.

I hope that this can be of help.

Cheers!

Julio