Nulls in pivot table calculated fields (Excel 2003, XP)

Topic

New Reply

Hi everybody:

Got a pivot table problem. I’m adding calculated fields to a pivot table, which work fine as long as none of the fields contain null values. (The fields are themselves calculated first). Excel doesn’t seem to like the Nz function and is nulling out the results. Anybody know a workaround?

Thank you, in advance, for your help.

Reply | Quote

Replies

Nz is an Access function, it is not available in Excel.

I’m not sure that I understand your problem. Unlike a field in Access, a cell in Excel cannot really be null – Excel treats blank cells, empty strings and zero values as more or less the same. Can you provide an example?

Reply | Quote

Hi Hans:

Thanks for your response.

It’s not about cells being null; its about calculated fields in the pivot table code producing null values that I need to convert to 0s.
What I am doing is first creating calculated fields for monthly balances (using code below case 3: result is, i.e. for April: ‘ “=(‘4/30/2008 Balance’ -‘4/1/2008 Balance’ )/’4/1/2008 Balance'”) and then using those fields to create calculated field for YTD
(also using code below, case 5). The problem seems to be that some of the funds don’t have monthly balances for all the months to date and I need to zero these out so the YTD calculation will work instead of returning a null or empty string in my pivot table report.

Code to produce monthly returns fields:
__________________________
intNum = 0
For intNum = 0 To intMo – 1 ‘loop backward through months in current year,starting with current month
dtWork = fnMonthEnd(dtMoEnd, -intNum)
strField = fnPTFldName(dtWork, 3)
strCalc = fnPTCalc(dtWork, 3)
pt.CalculatedFields.Add strField, strCalc ‘Must use fields in the query for pivot cache
pt.PivotFields(strField).Orientation = xlDataField
pt.PivotFields(“Sum Of ” & strField).NumberFormat = “0.00%”
pt.PivotFields(strField).NumberFormat = “0.00%”
Next intNum
_________________
code to produce calculation to go in the pivot field calc:
_________________
Function fnPTCalc(dtDate As Date, btType As Byte) As String
‘Build string for calculation in pivot table field
‘from date argument
Dim dtBOM As Date, strBOM As String, strCalc As String, strYear As String, strPYear As String
Dim strEOM As Date, dtEOM As Date, intMo As Integer, intQ As Integer, intCt As Integer, strWork As String
Dim strMonth As String, dtWork As Date
Const strErr As String = “fnCalc error: ”
On Error GoTo err_fnPTCalc
intMo = Month(dtDate)
dtBOM = CDate(intMo & “/1/” & Year(dtDate))
dtEOM = fnMonthEnd(dtDate)
strBOM = fnStrDate(dtBOM)
strEOM = fnStrDate(dtEOM)
strYear = Year(dtDate)
Select Case btType
Case 3 ‘monthly percentage calc
strCalc = “=(‘” & strEOM & ” Balance’ – ‘” & strBOM & ” Balance’)/'” & strBOM & ” Balance'”
‘ ‘3/31/2008 Balance’ -‘3/1/2008 Balance’ )/’3/1/2008 Balance’
Case 5 ‘YTD or year-end percentage calc
‘ i.e., “=((1+Jan_return_2008)*(1+Feb_return_2008)*(1+Mar_return_2008)* (1+Apr_return_2008))-1”
intCt = 0
For intCt = intMo – 1 To intCt Step -1
dtWork = fnMonthEnd(dtDate, -intCt)
strMonth = Format(dtWork, “mmm”)
strWork = strWork & “(1+” & strMonth & “_return_” & strYear & “)*”
‘strWork = strWork & “(1+Nz(” & strMonth & “_return_” & strYear & “),0)*” ‘THIS DOESN’T WORK
Next intCt
strWork = Left(strWork, Len(strWork) – 1) ‘strip off last *
strCalc = “=(” & strWork & “)-1”
Case Else
MsgBox strErr & “No calc available for ” & btType & “. Please see Application Developer.”, vbOKOnly
fnPTCalc = “”
GoTo exit_fnPTCalc
End Select

fnPTCalc = strCalc

exit_fnPTCalc:
Exit Function
err_fnPTCalc:
MsgBox strErr & Err.Description
Err.Clear
fnPTCalc = “”
Resume exit_fnPTCalc
End Function

So, for example, if a fund started in February and has no returns in January, its Jan_return_08 pivot table field returns a null, which nulls out my YTD calculation for that fund.

Hope this is clear.

Thanks!

Reply | Quote

I don’t ever use calculated fields in a pivot table, so I don’t know much about them, but can you change the line

strCalc = “=(‘” & strEOM & ” Balance’ – ‘” & strBOM & ” Balance’)/'” & strBOM & ” Balance'”

to

