Please ignore the first post, important information was missing. I have included it now:
Hi,
The aim of my calculations is to generate the weighted average of values from EZ Neu pro Std. which is the electricity produced per hour multiplied with the market price coming from sheet GHP.
I implemented the following formulas in sheet EZ&GHP in column O and P to derive the market price from sheet GHP (This is a separate sheet as the GHP and EZ Neu pro Std. come from different sources)
GHP:
=IF(IFNA(INDEX(GHP!$P$1:$P$4956;MATCH(1;INDEX((GHP!$A$1:$A$4956=$B3)*(GHP!$B$1:$B$4956=$K3));"")<0;0;IFNA(INDEX(GHP!$P$1:$P$4956;MATCH(1;INDEX((GHP!$A$1:$A$4956=$B3)*(GHP!$B$1:$B$4956=$K3));""))
The formula derives the values (they are the market prices) from sheet GHP whenever following criteria are met:
Uhrzeit NEU is found in column B of sheet GHP (the time of electricity production matches the time the market price is given)
Datum is found in column A of sheet GHP (the date of electricity production matches the date the market price is given)
Value in column P is greater Zero
Vermarktungswert: IFERROR((M3/SUMIFS($M$2:$M$100000;$G$2:$G$100000;G3;$H$2:$H$100000;H3;$J$2:$J$100000;J3;$O$2:$O$100000;">0")*O3);0)
For each cell in column Vermarktungswert the value from column EZ Neu pro Std. is divided by the sum of values from column EZ Neu pro Std. matching the criteria in columns Erzeugungsquelle (=Electricity plant), Leistung (=Energy) and Jahr ("Year").
The formula calculates the market value of an hour of produced energy and weighs it by the total number of hours where electricity is produced and market prices are positive.
Aim of this procedure is to produce a list of the total values in a similar format as you find in sheet FV PV. Currently I use a pivot table to get the result (Formulas would do the job as well I know).
Since the calculation takes a very long time I was wondering if it were possible to write a sub-procedure calculating this result in a separate sheet, as I am not familiar in writing VBA I was hoping someone in the forum could assist. Important would be that the sub procedure allows for the number of lines taken into consideration from sheet EZ&GHP to be expanded. In my original file I have 80.000 lines ( 1 line per electricity produced per hour)
Bookmarks