Solar Monthly multiplier

[NetZero]

Hi there! Debut post =)

I'm trying to do some calculations but I can only see a long manual way round so looking for a much easier way!

What I'm trying to do is work out how much solar energy (kWh) has been generated since the installation date of some solar panels.
I have a bunch of dates in column A which are assigned to annual kWh values in column B. So I need to multiply that value by the number of years that have passed since that date.

I then need to multiply the remaining months by a multiplier (a percentage) specific to each month that is left over.
A B

16-09-12	22,246
06-09-13	7,150
07-05-14	57,753

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

January 3.1
February 5
March 8.7
April 11.4
May 12.3
June 12.3
July 13.3
August 10.8
September 9.7
October 6.5
November 3.8
December 3.1

Any help is greatly appreciated thank you!

 

[Roxxien]

Hello and welcome on the forum.

I found a good solution for you by creating an User Defined Function (UDF).

The function use 2 parameter. The first one is the installation date. The second one is a range of 12 row representing the multiplier from January to December.

By using that second parameter, you will be able to modify them if necessary put your value where you want.

Option Explicit


Function solarmultiplier(ByVal InstallationDate As Date, ByVal monthlymultiplier As Variant)
Dim d As Date
Dim diff As Currency
Dim c As Currency
On Error GoTo Problem
d = Date
InstallationDate = Int(InstallationDate)


'Part to close the installation month
Select Case Month(InstallationDate)
    Case 1, 3, 5, 7, 8, 10, 12
        c = (31 - Day(InstallationDate) + 1) / 31
    Case 2
        If Year(InstallationDate) / 4 = Int(Year(InstallationDate) / 4) Then
            c = (29 - Day(InstallationDate) + 1) / 29
        Else
            c = (28 - Day(InstallationDate) + 1) / 28
        End If
    Case 4, 6, 9, 11
        c = (30 - Day(InstallationDate) + 1) / 30
End Select
c = c * monthlymultiplier(Month(InstallationDate))
diff = diff + c
c = 0


'Part to add full year
Do Until DateAdd("yyyy", 1, InstallationDate) > d
    InstallationDate = DateAdd("yyyy", 1, InstallationDate)
    diff = diff + 100
Loop
InstallationDate = DateSerial(Year(InstallationDate), Month(InstallationDate) + 1, 1)


'Part to add full month
Do Until DateAdd("m", 1, InstallationDate) > d
    InstallationDate = DateAdd("m", 1, InstallationDate)
    diff = diff + monthlymultiplier(Month(InstallationDate))
Loop


'Part to adjust to the actual day of the month
Select Case Month(d)
    Case 1, 3, 5, 7, 8, 10, 12
        c = Day(d) / 31
    Case 2
        If Year(d) / 4 = Int(Year(d) / 4) Then
            c = Day(d) / 29
        Else
            c = Day(d) / 28
        End If
    Case 4, 6, 9, 11
        c = Day(d) / 30
End Select
c = c * monthlymultiplier(Month(InstallationDate))
diff = diff + c


solarmultiplier = diff / 100
Exit Function
Problem:
solarmultiplier = Err.Description
End Function

Now, how to use an UDF
1. Open the VB editor in Excel with Alt+F11
2. Create a new module by right click on the left window then insert->Module
3. Paste the code above COMPLETELY
4. Close the VB editor
5. "Save as" your workbook in a format the enabled macro (.xlsm for example)
6. You now have a new Excel function in that workbook that you can use like any other natural function

I highly suggest you try this in a copy of your original workbook in case of problem.
Compare the result to your original data to see if everything is correct.

I hope to have solved your problem.

 

[NetZero]

Thank you for your reply, unfortunately I have absolutely no clue how to use a UDF and don't really understand what I am supposed to do once I have saved the workbook with the macro in it.

I have been thinking on this problem and have perhaps come up with a better way of wording it to be clearer in my goal.

I am trying to determine the kWh produced by several solar systems from installation to today, but have to multiply months in incomplete years by a percentage due to the fact that solar power is not generated evenly throughout the year.

So I have 3 pages.

Page 1

Location	kWh Per Annum (PA)	Installation Date
A	22,246	16-09-12
B	7,150	06-09-2013
C	57,753	07-05-14
D	44,303	08-10-14

