Average Summary in Report not showing % format

Kate Folwell

I have a report that is summarizing 4 columns of data in which the data is all formatted as %s.

As you can see, only one average presents as a %, and I would like all of the averages to present this way for stakeholders to read with ease. I have checked the summary fields these stats are pulling from to be sure they are formatted as %s(and these are present as %s in report as seen above). I can not find any reason why the first average stat presents correctly, and the others do not.

Any thoughts?

Thanks,

Kate

Genevieve P.

Hi @Kate Folwell

You noted that you checked the values to make sure they're formatted as %, but is it possible that even one of these Summary Fields is appending the % symbol as a text value next to the number? It's a little like a long string of Christmas lights... if even one light (or one cell) of the thread is out, then the summary at the top won't be able to format it as percent.

What formulas are you using in the Summary Fields?

Kate Folwell

Thanks, @Genevieve P.

The formulas in all of the cells that populate this chart are simple ratios like:

=[Project Total ID Days]# / [Project Total Actual Days on Completed Tasks]#.

However, these cells refer to other summary cells that contain formulas like these:

=SUMIFS([Actual Duration Days]:[Actual Duration Days], [Indent Level]:[Indent Level], >2, [ID Active Work]:[ID Active Work], 1) - SUM([ID Overlap A]:[ID Overlap A]) - SUM([ID Overlap B]:[ID Overlap B]) - SUM([ID Overlap C]:[ID Overlap C])

and

=SUMIF([Indent Level]:[Indent Level], >2, [Actual Duration Days]:[Actual Duration Days]) - SUM([ID Overlap A]:[ID Overlap A]) - SUM([ID Overlap B]:[ID Overlap B]) - SUM([ID Overlap C]:[ID Overlap C]) - SUM([CB Overlap A]:[CB Overlap A]) - SUM([CB Overlap B]:[CB Overlap B]) - SUM([CB Overlap C]:[CB Overlap C]) - SUM([MT to MT Overlap A]:[MT to MT Overlap A]) - SUM([MT to MT Overlap B]:[MT to MT Overlap B]) - SUM([MT to MT Overlap C]:[MT to MT Overlap C]).

These summary cells are all formatted the same way, with no deliberately chosen format- so general I assume. It is a bit of a head-scratcher as to why one "string of lights" produced a % and the others did not(great metaphor, btw). Nevertheless, I went back and changed all of the summary "lights" feeding into this output to a number format. Same results.

I think you seem to be suggesting that I then go to the sheet info cells that feed into the initial summary cells and change all of these to number formats. I could do that, but again, before I take that on, all of the "strings" are formatted the same way, so I am questioning whether this will change anything or if this is just a flukey thing due to the complexity of the layered formulas (the sheet cells also contain formulas).

Thanks again for weighing in,

Kate

Genevieve P.

Hi @Kate Folwell

No, I don't think there's a need to check the other formulas or the reference cells. What matters is the final output and how that's configured in the Sheet Summary before the Report.

If they're all set to Numbers and % formatting then this should reflect in the Report. You're right that it's odd where one Report column works and all the others don't. I'm very curious to know what's different about the first column.

Can I ask you to test something? Could you create a Report with only one of the other columns selected, so it's not all 4 in one, and see if it's able to recognize those cells as % if it's on its own?

Thanks!

Genevieve

TlittleJL

I'm having a very similar issue. I've gone through and re-typed each of these values to ensure they're input correctly. I have this report grouped and the other 3 groups have this column total as a % (as you can see shown at the bottom of my screengrab). But for some reason, for these 5 cells, I can't get it to roll up into an actual percentage - which is making it so that my overall total for the column is not showing as a percentage.