How to calc thousands of estimates using pivot table

[GradStudent2012]

Hi all!

I have a question regarding calculating estimates using hundreds (sometimes thousands) of lines of data. Here is what I'm trying to do.

I want to gather the weighted average of biomass in mexico. I have a data table that is formed through spatial data (so each line of data is from a different pixel - or combination of pixels because many pixels have the same data information). Each pixel/data line has the following information: pixel #, region, pixel count, forest area, forest biomass. What O did was: create a pivot table to get the sum of the pixels in each region (there are 150). the next part is the part where i'm lost.

I want to know the percentage of data within each country region (because what I want to do is to get the weighted average of biomass in each country). So, what I did was create a column with this calculation: [=B4/($L$4)]. Where B4, is the number of pixels, L4 is the total number of pixels in region (calculated by creating the pivot table). the problem is, because there are 150 regions, i'm inputting this in manually - I change the calculation based on the region. I've actually gotten pretty fast, but I worry about errors because I do this manually.

I'm not sure if I explained it well or clearly enough, but if someone has any thoughts or advice I would greatly appreciate it. I need to quite a few of these calculations so having a more systematic approach would be very helpful.

Thanks!

[DonkeyOte]

I want to know the percentage of data within each country region

The above seems a reasonable request, however:

Each pixel/data line has the following information: pixel #, region, pixel count, forest area, forest biomass.

there is no reference to Country in the above as I see it - just region.

...what I did was create a column with this calculation: [=B4/($L$4)]. Where B4, is the number of pixels, L4 is the total number of pixels in region (calculated by creating the pivot table)

It's not altogether clear as to where the B4/$L$4 reference exists ? Pivot sheet or source sheet ?
If source sheet (seems the more likely) then we assume this calculation occurs on each transaction ? Next question would then relate to L4 - is this a GETPIVOTDATA call or a hard wired cell reference to the Pivot Table total cell ?

I am presuming your concern currently stems from the fact that where you have a Region change you have to ensure you manually change the reference to L4 ... and if so, yes, you could avoid this quite simply (GEPTIVOTDATA call is very fast so you could repeat on each line using a dynamic reference to Region field in your Pivot - else use SUMIFS on source data itself [slower but truly dynamic]).

Could you possibly create a small mock up of your data such that we could see what you're working with - dummy values that are confidential (if any)

If you could outline the expected results that would help also...

[GradStudent2012]

thank you so much for your reply! i've attached a sample of the data that i have.

so, i created a pivot table to get a sum of the 'COUNT' of each region.
then, to get a percentage area of each region, i divided the 'COUNT' by the corresponding region in the pivot table. whenever i hit a new region in the row, i manually changed the estimate so it's divided by the next region - if you see the table i think you'll see what i mean.

anyways, this is just a sample so it's only 8 regions, but i have it for about few hundred regions and must produce these estimates a number of times and i feel like there is a simpler way but looking through the links that you posted (and through other forum threads), i couldn't figure it out.

thoughts?

thank you!

[DonkeyOte]

...whenever i hit a new region in the row, i manually changed the estimate so it's divided by the next region

As outlined earlier you should find you can replace the need for the above courtesy of GETPIVOTDATA:

Please Login or Register  to view this content.

...looking through the links that you posted

Those links are my general signature (topics of interest) and as such are not specifically aimed at your question

[GradStudent2012]

thanks for your quick reply! though, i copied the formula and it tells me that there is an error. i looked it up here: http://www.contextures.com/xlpivot06.html which has the same exact formula you typed in and so i'm not sure why it is telling me i have an error?

=B2/GETPIVOTDATA("COUNT",$H$1,"Region",$C2)

thoughts?

thanks again!

[DonkeyOte]

Can you be more specific regards the error - is it an error dialog (ie formula contains an error) or an error result

If the error is an error dialog then I presume that despite your US locale you are running an odd delimiter setting and you might try:

=B2/GETPIVOTDATA("COUNT";$H$1;"Region";$C2)

If the error is an error result please confirm error value - is it #REF! for ex. ?

[GradStudent2012]

"The formula you typed contains an error.
-To get assistance in entering a function, click OK, the click Function on the Insert menu.
-If you are not trying to enter a formula, avoidu sing an equal sign (=) or minus sign (-), or precede it with a single quotation mark (')."

I tried to go to 'function' on the 'insert menu,' but i didn't find a solution ...

[DonkeyOte]

Did you try prior suggestion (with altered delimiter - ie ; rather than , )

If you go to your Control Panel and click on Regional Formats/Settings what is your Location / Format set to ?

[GradStudent2012]

i was using a mac when the error popped up and i thought maybe i'll try it on a different computer so i just tried it on a pc and the equation you sent worked perfectly!

i also noticed that i should upgrade my excel because it looks like i am limited to 65000 rows on this version (i'm using excel 2004 for mac).

thanks again for your help!

[DonkeyOte]

If you choose to upgrade your Mac version I would suggest you jump straight to 2011 and avoid 2008.

[GradStudent2012]

will do, thanks!