Calculations using Sigma Summation

Joeclupis · Oct 13, 2018

Hello everybody,

I am working on a thesis paper and have an equation that I need to be able to use in excel. I have no idea how to design an excel equation to match what I see. I thing that this will be an array function of some sort.

The following are the parts of the equation:
C = COST $/L^-1
K = all capital costs ($)
O&M – all operating costs ($)
F = biomass feedstock costs ($)
R = rail freight costs (S)
H = all refining costs (S)

P = hydrocarbon production (L)

d = discount rate
t = case study year (1 – 40)

and the equation is:

C =( {sigma w/t next to bottom leg}(K_t+O&M_t+F_t+R_t+H_t)(1+d)^-(t-5))/( {sigma w/t under bottom leg) (P_t(1+d)^-(t-5)) )

I will be converting from metric to imperial where applicable. That I can do.

Thank you in advance for the assistance.
[FONT=&quot] [/FONT]

MEUserII · Oct 13, 2018

If you are looking to use this equation in Excel for the purposes of displaying it; you could do that by converting the equation to LaTeX (mathematical document format), and then inserting the converted equation as an image. See the below link for an online equation editor/composer utilizaing LaTeX:
Link: http://www.sciweavers.org/free-online-latex-equation-editor#

Joeclupis · Oct 13, 2018

MEUserII said:
If you are looking to use this equation in Excel for the purposes of displaying it; you could do that by converting the equation to LaTeX (mathematical document format), and then inserting the converted equation as an image. See the below link for an online equation editor/composer utilizaing LaTeX:
Link: http://www.sciweavers.org/free-online-latex-equation-editor#

that didn't work as expected

Joeclupis · Oct 13, 2018

i cannot get an image to load into this forum. I have tried the LaTeX, imgur, and linking to my google docs. Nothing works. I will try to finangle this so that is looks like what i need.

_ _
\ \
( /_t * (K_t+O&M_t+F_t+R_t+H_t)(1+d)^-(t-5))/ ( /_ * (P_t(1+d)^-(t-5)) )
t

Joeclupis · Oct 13, 2018

Well, I cannot get it to work. Every time I try to draw it out the editor screws up the positioning of the items. Please try to follow what I posted in the first post and ignore all other posts.

Thank you

MARK858 · Oct 13, 2018

Upload your file to a free hosting site like www.box.com or www.dropbox.com, mark your file for sharing and post the link it produces in the thread (not in any tags).
Or
use one of the screenshot addins in my signature block below if it is an Excel file.

shg · Oct 13, 2018

	A	B	C	D	E	F	G	H
1							d
2							0.377
3	t	K	OnM	F	R	H	P
4	1	0.435	0.538	0.541	0.410	0.799	0.432	4.55759
5	2	0.707	0.426	0.803	0.621	0.421	0.993
6	3	0.246	0.276	0.477	0.239	0.400	0.788
7	4	0.919	0.853	0.815	0.217	0.493	0.252
8	5	0.615	0.777	0.759	0.606	0.824	0.811
9	6	0.476	0.577	0.611	0.774	0.819	0.441
10	7	0.494	0.529	0.597	0.572	0.669	0.125
11	8	0.892	0.169	0.184	0.806	0.943	0.717
12	9	0.382	0.157	0.615	0.124	0.475	0.452
13	10	0.254	0.575	0.268	0.051	0.597	0.141
14	11	0.394	0.787	0.744	0.372	0.940	0.041
15	12	0.870	0.511	0.530	0.489	0.411	0.832
16	13	0.180	0.572	0.151	0.033	0.720	0.357
17	14	0.377	0.236	0.214	0.494	0.629	0.928
18	15	0.797	0.658	0.251	0.308	0.680	0.898
19	16	0.383	0.404	0.168	0.823	0.933	0.987
20	17	0.912	0.218	0.260	0.112	0.545	0.832
21	18	0.493	0.551	0.066	0.130	0.800	0.858
22	19	0.091	0.527	0.091	0.870	0.562	0.484
23	20	0.890	0.016	0.987	0.040	0.673	0.459

The formula in H4 is

=SUMPRODUCT(B4:B23 + C4:C23 + D4:D23 + E4:E23 + F4:F23, (1+$G$2) ^ (5 - A4:A23)) / SUMPRODUCT(G4:G23 * (1 + $G$2) ^ (5 - A4:A23))

Joeclupis · Oct 13, 2018

Re: Calculations using Sigma Summation (SOLVED)

shg said:
A
B
C
D
E
F
G
H
1
d
2
0.377
3
t
K
OnM
F
R
H
P
4
1
0.435
0.538
0.541
0.410
0.799
0.432
4.55759
5
2
0.707
0.426
0.803
0.621
0.421
0.993
6
3
0.246
0.276
0.477
0.239
0.400
0.788
7
4
0.919
0.853
0.815
0.217
0.493
0.252
8
5
0.615
0.777
0.759
0.606
0.824
0.811
9
6
0.476
0.577
0.611
0.774
0.819
0.441
10
7
0.494
0.529
0.597
0.572
0.669
0.125
11
8
0.892
0.169
0.184
0.806
0.943
0.717
12
9
0.382
0.157
0.615
0.124
0.475
0.452
13
10
0.254
0.575
0.268
0.051
0.597
0.141
14
11
0.394
0.787
0.744
0.372
0.940
0.041
15
12
0.870
0.511
0.530
0.489
0.411
0.832
16
13
0.180
0.572
0.151
0.033
0.720
0.357
17
14
0.377
0.236
0.214
0.494
0.629
0.928
18
15
0.797
0.658
0.251
0.308
0.680
0.898
19
16
0.383
0.404
0.168
0.823
0.933
0.987
20
17
0.912
0.218
0.260
0.112
0.545
0.832
21
18
0.493
0.551
0.066
0.130
0.800
0.858
22
19
0.091
0.527
0.091
0.870
0.562
0.484
23
20
0.890
0.016
0.987
0.040
0.673
0.459

<tbody>
</tbody>

The formula in H4 is

=SUMPRODUCT(B4:B23 + C4:C23 + D4:D23 + E4:E23 + F4:F23, (1+$G$2) ^ (5 - A4:A23)) / SUMPRODUCT(G4:G23 * (1 + $G$2) ^ (5 - A4:A23))

shg,

Thank you. This looks like it should work. I am still trying to understand the SUMPRODUCT statement. Using your numbers and formula I came up with 6.22436. I set up the spreadsheet exactly like yours and copied the formula exactly.

But it is time for me to shut the book and join reality.

Have a nice day

shg · Oct 13, 2018

I think you mis-copied something.

Calculations using Sigma Summation

Joeclupis

Board Regular

Excel Facts

MEUserII

Board Regular

Joeclupis

Board Regular

Joeclupis

Board Regular

Joeclupis

Board Regular

MARK858

MrExcel MVP

shg

MrExcel MVP

Joeclupis

Board Regular

shg

MrExcel MVP

Similar threads

Forum statistics

Share this page

Calculations using Sigma Summation

Board Regular

Excel Facts

Board Regular

Board Regular

Board Regular

Board Regular

MrExcel MVP

MrExcel MVP

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock