[SOLVED] Pivot Table Divide By Zero

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

Thomas Moufet wrote:
> 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, "")

NewsMan <[email protected]> wrote:

>Thomas Moufet wrote:
>> 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, "")

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

Thomas Moufet wrote:
> NewsMan <[email protected]> wrote:
>
>
>>Thomas Moufet wrote:
>>
>>>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, "")
>
>
> 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.

NewsMan <[email protected]> wrote:

<snip earlier stuff to save space>

>> 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.

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

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


Thomas Moufet wrote:
> NewsMan <[email protected]> wrote:
>
> <snip earlier stuff to save space>
>
>>>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.
>
>
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html