Pivot Calculated formula

[jomili]

I have a pivot that consolidates a lot of information. In the end, I'm interested in two main flavors, EA and Non-EA: For each Program, Facility, and Description Type I need to take the EA amount and divide it by the sums of EA and Non-EA. For instance, in the Example, in Program IV-E, in "Child Placing Agency", we have Basic EA and Basic Non-EA, Intense EA and Intense Non-EA, etc. Out in Column H I have my tentative formula, which I'd like to be a calculated field in my pivot. I'm stuck with how to proceed from here.

To add a wrinkle, not every EA has a Non-EA. See the values I put in red. There we have 3 Non-EA fields, with no EA counterpart, so the resulting percentage should be Zero.

As I mentioned, I'm stuck at this point. Any help or advice would be greatly appreciated.

Thanks,
John

[JeteMc]

I modified your formula so that it would be more general so that you could copy it across and then double click it down (see attached file). The formula also accounts for the rows that you highlighted in red, so as to give a value of 0.00% rows. I did find two discrepancies, where the Non-EA line was above the EA line, which I highlighted in green. I would suggest switching the position of those lines, so that the EA line precedes the Non-EA line, in order to get the formula to work. I also found another case, under the Foster Family facility, of an orphan Non-EA line which would not fit the rule, and marked it in orange. I feel like this may be a case where some data was omitted, if not it presents a case the will need further study. The formula used is:

Please Login or Register  to view this content.

I don't believe that you will be able to use a calculated field, however using the format painter the additional columns can be made to look similar to the Pivot Table itself.

Pivot Table Extension.xlsx

Let me know if you have any questions.

[jomili]

Thanks JeteMc. Sorry it took me so long to reply; I've been on a mission trip and just returned. It's funny (or shows I'm on the right track) that your notes and suggestions coincide with the changes I made since starting this post.

I can make it all work by keeping my formulas outside of my pivot table, just hoped there was a way to do it via a calculated field. Thanks for trying.

[JeteMc]

You're welcome, and thank you for the feedback. Hope that you have a nice day.