Problem with calculated field in Pivot table

Hi,

My pivot table produces data with some gaps in it and I'm having trouble working out how to handle these when creating a calculated field.

I've attached a screenshot of my pivot table (but I've had to save it as a monochrome bitmap to get under the maximum file size.)

Basically, there are values under "Attended" and "DNA", but some months, for some doctors, one or both of these values may not exist. And I need to calculate DNA/(Attended+DNA) to give a "DNA rate".

I've had three attempts at this as follows:

=IF(Attended<>"",DNA/(Attended+DNA),"") gives #DIV/0! if Attended is empty.

=IF(Attended>0,DNA/(Attended+DNA),"") gives #VALUE! if Attended is empty.

=IF(ISERROR(DNA/(Attended+DNA)),"",DNA /(Attended+DNA)) also gives #VALUE! if Attended is empty


Does anyone know how I can modify my formula so I don't get these error values? What I need is that if the Attended value is 0,blank,NULL,missing, or whatever, then the DNA rate should also show a blank cell.

HELP!

Try

Please Login or Register  to view this content.

Thanks for that Tippys, but I'm afraid it didn't work. That resulted in #DIV/0!

I think your version is basically the same as my first attempt, except you've put it the other way round. It SHOULD work, I think, and I don't understand why it doesn't. It must be something to do with the "Attended" field not actually being blank or empty, although it certainly looks like it is.

Because your IF statement (and mine) is evaluating the function to the "not blank" option.

But if the cell isn't really blank or empty and it's not = 0, then what the heck is it?

Nope...I'm afraid that didn't work either.

It shouldn't matter if DNA is blank because the calculation says "if A is not blank, divide B by (B+A), otherwise don't do anything". I use this format a lot in spreadsheets and it always works OK but this is the first time I've tried it in a pivot table.

Just in case, I also tried OR instead of AND, but that wouldn't work either. I still get #DIV/0!

can you attach a .zip sample of your document.

Hi Tippys

Unfortunately I couldn't get my filesize under 100KB unless I took out the pivot table. I took most of the data out, refreshed the pivot table and then zipped the file and it was STILL 120KB, so the forum wouldn't let me attach it. Is it just the fact it's got a pivot table that's making it so huge? The data is only 25 rows x 5 columns.

So this attachment has the data and the VALUES of the pivot table and maybe you can recreate what I need from this layout and the bitmap attached to my first post.

But if you can't, I've realised that if I replace "" with 0 in my first version of the function, it (more or less) works, because this gives me a DNA rate of 0% when there's no value in Attended. This isn't STRICTLY correct, because I should not have a DNA rate at all for those clinics, rather than a zero rate, but I can live with it.