Function getting WAY to long and complex!!!! And it still doesn't work!

[Craig Newell]

Hi everyone,

I have some information in a pivot table I need turned into a
percentage and entered into a field in a different sheet. At the
moment, my function looks like this:

=GETPIVOTDATA(PNGS!$K$3,"F" & " "
&Overall!A377)/(GETPIVOTDATA(PNGS!$K$3,"F" & " "
&Overall!A377)+GETPIVOTDATA(PNGS!$K$3,"M" & " " &Overall!A377))

which links the cell to the pivot table stored on sheet PNGS. Since I
have multiple data sources and pivot tables on the sheet, I am using
the absolute cell reference $K$3 to tell them apart.

What the function is currently doing is looking for two criteria in
the pivot table:

1. whether the STATUS column shows an "F"
2. whether the value in the value in column A of the row the function
exists in is present in the PATCHES column of the pivot table

then do the same action again for "F" and add it to the value for the
same lookup using the "M" status. Divide the F value by F+M to get a
percentage, which is the end result of what I need.

The issue I am running into is if either the "F" or the "M" value is
missing from the pivot table, I get a #REF.

So I have tried:

=IF(ISERROR(GETPIVOTDATA(PNGS!$K$3,"F" & " "
&Overall!A378)/(GETPIVOTDATA(PNGS!$K$3,"F" & " "
&Overall!A378)+GETPIVOTDATA(PNGS!$K$3,"M" & " "
&Overall!A378))),"100%",GETPIVOTDATA(PNGS!$K$3,"F" & " "
&Overall!A378)/(GETPIVOTDATA(PNGS!$K$3,"F" & " "
&Overall!A378)+GETPIVOTDATA(PNGS!$K$3,"M" & " " &Overall!A378)))

but it only returns a static value, no matter which STATUS is missing.
I need it to enter "100%" if the "M" status is missing and "0%" if the
"F" status is missing.

SO then I tried:

=IF(GETPIVOTDATA(PNGS!$K$3,"F"&"
"&Overall!A379)<=0,"0%",IF(GETPIVOTDATA(PNGS!$K$3,"M"&"
"&Overall!A379)<=0,"100%",GETPIVOTDATA(PNGS!$K$3,"F"&"
"&Overall!A379/(GETPIVOTDATA(PNGS!$K$3,"F"&"
"&Overall!A379)+GETPIVOTDATA(PNGS!$K$3,"M"&" "&Overall!A379)))))

but got lost instantly trying to troubleshoot the #REF I am still
getting with this one. I am not sure even how to get it to look for a
value that doesn't exist!

ANY help would be hugely appreciated!!!

Feel free to email me directly at:
craigDOTnewellATtorsdcDOTca, rendering the address properly, of
course! I HATE SPAM!!

Thanks,
Craig