Weighted Average Calculation

Hello,

Please refer to attached spreadsheet. I am trying to split the KW of electricity equally between the machines in the factory. I can do this by multiplying the KW by the % use of each machine to give me a flat usage (column I). Where I am struggling is that each machine uses Varying amounts of electricity based on machine size (column J). Is there a formula I can put in K that will adjust the usage based on the machine size?

Any help would be greatly appreciated.

Kind regards,

OM

Thank you. Thats pulling through a revised KW usage for Machine 2 although it didn't run on that day?

Kind regards,

OM

Hi omlette_boy At the beginning, it is important to clarify that not everyone understands your structure file
May I know Which column is your Machine 2 ?

Apologies, Machine Number is column B. (this is recurring each day as No of machines doesn't change). The runtime in Minutes for each machine, each day is column C. There is a maximum availability of hours at 16 hours per day (column D)

In column I, i have calculated the KW that each machine would have used if all were the same size (multiplied the % of total hours each machine used by the total KW each day), however, that hasnt taken into account the varying machine sizes and therefore omits the fact that some machines use more KW than others. Looking for a formula that will come back to the daily usage weighted to each machine type.

Again I apologise if not clear.

Thank you for helping.

OM

That is amazing. Please can I ask what the *(A2=A$2:A$999)) is doing? (the only bit I'm struggling to follow.)

Thanks so much

Regards,

OM

Thank you, that returns a KW value even if the machine was inactive during the day?

Thanks again

OM