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

=IF(Attended="","",DNA/(Attended+DNA))

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.