Reporting with Formulas

Using simple formula, you can get more valued results from result view. More detailed formula information will be continued on the next article. The formula will be included with the following functions.

*         Subtotal and Grand total

*         Average, Minimum, Maximum

*         Percent of total

*         Cumulative Percentage

*         Incremental values

*         Difference

*         Ranking

*         Standard Deviation and Standard Deviation P

*         Percentile, Percentile Excel

Below is a basic output of crosstab report. We will start from this report to show how to make your document more valuable results.

Percent of total

Let me talk about percent of total. You might see this is easy and there*s nothing to discuss to fill this document. But let*s see the difference with horizontal and vertical difference. The data shows your product category and subcategory have returned orders which shows on column area.

This is PoT of Sales by vertical summing up.

Compare with prior PoT of Sales by horizontal summing up.

Horizontal PoT shows what is a percentage value for each category that how much returned directly. And vertical PoT is correct but have a different meaning that how much is returned for each category level.

So you can get Technology Phones are the highest return sales, but from the second table shows Technology Copiers are the highest return ratio per sales.

Subtotal

Your development team or IT department might think this topic might be trivial and there is not much variety on this. Let me show several possible cases below with what business users expecting from your work.

Let*s start with basic with subtotal for each category.

Ok, now how you can see the subtotal first for each category like this.

Why you need to check every row for subtotal. Your business user wants to see the subtotal as a group on top or bottom.

More complex SubTotal

Let*s make more detailed business reports with information full of self instructions.