strCalc = “=IF(OR(ISBLANK(‘” & strEOM & ” Balance’),ISBLANK(‘” & strBOM & ” Balance’)),0,(‘” & strEOM & ” Balance’ – ‘” & strBOM & ” Balance’)/'” & strBOM & ” Balance’)”

or is that not allowed in a pivot table? If it is allowed, does it help?

Reply | Quote

Thanks, I’ll give it a try.

Reply | Quote

I tried your code for the monthly balance: it produced
=IF(OR(ISBLANK(‘6/30/2008 Balance’),ISBLANK(‘6/1/2008 Balance’)),0,(‘6/30/2008 Balance’ – ‘6/1/2008 Balance’)/’6/1/2008 Balance’)

I also tried code below for calculating the YTD, but my guesstimate as to how to use the IF and OR and ISBLANK functions doesn’t work:

strWork = strWork & “(1+ IF(OR(ISBLANK(” & strMonth & “_return_” & strYear & “),ISBLANK(” & strMonth & “_return_” & strYear & “),0)” & “)”
produces:
=((1+Jan_return_2008)*(1+ IF(OR(ISBLANK(Jan_return_2008),ISBLANK(Jan_return_2008),0))(1+Feb_return_2008)*(1+ IF(OR(ISBLANK(Feb_return_2008),ISBLANK(Feb_return_2008),0))(1+Mar_return_2008)*(1+ IF(OR(ISBLANK(Mar_return_2008),ISBLANK(Mar_return_2008),0))(1+Apr_return_2008)*(1+ IF(OR(ISBLANK(Apr_return_2008),ISBLANK(Apr_return_2008),0))(1+May_return_2008)*(1+ IF(OR(ISBLANK(May_return_2008),ISBLANK(May_return_2008),0))(1+Jun_return_2008)*(1+ IF(OR(ISBLANK(Jun_return_2008),ISBLANK(Jun_return_2008),0))-1

and when I try to add the YTD calculated field, Excel tells me it has run out of memory (run time error 7).

Sorry I can’t attach the workbook: it is only two sheets, but even zipped up, it’s bigger than 100k. My module is attached.

Thank you for all your help.

Reply | Quote

I’m afraid I can’t help you with this, sorry.

Reply | Quote

Any chance you can post a simplified workbook showing the data layout you have, the current pivot table layout (copy and paste special->values to keep size down), and what you are trying to create – typed in manually if necessary to show the desired result?

Reply | Quote

Hi Rory:

Thanks for your help.

I already posted the text file with my module code, which runs from a command button on another sheet which I couldn’t include due to size limitations.
The data comes in from an Access .mdb file and contains all the fields needed to calculate the monthly percentage returns and, from those, the YTD. None of the recordset values are nulls; I used Nz in the query to get zeros where there were blanks.

First, the PivotTable code creates calculated fields for the monthly returns, using the calculation: (end_mo_balance – beg_mo_balance) / beg_mo_balance for each monthly balance PivotTable field. Then, I create a calculated field for YTD using the monthly return PivotTable fields.

I use the fnPTCalc code below to derive strings similar to, i.e. for YTD as of April: ‘ “=((1+Jan_return_2008)*(1+Feb_return_2008)*(1+Mar_return_2008)* (1+Apr_return_2008))-1”
which is the calculation we use. My code returns the correct string, and it works in cases where all the months have returns, but not the others.

Calculation snippet where intMo is the current month number and dtDate is the current date. fnMonthEnd returns a date which is the last day of the month argument.

Case 5 ‘YTD or year-end percentage calc
intCt = 0
For intCt = intMo – 1 To intCt Step -1
dtWork = fnMonthEnd(dtDate, -intCt)
strMonth = Format(dtWork, “mmm”)
strWork = strWork & “(1+” & strMonth & “_return_” & strYear & “)*”
Next intCt
strWork = Left(strWork, Len(strWork) – 1) ‘strip off last *
strCalc = “=(” & strWork & “)-1”

Attached is a 1-page Excel file with the report result. If you look in the first few records (all Convertible Arbitrage), you will see that YTD numbers are missing, even though some of them have monthly balances in 2008. My theory was that the reason the calculated YTD pivot table fields are nulling out is that the monthly return pivot fields are coming up null in cases where a fund had no balances in that month, but perhaps the reason is that there are divide by 0 errors when the beginning month balance was zero. I’ve checked the data, and it seems to bear this out.

So, I think what we need to do is to figure out how to use the IF OR ISNULL functions to ensure that a 0 beginning month balance field used in the monthly performance calc is somehow disarmed. The PT should use the calculation unless the beginning balance is zero, in which case it should return zero.

Thank you for any light you can shed on this.

Reply | Quote

If you use Hans’ suggestion for the monthly figures, do you get zero values for the previously null data? If so, then your YTD function ought to be able to just use those calculated fields as they are.

Reply | Quote

No, I tried that. Hans’s string in the calculation of the monthly return pivot field still resulted in empty cells rather than zeros.
I tried to work around the problem by changing my database query to return zeros instead of nulls, but had the same problem.

I have finally solved it, using: strCalc = “=IF(‘” & strBOM & ” Balance’ = 0,0,(‘” & strEOM & ” Balance’ – ‘” & strBOM & ” Balance’)/'” & strBOM & ” Balance’)”

This results in zeros in monthly return fields where the beginning of month balance is zero and calculates the quarters and year-to-date correctly.

Thank you both so much for your help!

Reply | Quote