<tbody>
</tbody>

Page 2

Month	Solar Percentage	Cumulative Percentage	Rest of Year Percentage
January	3.1	3.1	100
February	5	8.1	96.9
March	8.7	16.8	91.9
April	11.4	28.2	83.2
May	12.3	40.5	71.8
June	12.3	52.8	59.5
July	13.3	66.1	47.2
August	10.8	76.9	33.9
September	9.7	86.6	23.1
October	6.5	93.1	13.4
November	3.8	96.9	6.9
December	3.1	100	3.1

<tbody>
</tbody>

Page 3 - kWh produced annually.

Location	2012	2013	2014	2015	2016	2017
A	Insert Code here
B
C
D

<tbody>
</tbody>

An idea of what the code is trying to achieve is broken down into 4 steps:

1. IF installation date (year) > column header (year) THEN input 0
2. IF installation date (year) = column header (year) THEN find month (page 2) and multiply kWh PA (page 1) by rest of year percentage (page 2) of relevant month for installation date.
3. IF installation date (year) = 2017 THEN find month (page 2) and multiply kWh PA (page 1) by cumulative % of =MONTH(TODAY()) - cumulative % of relevant month for installation date.
4. ELSE input kWh PA (page 1).

Help is greatly appreciated!!

 

[AlanY]

try this

Excel 2012
	A	B	C
1	Location	kWh Per Annum (PA)	Installation Date
2	A	22,246	16/09/2012
3	B	7,150	06/09/2013
4	C	57,753	07/05/2014
5	D	44,303	08/10/2014
6		45,678	21/02/2017
Sheet1

Excel 2012
	A	B	C	D
1	Month	Solar Percentage	Cumulative Percentage	Rest of Year Percentage
2	January	3.1	3.1	100
3	February	5	8.1	96.9
4	March	8.7	16.8	91.9
5	April	11.4	28.2	83.2
6	May	12.3	40.5	71.8
7	June	12.3	52.8	59.5
8	July	13.3	66.1	47.2
9	August	10.8	76.9	33.9
10	September	9.7	86.6	23.1
11	October	6.5	93.1	13.4
12	November	3.8	96.9	6.9
13	December	3.1	100	3.1
Sheet2

Excel 2012
	A	B	C	D	E	F	G
1	Location	2012	2013	2014	2015	2016	2017
2	A	5,139	0	0	0	0	0
3	B	0	1,652	0	0	0	0
4	C	0	0	41,467	0	0	0
5	D	0	0	5,937	0	0	0
6		0	0	0	0	0	44,262
Sheet3

Cell Formulas
Range	Formula
B2	=IF(YEAR(Sheet1!$C2)=B$1,Sheet1!$B2*INDEX(Sheet2!$D$2:$D$13,MONTH(Sheet1!$C2),0)/100,IF(AND(YEAR(TODAY())=B$1,YEAR(Sheet1!$C2)=YEAR(TODAY())),Sheet1!$B2*INDEX(Sheet2!$D$2:$D$13,MONTH(TODAY()),0)/100,0))

formula in B2 copy down and across

=IF(YEAR(Sheet1!$C2)=B$1,Sheet1!$B2*INDEX(Sheet2!$D$2:$D$13,MONTH(Sheet1!$C2),0)/100,IF(AND(YEAR(TODAY())=B$1,YEAR(Sheet1!$C2)=YEAR(TODAY())),Sheet1!$B2*INDEX(Sheet2!$D$2:$D$13,MONTH(TODAY()),0)/100,0))

 

[AlanY]

this one works with locations in Sheet1, Column A are not in order

=IF(YEAR(Sheet1!$C2)=B$1,INDEX(Sheet1!$B:$B,MATCH(Sheet3!$A2,Sheet1!$A:$A,0))*INDEX(Sheet2!$D$2:$D$13,MONTH(Sheet1!$C2),0)/100,IF(AND(YEAR(TODAY())=B$1,YEAR(Sheet1!$C2)=YEAR(TODAY())),INDEX(Sheet1!$B:$B,MATCH(Sheet3!$A2,Sheet1!$A:$A,0))*INDEX(Sheet2!$D$2:$D$13,MONTH(TODAY()),0)/100,0))