Employee Number

[Sionos]

All,

I have a list of employees that i need to simplify into groups as listed below

Employee No's
1-19
20-50
50-199
200-499
500+

In my list of data (attached) that i receive i currently manually count the employee numbers and then put them into the relevant bands dependant on the employee sizes. Example:

Employee List
4
28
35
46
503
376
2000

Employee No's
1-19 - 1
20-50 - 1
50-199 - 3
200-499 - 1
500+ - 2

Any ideas?

[dominicb]

Good afternoon Sionos

I've used SumProduct. Does this look OK for you?

HTH

DominicB

[broro183]

Hi Sionos,

Please see the attached file for a couple of possible solutions - I prefer the sumproduct approach because it would be easy to change/add new "band ranges" into the file whereas IF statements can only be nested 7 times & are possibly harder to maintain.



hth
Rob

[corinereyes]

Hi broro183,

Also you can try this formula on sheet1!B2:

Please Login or Register  to view this content.

[Dav]

AND(A2<=19,A2<=50,A2<=499) evalutes to a2<=19 so what are you trying to achieve?

Regards

Dav

[Sionos]

Thanks guys for your input, I now have a verifiable, fully automatic way to achieve what i need, many thanks.

[broro183]

Thanks for the feedback Sionos :-)

Corine, sorry, like Dav I don't understand how your suggestion would work either but at least Sionos' problem is now solved.


Rob

[corinereyes]

Hi broro183, Dav,

Just try the formula and see if its working or not. I've attached a sample for you to check and sorry if i misunderstood siono's question...

[broro183]

ahhhh, I see...

Corine, no need to apologise, your formula does appear to work for your intent but I think you've misunderstood the question.

My understanding is that Sionos' first section ("employee No's") represents the groups (or "bands") which he/she needs the data to be summarised into (ie a count of the number of data rows that fall within each band).
The second section ("Employee List") is an example of the actual data (& I suspect that this would normally be listed alongside some text eg specific company names/departments, which has probably been removed for confidentiality reasons etc).
The third section is the bands with the expected results listed alongside each band based on the example data. For example, the "500+" band has 2 rows in the example data which have a number greater than 500 (503, 2000, in bold below).
I'd say they've just had a typing error with the results that don't match the example.

I have a list of employees that i need to simplify into groups as listed below

Employee No's
1-19
20-50
50-199
200-499
500+

In my list of data (attached) that i receive i currently manually count the employee numbers and then put them into the relevant bands dependant on the employee sizes. Example:

Employee List
4
28
35
46
503
376
2000
Employee No's
1-19 - 1
20-50 - 1
50-199 - 3
200-499 - 1
500+ - 2

Any ideas?

Hopefully that clarifies things for you Corine :-)


Rob

[Sionos]

Broro, I have one question to add to your already excellent solution. Next quarter i will receive a similar list of numbers to put into bandings and it may be a shorter or longer list, how do i easily change the cell ranges for the calculation?

Many thanks for your help so far

[Dav]

Just make the ranges too large and adjust the sumproduct to exclude blanks

g6: =SUMPRODUCT(($A$5:$A$3260<>"")*1,($A$5:$A$3260<=F6)*1,($A$5:$A$3260>=E6)*1)

H6: =COUNTIF($B$6:$B$3260,D6) if you still needed it

then copy them down into the cells to the cells below

Regards

Dav

[broro183]

hi all,

Sionos thanks for the feedback :-) & sorry about the slow response.

Dav's solution of providing a range that is "too large" will work but you could also use a "dynamic named range" that automatically resizes based on the number of entries in the particular column.

Dave Hawley's (?) Ozgrid site has a useful page for explaining dynamic ranges:

Heaps of other explanations can be found by Googling eg:

One day I may set up a couple of files & compare the 2 approaches for impacts on calculation speed, memory use etc...
At the moment though, my preferred approach is the dynamic range because it will always resize (providing calculation is set to automatic), whereas the fixed range which is currently "too large" may at some stage become too small without users even realising that not all info is being considered.


hth
Rob