Excel Query

[Chrisgr31]

I have an excel spreadsheet which is effectively a properties and as an Yes/No/Maybe option against them. There are lots of properties in some towns, and the town field is a separate field.

So what I want to do is run a query which shows what percentage of properties in town A are yes, what percentage are No and what percentage are No and I want to do that for every town in the spreadsheet.

Is there an easy (easyish) way of doing it? Not sure what I have to search to find the answer!

[Chrisgr31]

Thanks, now all I need to do is investigate how to group my data by town.

Definitely a Friday job

[anxious_ant]

Chrisgr31 said:

Thanks, now all I need to do is investigate how to group my data by town.

Definitely a Friday job

Pivotable should do it. Drag town into row group and then the property under it. Under columns drag your Yes/No/Maybe. In values drag Yes/No/Maybe as a count and do show as percentage.

[MKnight702]

I would concatenate the town and yes/no then do a countif to total the various options and get a % that way. (or pivot the concatenation column)

Probably not the most elegant solution, but it is reasonably simple and quick!

[NewNameNeeded]

COUNTIFS would allow multiple variables - so you can include a town and whether it's a yes or no all in a single function.

[MOBB]

NewNameNeeded said:

COUNTIFS would allow multiple variables - so you can include a town and whether it's a yes or no all in a single function.

^ this is what I would do

[mmm-five]

anxious_ant said:

Pivotable should do it. Drag town into row group and then the property under it. Under columns drag your Yes/No/Maybe. In values drag Yes/No/Maybe as a count and do show as percentage.

Definitely a simple job for a pivotable, and just return the result as a % of the row.

If the data is laid out like this:


Then the pivot could return something like this: