Sigma Formula for Carbon Estimation

Copper Contributor

Hi, could someone help me to transform the attached formula to Ms. Excel? I have been trying for over 3 days, but I got a different result from the reference document. 

This Planvivo formula for baseline emission estimationThis Planvivo formula for baseline emission estimation

11 Replies

@ivannalis24 

Please attach a sample workbook with some data and the expected/desired result.

Hi @Hans Vogelaar, thanks for your interest. I attached the example file. Thanks in Advance.

 

@ivannalis24 

Thanks. I see the formula that you used.

Could you also tell us what the result should be? Perhaps that would provide a clue as to what would have to be changed.

@ivannalis24

 

The data that you provide in the Excel file is insufficient.

 

The notation

 

image.png

 

implies that Drr, Apa and Grr are 3-dimensional arrays, and C and Csf are 1-dimensional arrays.

 

The sigma formula says that we sum Drr*Apa*(C-Cnf) + Grr*Apa*(C-Csf) for each i, j and k -- that is, for each cell identified by each i,j,k in Drr, Apa and Grr and by each corresponding i in C and Csf.

 

But you only provide only one value of each in columns B:D, to namely:

 

Drr i,j,kPeat swamp          0.20
 Sec peat swamp          0.10
 Riparian           1.70
 Sec riparian          0.50
Grr i,j,kPeat swamp          0.13
 Sec peat swamp                 -
 Riparian           0.01
 Sec riparian                 -
Apa i,j,kPeat swamp        30.00
 Sec peat swamp        23.00
 Riparian       713.00
 Sec riparian        58.00
CiPeat swamp      147.00
 Sec peat swamp      147.00
 Riparian       174.00
 Sec riparian      174.00
CsfiPeat swamp      104.00
 Sec peat swamp      104.00
 Riparian         52.00
 Sec riparian        52.00

 

If i goes from 1 to p, and j goes from 1 to q, and k goes from 1 to r, you need to provide p*q*r data for each of Drr, Apa and Grr and p data for each of C and Csf.

 

You might present Drr, Apa and Grr as p-by-q arrays of data repeated r times (a total of p*r rows).  We can derive p from the size of C and Csf.

 

Otherwise, you need to tell us what p, q and r are.

 

@Joe User @Hans Vogelaar 

Hi, I'm so sorry for this late reply. Well, I referred to the reference document which provides a table like below:

'Drr' and 'Grr' for each i,j,k

drr and grr.png

'Apa' value for each i,j,k

apa.png

 

And the expected result should be like below (highlighted number):

ivannalis24_2-1621571852270.png

What I tried in the excel file was to obtain that result. I also want to confirm, does the formula that I wrote in the file is correct?

 

@ivannalis24 

According to this screenshot, the Drr and Grr values are percentages, so their values in your sample workbook are a factor 100 too large.

The value you want is the sum of the EBLs for the four forest types.

Your screenshots do not show the C, Cnf and Csf values, so I cannot check the outcome of your formula against that in the last screenshot.

@Hans Vogelaar 

Hi Hans, thank you for still interested in this topic. Really appreciate it. Value for C, Cnf, and Csf provided below:

C CNF SF.png

 

@ivannalis24 

My apologies, I'm totally unfamiliar with the subject, so the following is undoubtedly very stupid. Does this mean that

1) The C for Secondary Peat Swamp and Secondary Riparian is 0?

2) The Csf for Peat Swamp and Riparian is 0?

3) The Cnf for all the above types is 0?

@Hans Vogelaar 

Hi Hans, sorry I made it more complex.

Referring to 'Drr' and 'Grr' screenshot above, all types of forest i, j, k has deforested value, so I used 'Deforestated land' (Cnf) value (33 Mg C ha-1) constantly for each i, j, k, and it applies to C also, means every forest has 'C' value.

 

As I read in reference, secondary forest is the degraded forest and it is the lowest level of forest status (no more degradation would quantify there). So that's why every secondary forest doesn't have a 'Cnf' value, which means 0.

@ivannalis24 

Now I'm confused. You first write "I used 'Deforestated land' (Cnf) value (33 Mg C ha-1) constantly for each i, j, k," and then "every secondary forest doesn't have a 'Cnf' value, which means 0."

 

Do Secondary Peat Swamp and Secondary Riparian have Cnf=33 or Cnf=0?

@ivannalis24 

I would recommend collecting your data together in tabular form and, provided you are using Excel 365, perform calculations as array formulas using the structured references or defined names.  I picked up some of the percentage issues from your later posts but have not worked through them in detail.

 

image.png