3

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.

Link to Example Spreadsheet

Ref. image-1

Ref. image-2

Improve this question
0

1 Answer 1

Reset to default
1

The problem with your data is that there are several groups of data by the same flat number in column B, while only one group of data should be considered at a time. For example, flat 102 appears not only on rows 6, 7 and 8, but also in rows 91, 92, 93, 94 and 95.

The formula below will match the desired results you show. It creates a "window" to data that spans ten rows above and below the current cell, and only includes flats with a matching flat number within that window:

=let( 
  flats, B2:B, durations, C2:C, consumptions, D2:D, 
  subset_, lambda(flat, 
    sum(filter( 
      durations, 
      flats = flat, 
      isbetween(row(flats), row(flat) - 10, row(flat) + 10) 
    )) 
  ), 
  map( 
    flats, durations, consumptions, 
    lambda( 
      flat, duration, consumption, 
      if( 
        isblank(flat), 
        iferror(ø), 
        consumption * duration / subset_(flat) 
      )  
    ) 
  ) 
)
Improve this answer
2
  • Hi doubleunary, First of all, Thank you! This worked like a charm! Yes, they have common flat names in many cases which is why I wanted to find a way to consider the row in such a way that it could act as a unique identifier, in order to avoid miscalculations and give accurate results. I have updated the sheet link along with a column H, in which I used your code. I'm not good in coding but I understood the logic. Just curious though, can the same be done through google sheet functions without line of codes? Thanks again, much appreciated.
    – Shiv
    May 29 at 19:13
  • 1
    The formula is using plain vanilla Google Sheets spreadsheet functions. It is just formatted with newlines and indentation to make it more readable.
    – doubleunary
    May 30 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.