Calculate average of all values column A if all values are the same in column B and C

English
#1

Hi,

I am trying to calculate the average of a set of values from one column if the values are the same in 2 other columns.

Columns A has number of beds in a property - 1 or 2.
Column B has postcodes (EH1, EH2, EH3, etc)
Column C has monetary values.

I want to calculate the average value for C where the postcode and the number of beds are the same. Average value of property for all EH1s that have 1 bed.

And then the average value for all EH1s that have a 2 beds.

And the average value for all EH2s that have 1 bed.

And so on.
example-flats.ods (19.3 KB)

#2

How can “postcodes have a value of 1” (or 2)?
What would be the contents of col B and col C in an example row NOT meeting the intended condition if there only the two values 1 and 2 are allowed in column C?

Bestr attachan example .ods.

#3

I’ll make a wild guess that what you want is to have a small table somewhere of the different possibilities and use AVERAGEIFS() to calculate the individual averages.

See the attached ODS.

AverageIfsExample.ods (17.0 KB)

1 Like
#5

Yes, you can exactly apply the example sheet I provided to calculate what you need on your sheet.

Notice that it was not a good idea to put the word bedroom in each cell. In cases like this, rely on the header for the unit and just use numbers. :neutral_face:

Of course, if it is business you’re talking about, verify all results!

example-flats.ods (36.2 KB)

#7

That’s great, thanks @joshua4.

I really can’t see how that works. Why does it even mention “size”?

Is “Price” anything to do with the heading “Price Average”? I can’t see how.

What is the 2 at the end?

How does it know to take the data from column A, B, and C?

:slight_smile:

#8

Also, I pasted your cells into a separate sheet and the values have stayed the same (along with formula). I did recalculate and it hasn’t updated.

#9

LibreOffice help on AVERAGEIFS.

#10

(I don’t understand prices of about 400 thousand pounds for a bedroom. Is this about construction cost and all that …? No. Can’t believe.)

What you actually wanted to get seems to be a table containing a cell for each one of all the possible pairings of values of 2 variables: NumberOfBeds and ArbitraryCode where every such cell shows the average price calculated from all rows matching both variable values.
In this 2-variable-case it’s often recommendable for getting a good overview to arrange all the pairings in a two-dimensional scheme (an assignment table). Each calculated average is then placed in the cell where the column for a given number of beds crosses the row containing the specific code.

funnyBedroomPrices.ods (31.2 KB)

#11

@Lupp is right, you probably should use a 2-D arrangement in this particular case. However, the table set in columns is certainly an okay way to do it–and basically becomes necessary for 3-or-more-value cases. I come from a background of combinatorics, so repeated counting down columns is natural for me since it’s generalizable.

I think it might be a language thing, but in English “bedrooms” indicates the number of rooms in a flat that have dressing closets. It’s basically an indicator of the flat size. So a big flat is 3-bedroom. The price is for the entire flat. A 2-bedroom flat, if generous, might be, what, 120 m^2? Not $800,000 where I live, but I suppose understandable in London.

I don’t know where any of the sorting comes from. AVERAGEIFS does it all, there is no reason to sort unless a person wants to see the sorted data for themselves.

@sil, The Price, Size, Etc. are Named Ranges. You can just replace that part of the formula with the range, like $B$4:$B$24 (just an example)–just think of them as abstract labels you replace with range addresses. Or, read up a little on Named Ranges in LO or Excel and you’ll get the idea really fast.

#12

Concerning the limitations of 2D-arrays I agree anyway.
Thanks for the explanations concerning the number of NOT beds, but bedrooms. I should have known after all, but English customs are still difficult to understand for me. I would give the size of a flat by m^2 as the unit, and create classes.

#13

Thanks, that cleared things up. I have managed to get it working.

What is the benefit of using names ranges rather than just the range of cells in the columns in the same sheet? That really threw me until I found it :slight_smile:

#14

Yeah, a 1 bedroom can sell for those prices and more in London. There are 100s/1000s of rows and so the way joshua4 set it up works really well.

Thanks for your help.

#15

The task is made for a pivot table.

example-flats(1).ods (14,1 KB)

1 Like
#16

Sadly.

But how would you list a flat/apartment where the general living area and the area used for sleeping aren’t separated as different rooms.? I found a legal text from the US where the case is described by the term, “Zero-Bedroom-Dwelling”.

Finally and just for curiosity: What good for? (The averages and all. Collecting the data surely requires some effort. In what way does the result serve a purpose?)

Editing:
@sil: Thanks for your answer in the comment below.
The source where I found the term “zero-bedroom-dwelling” was from the USA.

#17

Hi @Lupp, The properties in my list are mostly if not all nice homes with kitchens, living rooms, etc. “Zero-Bedroom-Dwelling” is maybe a studio flat in the UK? I’m not sure.

The data I shared is just part of my spreadsheet and helps me find properties to turn into homes for disabled people. Many areas are too expensive and with their state benefits they couldn’t afford to live. This helps filter them out automatically.

#18

Named ranges offer a wide variety of benefits, depending on the exact circumstance. It is very easy to get an incorrect range address, or to forget a $ and have your range accidentally vary when you copy down a formula, etc. With a named range you just make sure all your names mark correct ranges (which is easy to do), then you can use abstract terms and know you are getting what you want. It isn’t always better, but often a ‘mature’ and ‘audit-ready’ spreadsheet will make use of named ranges.

#19

With a database you could reach a satisfactory solution to all these problems, a solution that works for many years to come. Spreadsheets were never intended to be used as databases even though the vast majority of today’s spreadsheet users try so desparately.

#4

@joshua4 are on the right track. I apologise for my bad explanation of my requirements. It seems obvious until someone points out the ambiguity. Also, I didn’t think I would be allowed to upload attachments being a new poster and didn’t see the upload link. There is an attachment now, thanks @Lupp.

#6

Oh, to continue the conversation, use the comment bubble (like in cartoons) rather than “Reply” or “Suggest a Solution”. Those are for a final answer.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.