Ben has attended:
Excel Advanced course
Pivot Table and Charts
Hi,
I've a database as follows ( to make it simple )
Col A = Date
Col B = specific number
Col C = ok or not ok
Col D = names
IS it possible with pivot table and charts to get the percentage ok over (ok + not ok) and also the charts for percenate ok only
ok will means the quality score.
Do let me know if I need to send my excel sheet so you can have a look.
Thanks
In advance
RE: Pivot Table and Charts
Hi Ben,
Thank you for the forum question.
I cannot get my head around it.
May be it could be a good idea to see your worksheet.
Please send it to:
info@stl-training.co.uk
I have some time tomorrow morning to find a solution.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Pivot Table and Charts
Hi Ben,
The Pivot Table tool is very powerful but has some limitations. To do what you want I see two option. You will need to create a calculated item. I have created a calculated item with the formula =iferror(OK/(OK+Not OK),0). You also have another option. You can reuse your Pivot Table output in a "normal" table and then do the calculations you need in this table. Then the chart can be created from the "normal" table.
I have attached a workbook with both solutions.
If you want to create calculated items you will get more limitation. You can only use a field one time in the Pivot Table.
I hope this can help you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Quality Report Demo.xlsxRE: Pivot Table and Charts
HiJens,
Thanks for your reply. I've tried to replicate it on my excel sheet but got an error message.So i using the excel sheet you have uploaded.
Thanks again.
Ben
RE: Pivot Table and Charts
Hi Ben,
You are welcome. Happy to help.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
