Pivot Table Divide By Zero

[Thomas Moufet]

I am trying to make a sales report showing customer name, billed
revenue, cost, profit and percentage of profit (or loss) on each of
the jobs my company handled for the year 2004. I then want to make a
pivot table, showing the average profit we made on a per-customer
basis. Sadly, in some cases our costs exceeded the revenue billed, and
we consequently lost money on them. Life is tough, but that is not my
current problem.

The problem is those jobs where we were unable to bill the customer
any money at all (due to a failure on our part), yet we still incurred
costs that have to be paid. Those lines have a zero in them, causing a
"#DIV/0!" to show up in my spreadsheet. This consequently screws up my
pivot table.

Does anyone know of a workaround for this? I guess I could go through
the lines one by one and change the zero to a single penny, but I
suspect there might be a better solution.

TM

 

[NewsMan]

I am trying to make a sales report showing customer name, billed
revenue, cost, profit and percentage of profit (or loss) on each of
the jobs my company handled for the year 2004. I then want to make a
pivot table, showing the average profit we made on a per-customer
basis. Sadly, in some cases our costs exceeded the revenue billed, and
we consequently lost money on them. Life is tough, but that is not my
current problem.

The problem is those jobs where we were unable to bill the customer
any money at all (due to a failure on our part), yet we still incurred
costs that have to be paid. Those lines have a zero in them, causing a
"#DIV/0!" to show up in my spreadsheet. This consequently screws up my
pivot table.

Does anyone know of a workaround for this? I guess I could go through
the lines one by one and change the zero to a single penny, but I
suspect there might be a better solution.

TM

You could change your formula from

=A1/B1

to

=IF(B1, A1/B1, "")

 

[Thomas Moufet]

You could change your formula from

=A1/B1

to

=IF(B1, A1/B1, "")

Well, heck, I don't understand what that formula does, and
although it does eliminate the divide by zero problem,
it doesn't appear to yield the correct percentage of profit.

Moreover, I seem to have outlined my problem incorrectly. What
I want to get is an average percent of profit per customer.

Can anyone point me in the right direction? The pivot table result
doesn't give me an *average* per customer, no matter how I
(mistakenly) ask for it.

TM

 

[NewsMan]

Well, heck, I don't understand what that formula does, and
although it does eliminate the divide by zero problem,
it doesn't appear to yield the correct percentage of profit.

Moreover, I seem to have outlined my problem incorrectly. What
I want to get is an average percent of profit per customer.

Can anyone point me in the right direction? The pivot table result
doesn't give me an *average* per customer, no matter how I
(mistakenly) ask for it.

TM

perhaps you could provide an example of what you are doing and where the
DIV/0 problem happens.

 

[Thomas Moufet]

perhaps you could provide an example of what you are doing and where the
DIV/0 problem happens.

Sure, sorry I have been unclear so far. Let's say I have some 5,000
lines in a spreadsheet, indicating all the jobs we performed last
year. The column headings & example lines are, say,

Customer Revenue Cost Profit Percent Profit

Cust#1 144.75 108.68 36.07 25% (d1/b1)
Cust#2 0 205.00 -205.00 #DIV/0!

And repeat for 5K lines. I can eliminate the few #DIV/0! lines by
changing the revenue to .01 cents, I guess. Then the DIV/01 would
change to a minus 205% or so in the example above.

There are some 300 different customers whose revenue is shown in the
5,000 total lines, each line indicating profit and/or loss on the
individual jobs. I then want to make a Pivot Table to determine which
customers have been the most profitable, and which the least so.

I click on Data + Pivot Table (accepting the default ranges with Next,
Next and Finish until the Wizard box pops up) and drag Customer to the
far left block (Row Fields), and Percent Profit to the big box to the
right of that one (Data Items) and close the Pivot Table Wizard.

Next I right-click on any number in what is now column B (column A has
all the customer names listed alphabetically down the left side),
click on Field Settings, Average and OK. But that does not appear to
result in the average profit percent for the customers in column A.
What number DOES result, I cannot figure out, but it is definitely not
what I was hoping for.

How to get the average profit per customer for all the jobs performed
in the period being measured?

TM

 

[Debra Dalgleish]

Instead of calculating the Percent Profit in the source table, you could
calculate it in the Pivot Table:

Select a cell in the Pivot Table
From the Pivot toolbar, choose PivotTable>Formulas>Calculated Field
Type a name, e.g. PctProfit
Enter a formula: =Profit /Revenue
Click OK

Format the field as Percent