Why won't AVG calculate correctly?

Josh G.

I have a sheet that includes clients, purchased products and the discounts given. On a helper sheet, I am trying to calculate the average of the discounts per type of product. In the helper sheet I select the column containing the discounts and wrap it in the AVG formula. The formula works for one of the products but not the rest. I noticed the product discounts that won't calculate in the AVG formula are in the inside left of the cell. The product discounts that will calculate are to the inside right of the cell. All columns are text/number. Anyone know what could be causing this and preventing the formula from working correctly in all instances? Thanks.

Kelly Moore

Hey @Josh G.

It sounds like you have a mixture of real numbers and text that looks like numbers. What formula are you using to generate the data in that column? Can you wrap that formula with a VALUE

=VALUE(the entire formula you are using)

Does that work?

Kelly

Josh G.

Hi @Kelly Moore,

Thanks for the suggestion, but VALUE doesn't work. What I discovered, and can't figure out how it happened since I'm using Data Shuttle to upload the data, is that certain discounts had an apostrophe before the discount. Therefore, the AVG formula didn't like that. I have no idea how those apostrophes came to be since I have set up a global sheet of information for all my clients and use Data Shuttle to transfer client info to their own sheets. Then each client sheet has a helper sheet that I use to make calculations and drive the dashboard.

Kelly Moore

Hey @Josh G.

There may be other fixes for your dilemma, but one work around is adding a helper column and average off of that column. You might be able to do this within Datashuttle by inserting the expression but I typically like to see my formulas happen to ensure they are working correctly.

In a helper column, you could try this. The CHAR(39) is the ASCII code for an apostrophe. The formula looks to see if an apostrophe exists, if it does the FIND formula would register a value. If it doesn't find it then FIND returns a zero. If found, the SUBSTITUTE function strips the apostrophe out, if not found it inserts the original value.

=IF(FIND(CHAR(39), [your current average column]@row) > 0, VALUE(SUBSTITUTE([your current average column]@row, CHAR(39), "")), [your current average column]@row)

There may be posts about characters being inserted during datashuttle upload. Certainly fixing the real root cause is the best solution - mine formula is just a quick fix work around.

I'll be interested if you find a Datashuttle fix - I use datashuttle all the time and therefore I might stumble on this in one of my projects at some time.

Kelly