Hi,
can anyone help me! I have created a pivot table, and want to calculate PPM ( parts per million). the table numbers are correct ie totals,but i cant get the PPM to calculate ( value displayed is sum). the correct result is manually done based on failed/total*1000000 and is what id like to see in the PPM column so i can produce a graph. I could do a lookup but im repeating the data
Thanks in advance...
Data:
pivot table.jpg
Last edited by interested; 12-28-2011 at 06:50 AM. Reason: Data not displayed correctly
Hi,
Would it be possible for you to upload the sample file.
I believe this can be done using pivot inter-calculations.
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
Thanks for the quick responce.I have emailed you with a sample of the pivot data and raw data taken from. I cannot upload as its above the limit required.
Hi,
You are using formula =1000000*(AV8/AW8) to calculate the PPM but your AW column is blank... and this is why the PPM says #DIV/0!, may be I am missing something here but need little more details on this. Thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
Hi, i was looking at the range AI-AO, with manual calculations in AP. This is what i want the pivot table to calculate as it is currently wrong. Ill upload a cut down version if i can get it ti 1mb.
Thanks
Hi,
See the attached workbook, yellow column, where I have obtained the similar results using "calculated field" option of Pivot table, which you have done manually. You can get more by following similar path.
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
Thanks thats exactly what i was looking for, now the hard question "how have you done it" As i have several sheets to format this way. Oh newbie step by step please.
thankyou for your very quick responces.
You are welcome..
For your learning, I have created and uploaded a screen shot which will help you to understand how to do this step by step. If you have fixed number of sheets, then I would suggest you to do this manually and then you just need to refresh the pivots... but if you have huge number of sheets to apply the same logic plus these sheets will be coming in regularly, then I would suggest you to go for VBA macro
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
Oops... missed the screenshot, now uploaded.
Cheers .....
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
Thanyou so much, i can see other uses for this now !!!!!!!!!!!!!!!
That's great... Pivots are great tool and so vast in nature...
Feel free to reach out in case you stuck somewhere.
Cheers
Regards,
DILIPandey
<click on below star if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
Post Deleted
Last edited by JieJenn; 12-30-2011 at 06:24 PM.
To thank someone who has helped you, click on the star icon below their name.
I hate reading
Portfolio
I need a job.I am young and incompetent
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks