#DIV/0! Error in Calculated Field won't calculate Grand Total for YTD

NancyRG

New Member
Joined
Mar 10, 2015
Messages
3
I have read and incorporated several solutions for this problem(I have Excel 2010).

Currently, my formula in the calculated field of my Pivot table is (=IF('OptIn Info'/'Leads Info'=0,"-",'OptIn Info'/'Leads Info'). Both the OptIn and Lead numbers can have 0's creating the error.
I have also taken out the "0's" from my data, that doesn't work.
I have tried ISERROR and that doesn't work. Screws up all calculations
In Pivot Table Options I have changed the format for error values to show "None", and empty cells to have --. This has helped clean up the Pivot table.

BUT My Sub Totals for YTD and Grand Totals do not work if there is an error and says "None" like I've told it to do, it will not Calculate. It does work at a month level? and it shows totals for the OptIn & Leads that it should be dividing.

Is there another solution? (P.S. I can't figure out how to paste screen shot or attach sample) HELP!!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Watch MrExcel Video

Forum statistics

Threads
1,122,803
Messages
5,598,131
Members
414,214
Latest member
marketingnumbersguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top