1

I am trying to create a production report for my work and all of the tasks that are done are counted for each employee. I have my data, my pivot, and my main dash that points all my data to go where I want it to go. The problem is, when someone does not complete a task for the day I get an error message when all I need it to say is "0". I have done research and cannot find the exact formula to apply to my excel report that will formulate a "0" for empty cells instead of an error message. Please help? Thanks

New contributor
user1153896 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 1
    What does your data look like? What are the existing by queries? – music2myear Mar 26 at 2:54
  • There is no queries, I am just trying to have a cell point to specific data in the pivot table but return a "0" if there is no information instead of a "refError". Does that help? – user1153896 Mar 26 at 3:03
  • Without knowing the formula /pivot table setup you use it's impossible to tell how to fix it. – Máté Juhász Mar 26 at 19:38
1

Have you tried an iserror formula?

eg =IF(ISERROR(D3/0),0, D3/0)

The iserror returns a true value if the formula errors, in the above if d3/0 errors it returns a 0 value otherwise it calculates d3/0.

New contributor
Justin Doward is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1

A step one solution might be the replace all the empty cells in you data with a zero. This can easily be done by highlighting the area. Then use find a replace; leave replace blank and add 0 to replace with. Not a great solution but it should help. You could then record a simple Marco to automate these steps.

I tried using Find and Select Special, then used blanks, but it won't let me replace all the values at once.

Your Answer

user1153896 is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.