Help multiply cell with several cells

[svh105]

Hi,

I am looking for a tool that can automatically multiply one cell with several others depending on the year. I have been able to make it multiply with the correct values individually. But I need it to multiply the price with the value for each year at the same time (see the screenshot I attached), instead of individually as it does now.
I created a loop for it to find the correct year and values to multiply with. But I need help getting it to do exactly what I want. Hope someone is able to help me.

Sub calculate ()

ActiveCell.Offset(0,1).Select
Dim Cell As Range
    For Each Cell In Range("F5:F24")
    If Cell.Value < Range("B7").Value Then
    
    ElseIf Cell.Value >= Range("B7") Then
        Cell.Offset(0,4).Value = Range("B8") * Cell.Offset(0,1).Value
        
        End If
    Next Cell

End Sub

 

[kevin9999]

I note that you have 365, so the Filter function should be available to you. If you're ok with a non-VBA solution, and don't mind using some helper columns, then the following gives you what you want (I think). Change the year values in B7 and B12 (the highlighted cells) to demonstrate how it works.

shift.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3				Example of calculation:					Calc #1 filter			Calc #2 filter
4						Year	Price		2017	0.87		2013	0.83
5						2000	0.7		2018	0.88		2014	0.84
6	Expected lifetime of ivestment (years)	10		Price in 2019 (Calc #1)		2001	0.71		2019	0.89		2015	0.85
7	Year of investment	2017		2725.54		2002	0.72					2016	0.86
8	Price of investment	4000				2003	0.73					2017	0.87
9						2004	0.74					2018	0.88
10						2005	0.75					2019	0.89
11	Expected lifetime of ivestment (years)	75		Price in 2019 (Calc #2)		2006	0.76
12	Year of investment	2013		3472.70		2007	0.77
13	Price of investment	10000				2008	0.78
14						2009	0.79
15						2010	0.8
16						2011	0.81
17						2012	0.82
18						2013	0.83
19						2014	0.84
20						2015	0.85
21						2016	0.86
22						2017	0.87
23						2018	0.88
24						2019	0.89
25
Sheet2

Cell Formulas
Range	Formula
I4:J6	I4	=FILTER($F$5:$G$24,$F$5:$F$24>=$B$7,"")
L4:M10	L4	=FILTER($F$5:$G$24,$F$5:$F$24>=$B$12,"")
D7	D7	=PRODUCT($B$8,$J$4:$J$23)
D12	D12	=PRODUCT($B$13,$M$4:$M$23)
Dynamic array formulas.

 

[bebo021999]

Using UDF in cell D7:
=calculate(B7,B8)
Then copy to D12.
with the source code:

Function Calculate(ByVal year As Range, ByVal price As Range) As Double
Dim cell As Range, n As Double
n = 1
    For Each cell In Range("F5:F24")
        If cell >= year Then n = n * cell.Offset(0, 1)
    Next
Calculate = price * n
End Function

Book1
	A	B	C	D	E	F	G
3				Example of calculation:
4						Year	Price
5						2000	0.7
6	Expected lifetime of ivestment (years)	10		Price in 2019 (Calc #1)		2001	0.71
7	Year of investment	2017		2,725.54		2002	0.72
8	Price of investment	4000				2003	0.73
9						2004	0.74
10						2005	0.75
11	Expected lifetime of ivestment (years)	75		Price in 2019 (Calc #2)		2006	0.76
12	Year of investment	2013		3,472.70		2007	0.77
13	Price of investment	10000				2008	0.78
14						2009	0.79
15						2010	0.8
16						2011	0.81
17						2012	0.82
18						2013	0.83
19						2014	0.84
20						2015	0.85
21						2016	0.86
22						2017	0.87
23						2018	0.88
24						2019	0.89
Sheet2

Cell Formulas
Range	Formula
D7,D12	D7	=calculate(B7,B8)

 

[Fluff]

Another formula option, without the helper cells

+Fluff v1.xlsm
	A	B	C	D	E	F	G
3				Example of calculation:
4						Year	Price
5						2000	0.7
6	Expected lifetime of ivestment (years)	10		Price in 2019 (Calc #1)		2001	0.71
7	Year of investment	2017		2725.54		2002	0.72
8	Price of investment	4000				2003	0.73
9						2004	0.74
10						2005	0.75
11	Expected lifetime of ivestment (years)	75		Price in 2019 (Calc #2)		2006	0.76
12	Year of investment	2013		3472.70		2007	0.77
13	Price of investment	10000				2008	0.78
14						2009	0.79
15						2010	0.8
16						2011	0.81
17						2012	0.82
18						2013	0.83
19						2014	0.84
20						2015	0.85
21						2016	0.86
22						2017	0.87
23						2018	0.88
24						2019	0.89
Sheet1

Cell Formulas
Range	Formula
D7	D7	=PRODUCT(B8,FILTER(G5:G24,F5:F24>=B7))
D12	D12	=PRODUCT($B$13,FILTER(G5:G24,F5:F24>=B12))

 

[kevin9999]

Another formula option, without the helper cells

+Fluff v1.xlsm
	A	B	C	D	E	F	G
3				Example of calculation:
4						Year	Price
5						2000	0.7
6	Expected lifetime of ivestment (years)	10		Price in 2019 (Calc #1)		2001	0.71
7	Year of investment	2017		2725.54		2002	0.72
8	Price of investment	4000				2003	0.73
9						2004	0.74
10						2005	0.75
11	Expected lifetime of ivestment (years)	75		Price in 2019 (Calc #2)		2006	0.76
12	Year of investment	2013		3472.70		2007	0.77
13	Price of investment	10000				2008	0.78
14						2009	0.79
15						2010	0.8
16						2011	0.81
17						2012	0.82
18						2013	0.83
19						2014	0.84
20						2015	0.85
21						2016	0.86
22						2017	0.87
23						2018	0.88
24						2019	0.89
Sheet1

Cell Formulas
Range	Formula
D7	D7	=PRODUCT(B8,FILTER(G5:G24,F5:F24>=B7))
D12	D12	=PRODUCT($B$13,FILTER(G5:G24,F5:F24>=B12))

Nice!