Pivottable chaos: blank cells return 0, empty cells return nothing

[medexcel]

Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is
=IF((OR((C2=""),(D2=""))),"",C2/D2) so that if either the denominator or the numerator is missing, the rate will be blank. This gives a table thus:

..A...........B.............C.............D...........E...
..CLINIC......MONTH.........KIDPOX........ALL CASES...RATE
1 Doctorville.Jan 08........100...........200.........50%
2 Nurstown....Jan 08..........0...........300..........0%
3 Doctorville.Feb 08........100
4 Nurstown....Feb 08........200
5 Doctorville.Mar 08

I need to display this data as a pivottable thus:

SUM OF RATE ....MONTH
CLINIC .........Jan 08..... Feb 08.....Mar 08
Doctorsville... 50%
Nursetown .......0%

But when I do the pivottable function the resulting pivottable looks like this:

SUM OF RATE ....MONTH
CLINIC .........Jan 08.....Feb 08.....Mar 08
Doctorsville... 50%........0%.........0%....
Nursetown .......0%........0%

The problem seems to be that even though cells E3, E4 and E5 are blank in the original data table, they aren't actually empty, because they have a formula that returns "" if the formula doesn't give a real number. And it seems that this is responsible for returning the value "0" in the pivottable, making it look like the rate in the clinic is 0% when in fact it's uncalculable as either the denominator or numerator are missing. That's misleading, as I need to be able to tell from the pivottable whether the kidpox rate is 0% or whether it is not calculable owing to missing data in either column C or column D.

If I manually delele the formula in cells E3 to E5 and refresh the pivottable I get displayed what I need, which feeds my suspicion that the presence of a formula that returns "" is somehow messing things up. But I have several thousand rows of data so in reality manual deletion isn't an option.

Many thanks for your suggestions.

 

[Andrew Poulsom]

If can can live with having #DIV/0! in column E with the formula =C2/D2, you can achieve what you want by checking "For error values, show:" under Table Options.

 

[RoryA]

You could also use a calculated field in the table set to:
='KIDPOX'/'ALL CASES'
and then use the error hiding option that Andrew suggested. Saves having errors shown in your underlying data.

 

[medexcel]

If can can live with having #DIV/0! in column E with the formula =C2/D2, you can achieve what you want by checking "For error values, show:" under Table Options.

Thanks very much Andrew, I hadn't considered that possibility and it would certainly simplify the formulae if it works.

As for calculated fields, you may well ask why I didn't go for that approach to start with (the underlying data table is imported from an SQL database, and has additional columns on the right of the last data column in which the formulae are found). The reason is, I presented a gross simplification of the data table, which in reality has around 30,000 rows and 150 columns, 130 of which are from the SQL database and 20 are additional columns performing calculations. This rotates into a pivottable approximately 50,000 rows deep and 120 columns across, with about 145 datafields.

I did try calculated fields but above several thousand rows, it seems it just can't cope and for whatever bizarre reason they don't appear in the pivottable. If anyone knows how to overcome that issue, I'd welcome ideas too, though I did post that question earlier without any satisfactory response.

Incidentally that's why I use Excel rather than Openoffice Calc, which maxes out at I think 8 datafields in its pivottables. For 145 datafields and growing, Excel pivottables are the only show in town.

In reality the pivottable is a hidden worksheet. With the functions offset, index and match that I learnt on this wonderfully informative website, the end user simply has to select, using a few "forms", one of 145 different health indicators, a start date and a selection of clinics and the screen shows just a small selection of data from the pivottable. It makes for a lovely front end, allows for sophisticated graphs and data analysis way beyond what a database could achieve (for instance I have a worksheet with a bublechart that functions as a map; clinic positions are geocoded and you can plot disease outbreaks as bubbles of various sies with the easting and northings as the x and y axes of the graph).

Its biggest limitation has been that it is unable to distinguish empty cells from cells with the value 0. I'll rewrite the formulae and see what we end up with. I foresee a possible problem with addition formulae like if the formula in E2 was =A2+B2; if A2 and B2 are both blank it will return a zero at the moment in the pivottable; perhaps =if((and(A=""),(B2="")),0/1,sum(A2,B2)) will now work, ie maybe I need to force an error value (which the pivottable would then filter out) to stop the pivottable from incorrectly returning the value zero.

 

[RoryA]

To be honest, I would never work with a PT that size. I would use code to only extract the required data for the PT based on the user inputs, or even to simply filter the PT to show what the user wants.
If you are getting the data from a SQL server, you could also just extract the relevant data from there, unless of course the table needs to be used when disconnected from the server.

 

[medexcel]

OK, status report. You will recall, I was creating a column that was a composite (depending on the column, either the sum or the quotient of the numbers in the source columns) of two other columns, and trying to create a pivottable that gave blanks in the pivottable when the underlying data was an error (essentially doing the same as a calculated field, only Excel calculated fields in pivottables don't work with thousands of rows of data).

The function within the pivottable fields menu to return blank for errors worked well for those indicators where I divided one value by another and allowed the error to show in the composite column.

However there is now a new and massive problem for the columns with additions. Previously I'd had the formula,

=if((and((A2=""),(B2="")),"",sum(A2,B2))

but the problem was, when A2 and B2 were both blank, the pivottable would return a 0, which I said was misleading. But when I rewrote the formula to

if((and((A2=""),(B2="")),1/0,sum(A2,B2))

to force an error, whilst the cell in the pivottable that previously erroneously said 0 now returns blank, I lose the "total sum of", as the pivottable seems to only calculate a "total sum of" when all the cells making that total are filled, even if by a 0.

To put it simply, is there any way that if there are any cells whatsover with error in a pivottable, Excel can still calculate the "total sum of" for that data field? And if not, can anyone suggest the alternative which is how I can hide the 0s in the pivottable that result when adding two blank cells?

Many thanks!

 

[Fazza]

You might consider, as an option, a different approach.

Maybe - if it suits - along the lines of rorya's comments, work with the data more like a database. And where possible, not use worksheet formulas. (When handled like a database, results sets can include true nulls; unlike worksheet formulas that return zero length strings or zeros.) Do more of the work with SQL. This would be different from the current approach for the steps that work with the data, but after that - reports or charts - maybe that would be little changed.

Custom/calculated fields for example could be done in the SQL with greater flexibility/usability than pivot table calculated fields.

Maybe a simpler way would be some VBA to overcome the problems with the string entries in the numeric field. Such as a simple replace all with nothing, maybe?

HTH, F

 

[Fazza]

Some further thoughts.

There is a large amount of data, and it is sourced from a database. The pivot table is on a hidden worksheet and then OFFSET, INDEX & MATCH functions are used. This might be OK for small amounts of data, but even then is not the smartest approach. With large amounts of data the deficiencies are being exposed.

I wonder if the file will likely have further functionality in the future, or is the file pretty much fully developed?

What do you think, like suggested at the end of my previous post, you had a simple (one line) of VBA to blank/delete the "" entries?

 

[medexcel]

Thanks Fazza - the answer is, I have no knowledge of SQL and besides I inherited a pretty sophisticated database that had taken two years to compile. Not being proficient with SQL, and needing to be able to share the database with other people who didn't have SQL/PHP installed, I chose to import the entire database into Excel then use forms as a nice simple "front end" for people to pull out the data they needed using just one big file. Admittedly it has shortcomings, but it's easier to have all the data in one place and it actually works very well for us. It's just a nuisance that I've run up with a few shortcomings like the one described above...either blank cells sum to zero meaning I can't tell if the sum is zero or if it's a combination of empty cells, or the total is blank, which is actually worse as it stops real data in certain cells from being summed up in the row "total sum of". Me thinks I will have to play around with VBA and macros and see if I can figure out a crafty solution.

 

[Fazza]

OK. To be clear, my references to SQL are about using SQL entirely within Excel. SQL can be used by default in Excel; it can be transparent to users via VBA so there is no need for users to understand SQL, and it is nothing to do with having PHP/SQL installed. SQL is a (largely) platform independent standard for working with databases. Such as to specify records returned by queries. In Excel that could be query tables or ADO recordsets. The queries would be on the data in Excel. Also entirely within Excel, SQL can specify the dataset feeding into pivot tables - such as filter a large source dataset so the pivot table receives the filtered, or already summarised, or somehow modified data. The same functionality as using SQL in queries. regards