Fixing #DIV/0 in Pivot Table average and Presenting sum of days and average of months.

[jacof]

Hi. The post title might not be too clear, but what I want to do is the following:

I have a Pivot Table which presents for each day, month and year, the daily expenses.

In another column, I have an "Average" field, which presents the daily average expense in the "Month" row, and the monthly average expense in the "Year" row. This is standard.

What I don't want, is excel displaying the average for the days also. I mean, it's ok that the average for the months row should be the daily average, and the average in the year's row should be the monthly average, but it doesn't make sense presenting the "daily" average. It's not useful and I would like it to display the SUM for each day.

This is shown using the following example:

Expenses Day 1: 1000
Expenses Day 1: 2000
Expenses Day 2: 1000
Expenses Day 3: 0


Excel does the following:

Year 1 - Total Expense: 5000 - Average Expense: 1333.
--Month 1 - Total Expense: 5000 - Average Expense: 1333.
----Day 1 - Total Expense: 3000 - Average Expense: 1500.
----Day 2 - Total Expense: 1000 - Average Expense: 1000.
----Day 3 - Total Expense: 1000 - Average Expense: #¡DIV/0!.

The monthtly average is correct, since it sums up every daily expense and averages it. The yearly average is correct as well. The daily average, however, presents two flaws that I want to avoid:

1- It presents #¡DIV/0! when there's 0 expenses, where it should be simply 0.

2- It presents the daily average, which makes mathematical sense but no practical sense at all. I want to change this to a daily sum, a monthly average, and a yearly average. This could be acomplished using the following formula:

= (TOTAL EXPENSES) / (NUMBER OF DAYS)

Instead of *divided by (number of expenses). This solution would give a proper average, and also remove the #¡DIV/0! problem. It would return the simple sum for days, and the average for months. Is it possible to implement this using a calculated field? I tried to retrieve a column for "Number of days" but it just displays "Count of days" (Where Day 1 would have "2")

If this is not possible, I would like to hide the daily averages, and just use the monthly, and yearly. Is this possible?


Thank you very much.

 

[jacof]

How do I change the query so its according to my own tables? (those are Fecha, Descripción, Ingresos, Gastos) (Gastos = Expenses)

I tried modifying it in I think all possible ways.

 

[Marcelo Branco]

Please, post a small sample of your data

Select 5 rows including headers, put borders, copy (Ctrl+C), and paste (ctrl+V) in the forum reply-page

M.

 

[Marcelo Branco]

How do I change the query so its according to my own tables? (those are Fecha, Descripción, Ingresos, Gastos) (Gastos = Expenses)

I tried modifying it in I think all possible ways.

Jacof,

These are headers of your PVT. We need the field-names of your raw data, not the PVT.

You have to name the raw-data range as MyData not the PVT
Select your data and in the name-box (beside formula-bar) type MyData and hit Enter

M.

 

[jacof]

Oh not at all sir. The raw data from which the PVT comes from is structured in 4 columns. Fecha, Descripción, Ingresos, Gastos. Importing the file from Microsoft Query returns my raw data in the Microsoft Query window, but the Query itself gives an error "You need at least 4 arguments"

 

[Marcelo Branco]

Oh not at all sir. The raw data from which the PVT comes from is structured in 4 columns. Fecha, Descripción, Ingresos, Gastos. Importing the file from Microsoft Query returns my raw data in the Microsoft Query window, but the Query itself gives an error "You need at least 4 arguments"

Could i see how is the raw-data structered? Date-format (fecha), etc.
5 rows is enough

M.

 

[jacof]

Certainly. As I described:

Fecha	Descripción	Ingresos	Gastos
02/05/2011	ABONO LIN.CDTO.	1.000	
02/05/2011	CARGO REC. CELULAR MOVISTAR 07-4146663.		1.000
03/05/2011	ABONO LIN.CDTO.	31.400	
03/05/2011	CARGO HACIA BANCO BCI.		31.400

How do you paste the values so they keep their format, by the way?

I'm going for now... Its a school day for me tomorrow. Could we continue this tomorrow? Thank you for your kind help.

 

[Marcelo Branco]

Maybe this (trying to guess)

SELECT MyData.Fecha, MyData.Descripción, MyData.Ingresos, Sum(MyData.Gastos)
FROM MyData MyData
GROUP BY MyData.Fecha, MyData.Descripción, MyData.Ingresos, MyData.Gastos

 

[Marcelo Branco]

Ok
Its late for me too
Tomorrow we try

M.

 

[Marcelo Branco]

My last shot

SELECT MyData.Fecha, SUM(MyData.Gastos)
FROM MyData
GROUP BY MyData.Fecha

M.