Known values:
A | B | C | D |
---|---|---|---|
Tenant | Flat no. | Duration stayed | Energy consumed in flat |
To find out:
E |
---|
Energy consumed per tenant |
Condition:
I have to consider the unique value of column A
as an identifier in order to avoid duplicate results/errors in calculations.
Example:
4 tenants A2:A5
stay in the same flat B2:B5
, they have consumed 85KW D
over 30 days C
Simple Formula:
=D2/SUM(C2:C5)*C2
.
Manual process as per every flat
Formula to automate:
=IF(COUNTIF($A$2:A2,A2)=1,D2/SUMIF($B$2:$B$44,B2,$C$2:$C$44)*C2,"")
However this formula only works up to a certain range. If I increase the range for column B
, the result becomes incorrect.