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

0

1 Answer 1

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) 
      )  
    ) 
  ) 
)
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, 2023 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. May 30, 2023 at 20:12

Your Answer

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

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