Solar Monthly multiplier

N

NetZero

New Member
Joined
Aug 8, 2017
Messages
3
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
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]16-09-12[/TD]
[TD="class: xl65, width: 64"] 22,246[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"]06-09-13[/TD]
[TD="class: xl63, width: 64"] 7,150[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]07-05-14[/TD]
[TD="class: xl65, width: 64"] 57,753[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
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.

Code: 
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.
 
Upvote 0
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

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]kWh Per Annum (PA)[/TD]
[TD]Installation Date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]22,246[/TD]
[TD]16-09-12[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]7,150[/TD]
[TD]06-09-2013[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]57,753[/TD]
[TD]07-05-14[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]44,303[/TD]
[TD]08-10-14[/TD]
[/TR]
</tbody>[/TABLE]


Page 2

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Solar Percentage[/TD]
[TD]Cumulative Percentage[/TD]
[TD]Rest of Year Percentage[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]3.1[/TD]
[TD]3.1[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]5[/TD]
[TD]8.1[/TD]
[TD]96.9[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]8.7[/TD]
[TD]16.8[/TD]
[TD]91.9[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD]11.4[/TD]
[TD]28.2[/TD]
[TD]83.2[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]12.3[/TD]
[TD]40.5[/TD]
[TD]71.8[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]12.3[/TD]
[TD]52.8[/TD]
[TD]59.5[/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD]13.3[/TD]
[TD]66.1[/TD]
[TD]47.2[/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD]10.8[/TD]
[TD]76.9[/TD]
[TD]33.9[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]9.7[/TD]
[TD]86.6[/TD]
[TD]23.1[/TD]
[/TR]
[TR]
[TD]October[/TD]
[TD]6.5[/TD]
[TD]93.1[/TD]
[TD]13.4[/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD]3.8[/TD]
[TD]96.9[/TD]
[TD]6.9[/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD]3.1[/TD]
[TD]100[/TD]
[TD]3.1[/TD]
[/TR]
</tbody>[/TABLE]


Page 3 - kWh produced annually.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Insert Code here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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!!
 
Upvote 0
try this


Excel 2012
ABC
1LocationkWh Per Annum (PA)Installation Date
2A22,24616/09/2012
3B7,15006/09/2013
4C57,75307/05/2014
5D44,30308/10/2014
645,67821/02/2017
Sheet1



Excel 2012
ABCD
1MonthSolar PercentageCumulative PercentageRest of Year Percentage
2January3.13.1100
3February58.196.9
4March8.716.891.9
5April11.428.283.2
6May12.340.571.8
7June12.352.859.5
8July13.366.147.2
9August10.876.933.9
10September9.786.623.1
11October6.593.113.4
12November3.896.96.9
13December3.11003.1
Sheet2



Excel 2012
ABCDEFG
1Location201220132014201520162017
2A5,13900000
3B01,6520000
4C0041,467000
5D005,937000
60000044,262
Sheet3
Cell Formulas
RangeFormula
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

Code: 
=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))
 
Last edited:
Upvote 0
this one works with locations in Sheet1, Column A are not in order

Code: 
=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))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
change cell colour
Replies
0
Views
446
bookartco
B
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
N
  • Locked
  • Question Question
Solar Calculator
Replies
1
Views
825
RoryA
RoryA
J
  • Question Question
Dynamic chart (using dynamic range?)
Replies
1
Views
597
Micron
M
A
  • Question Question
Sales Related Questions
Replies
4
Views
673
anneb_87
A

Forum statistics

Threads
1,223,883
Messages
6,175,168
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top