Pivot table displaying ratio % values (20:80) from spreadsheet as '#DIV/0!' or '0'

Hi,

Having trouble displaying a ratio % value (e.g. 20:80) from spreadsheet in a pivot table. I've been playing around with whether the value is displayed as average, max etc with no solution. The values are displayed as '#DIV/0!' when average is selected and '0' when other formats are selected. Hoping someone can help?

Thanks

Without seeing the workbook, it is difficult.

However, is it possible your PT is getting confused with TIME.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Thanks for the quick reply. I have attached screenshots below. The first one is the formula within the spreadhseet, the second is the value calculated from the formula which is displayed in the spreadsheet, and the third is the pivot table error.

Many thanks

Sorry, can't follow images, I need to play with the workbook.

Can you strip it down to remove anything confidential, and post the workbook?

- Click on Go Advanced and click on the Paper Clip.

DAC

Sorry new to this, where would I find the 'go advanced' option?

Originally Posted by henners2

Sorry new to this, where would I find the 'go advanced' option?

I copied this from the Forum Rules sheet:To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

I think you find it on your original post.

Alternatively, when you hit REPLY, at the bottom right, you will see three buttons. Click on the middle Go Advanced button, then scroll down past the "Preview" of you message until you see Your Message. You will see the [paperclip icon in the first row of icons. Click, then use your browser to find your file. Choose it, then click upload. You should then see it in the Attachments bar at the bottom. If you click Insert, it will include the attachment in your text, if you click Done, it will appear as an attachment.

See how you go!

DAC

Thanks for that, but I am being told the file exceeds the size limit even once i've condensed it down to the bare minimum. Any ideas?

Cheers

Just create a small sample that generates the same problem result.

All I need to see it the data the PT is drawing on and producing the error.

I have to go out and mend some electric fences, so may not get back to you before COB today.

DAC

Okay cheers mate, appreciate it.

I have attached the document as you requested.

Thanks again,
H

Hi Henners,

The reason is simple! Your Ratio is TEXT. Hence your errors.

I don't think there is a way of overcoming this, so you may need to have a look at having two columns. One for Low %age, the other for High %age.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Thanks very much for your help, really appreciated.

H