Be Excellent To Each Other

If I'm, oh I dunno, running a list of people that were arrested during 11pm punchups outside pubs in several cituies, from one week to the next, and wanted to know the frequency of each offender's detainment, what's the best way of doing this?

I'll be wanting to get data from 5 sheets and stick it all in one, and then to be able to look and say "Scrappy Mcfighty has been arrested every weekend this month".

I thought about VLOOKUP each value, but then it gets complicated from the third column onwards, as I've got to start chucking filters on. Is there something that'll say "Scrappy Mcfighty each week" or, even better, "Scrappy McFighty in Manchester, Leeds, Bradford and bingley"?

Pivot tables might help to summarise the data a bit.

Pivot tables are cool.

Or use the countif function multiple times for each combination you want to test.

Yeah. I think I'm going to have to break the information down to person by location, and then deep diving to look for any patterns. I'm trying to work my head around which questions I need to ask , if you see what I mean, to find out why X,Y and Z do Q. or something. I'm going home in five minutes, so I'll have a think about it in the shower tomorrow.

I think the questions are "Who is where and how often?", "Who is most often where" and "Is it the person, and other(s) in location(s)". I need to discover if it is the person or the location at first, then look at different people in that location. This could be quite a big spreadsheet.

Put it in an Access database. Heh.

You want a COUNTIF, don't you? Or, more accurately, a SUM of COUNTIFs.

So say sheet1 to sheet5 had the list of offenders in the A column, to find out how often the chap in the first cell of the first sheet had been arrested you'd want:


I hope there's a better way of doing it than that

Stop it.

Tools > Data Analysis. Histogram Plug-in might help.

That works, thank you.

How could I combine these two things?



EDIT: I'm probably better off doing all the calculations on a sheet, theng enerating the outcome in a different one, aren't I?

Gah! Scrub the above.

If the number is between 12-30 I want it to say "Check", 0-12 "OK" and >30 "Attention". I also want it to do work for negative numbers, so if there is a +ve or -ve swing, it'll pick it up. I can't nest these calculations.

Sorry to be a pain.

Have one calculation create the result in another column, and then do your next calculation on the new column?

Assuming "the number" is in A1:


When you say "work for negative numbers", do you mean that something lower than -30 will also say "Attention"?

Yes, that'd be lovely. The real problem I'm havin is nesting the statements, I think.

ABS is the function you want.

That's superb, thank you. With your help I've come up with this:



<proud>