Locating & Selecting Cell Range Excel 2007

Hello.

I'd like help with something if possible.

I have a very long list of names within a larger table (which also contains other data), the names usually appear once but their are instances of 2, 3, 4, etc. occurrences.
I'd like to find a way to get the range, as a result, for each unqiue name, i.e. In the format $F$45:$F$83, $F$3:$F$4, etc. Because I need the range to put into another function.
I am thinking I need to use an IF statement and VLOOKUP, but can't get to grips with the logic.

Much appreciated,
Ian

Formula are very bad at returning concatenated lists containing an arbitrary number of values, so you're very unlikely to get a usable formula which returns a list of ranges. Your options are:

1. Tell us what you're trying to do - it might be that you don't need a list of ranges plugging into your other functions, and that there's a way to do it using formula, or

2. Use VBA.

If you can show us an example workbook I'm sure we'd be able to advise you which is the best way to jump.

Thanks for your fast response!

To cut a long story short:
-I am using the FREQUENCY function to find the frequency of occurences of a data set to fit into 6 different bins.
-As the frequency function only outputs a vertical array I am then transposing the result into my original worksheet.
-The frequency function requires inputs of (a cell range, bin range) hence my original post for cell range.

I am new to statistical analysis with Excel, I was thinking I could use a pivot table but wasn't sure how to include statistics. What do you think I should do?
I'll be happy to give more detail if necessary. Thanks.

I have to confess I know nothing about pivot tables, but you should be able to feed an array into the FREQUENCY function, which would save you having to build a range. If you could post an example workbook with expected results that would really help me understand better.

What do you mean by "feed an array", what kind of array?

I have attached an Example Workbook. I have divided it into two sheets:
-'Final' is the sheet with the bins I'd like to populate with the raw data from 'Master Sheet'.
-So bin 12.5% means all info up to 12.5% of the total, i.e. 12.5% of 563 (=70.375), so for ABC PLC that would be 1, and so on.
-Since I have a long list of different names, I'd like to click and drag the result in the 'Final' sheet.

Thank you so much for your help : )

OK, I think I'm following you (my apologies if I'm not). In your example do you get the numbers you're looking for if you put the following formula into cell C3 and then copy across:

=FREQUENCY(IF('Master Sheet'!$A$3:$A$9=$A3,'Master Sheet'!$B$3:$B$9,$B3+1),C$2*$B3)

This is an array formula, so must be entered using Ctrl-Shift-Enter, not just enter.

It works!
Although I don't understand the IF statement.

So IF(full range of names = name in 'Final', use full range of numbers in 'Master Sheet', else...use the total in 'Final'?). How does the IF statement work to select the correct range of cells in 'Master Sheet'. Could you explain the logic please : )

Thanks!

I'll try to explain - what FREQUENCY is expecting is two lists of values. Normally the first list would be a range, but we can use an array instead. An array is just another kind of list, and it's the IF statement that builds that list. As an aside, in Excel arrays are normally indicated by curly brackets, so {1,2,3,4,5} is an array of the numbers 1-5. I'll stick to that convention below...

So, looking just at the IF statement:

IF('Master Sheet'!$A$3:$A$9=$A3,'Master Sheet'!$B$3:$B$9,$B3+1)

What it's doing is looking at each value in the range A3:A9 on the master sheet and seeing if each one of those values is equal to the value in cell A3 on the Final sheet.

So, in your example, this would create the array {TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE}, because the first 5 values are equal to the value in A3, but the last 2 are not.

Then, for each true statement it returns the value in corresponding cell in range B3:B9 on the master sheet, and for each false statement it returns the summed value from B3 on the Final sheet, plus one.

So the IF statement finally returns an array of {159,132,111,100,61,564,564}, because we're looking for values up to various percentiles the values returned by non-matching rows (i.e. the last two) just has to be higher than the total. I could just have had it return a big number, but I didn't know what your live data was like, so just making it one bigger than the sum value seemed easiest.

Does that make sense?

Sorry, just to add - I got a bit distracted by you using FREQUENCY for this one. You can use COUNTIFS to get the same result, which might be easier:

=COUNTIFS('Master Sheet'!$A$3:$A$9,$A3,'Master Sheet'!$B$3:$B$9,"<=" & C$2*$B3)

(With regards to your penultimate response)
That's very interesting, do IF statements always work like that, by creating an array with TRUEs & FALSEs?

No, only if they're given multiple values to check and you use Ctrl-Shift-Enter to make them an array formula. A lot of the time there are easier ways to achieve the same effect without having to deal with array formula (which can be tricky to get your head around sometimes).

If it makes you feel any better I didn't know any of this stuff when I joined this forum, but it's a great learning environment

That's fantastic, the COUNTIFS worked too. Much cleaner than using FREQUENCY, but was useful learning about arrays along the way!

Originally Posted by Andrew-R

Then, for each true statement it returns the value in corresponding cell in range B3:B9 on the master sheet, and for each false statement it returns the summed value from B3 on the Final sheet, plus one.

For each false statement, why did you choose to return B3 on the Final Sheet + 1? Is this arbitrary?

Pretty arbitrary, yes - it needed to return a value that was higher than the 100% percentile, so that FREQUENCY would never count it when returning the result.

I could have just picked a very big number, but I didn't know what numbers were in your real data. Putting in B3+1 meant that the value would always be beyond the 100% percentile, irrespective of what numbers you were using in your live data.

Oh right I understand, beacuse I was using the FREQUENCY function.

As an aside (I've been trying this for a while now). I am trying to figure out whether a required range has >= 4 cells. The code I have so far is:

=COUNTIFS('Master Sheet'!$A$3:$A$9, $A3 & ">=4")

But it doesn't work, I feel like I've missed something out, but I can't figure out what it is?

I think you want:

=COUNTIF('Master Sheet'!$A$3:$A$9, $A3) >=4

Ah forgive me, I actually figured it out before reading your post.
It's just that I've learnt so much Excel in the past couple of days, I feel like I need time for it to all sink in.

Do you know if it's possible to include an array as an arguement in an IF statement along with normal values?

I've constructed a long formula and I am assuming there's conflict with the type of values I am using.

The formula simply finds the largest 4 values in a list for a certain identifier & given that the identifier doesn't have 4 values, it then goes to find the largest 3 values for that identifier, and so on and so forth, so that the entire column is filled with values.

I have attached the formula as a word document

Wow, that's quite some formula

You can manually enter an array in a formula in Excel, so =SUM({1,2,3}) will return 6, but the rules are that anything within the {} has to be a literal value, you can't put other formula in there. So =SUM({1,2,2+1}) will generate an error.

I think the formula you're looking for here is:

=SUM(LARGE(INDEX(('Master Sheet'!$A$3:$A$9=$A3)*'Master Sheet'!$B$3:$B$9,0),{1,2,3,4}))

Entered with Ctrl-Shift-Enter - the INDEX function will create an array of the values in B3:B9 (where A3:A9=A3) and 0's where it does not.

So working with your sample file the array returned would be {159,132,111,100,61,0,0} the array at the end, coupled with the LARGE will return the 1st, 2nd, 3rd and 4th largest values from this array and SUM will add them all together.

You don't need to worry about there being less than 4 values for a given code, because if there are, say, only 3 values then the 4th largest element of the array will be zero, and therefore won't affect the sum.

Is that what you were after?