it1000
asked on
Company size based on number of employees
I have a column with number of employees: it's blank if unavailable, otherwise it's a numeric value, like 1, 5, 260, etc
I have another column called Company Size. I'd like to populate it as follows:
If number of employees is 1-5, fill in the text Micro
If number of employees is 6-250, fill in the text Small
If number of employees is 250-500, fill in the text Medium
If number of employees is 500-5000, fill in the text Large
If number of employees is 5000+, fill in the text X-Large
What would be the code to do this for one cell? I can drag fill the rest. Thanks
I have another column called Company Size. I'd like to populate it as follows:
If number of employees is 1-5, fill in the text Micro
If number of employees is 6-250, fill in the text Small
If number of employees is 250-500, fill in the text Medium
If number of employees is 500-5000, fill in the text Large
If number of employees is 5000+, fill in the text X-Large
What would be the code to do this for one cell? I can drag fill the rest. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Saqib Husain
=IF(A1="","",VLOOKUP(A1,{1
=IF(AND(A2>0,A2<6),"Micro"
drag formula from staring B2
A B
1 ID Company Size
2 0
4
3 -1
4 1 Micro
5 250 Small
6 5001 X-Large
drag formula from staring B2
A B
1 ID Company Size
2 0
4
3 -1
4 1 Micro
5 250 Small
6 5001 X-Large
Correct typing mistake, please try this that also take care to ID less than 0 such as -1 ,-1000,.....
=IF(AND(A2>0,A2<6),"Micro"
=IF(AND(A2>0,A2<6),"Micro"
here's another using Index & Match:
=INDEX({"","Micro","Small"
Looking at ssaqibh and mine I would possibly use:
=VLOOKUP(A1,{0,"";1,"Micro
=INDEX({"","Micro","Small"
Looking at ssaqibh and mine I would possibly use:
=VLOOKUP(A1,{0,"";1,"Micro
....or you could use LOOKUP....:)
=LOOKUP(A1,{0,1,6,251,501,
regards, barry
=LOOKUP(A1,{0,1,6,251,501,
regards, barry
But index and vlookup method will have "#N/A" when ID(at A1 or A2) is less than 0
=INDEX({"","Micro","Small"
=VLOOKUP(A1,{0,"";1,"Micro
So take this and compare the execution speed of three formula when drag it 5000 cells or 10000 cells
=IF(AND(A2>0,A2<6),"Micro"
drag formula from staring B2
A B
1 ID Company Size
2 0
4
3 -1
4 1 Micro
5 250 Small
6 5001 X-Large
=INDEX({"","Micro","Small"
=VLOOKUP(A1,{0,"";1,"Micro
So take this and compare the execution speed of three formula when drag it 5000 cells or 10000 cells
=IF(AND(A2>0,A2<6),"Micro"
drag formula from staring B2
A B
1 ID Company Size
2 0
4
3 -1
4 1 Micro
5 250 Small
6 5001 X-Large
I think that we are not very likely to find a company with less than no employees.
I think the question of speed is a good one, will test and see.
Well... with 60,000 lines of random numbers between 0 and 7,500 I cannot get a result of more than 0.015s on any of the methods.
So I do not think that speed is an issue for any of these.
I think the question of speed is a good one, will test and see.
Well... with 60,000 lines of random numbers between 0 and 7,500 I cannot get a result of more than 0.015s on any of the methods.
So I do not think that speed is an issue for any of these.
If you were to use nested IFs then there's no need for using AND here, try this version
=IF(A1>5000,"X-Large",IF(A
regards, barry
=IF(A1>5000,"X-Large",IF(A
regards, barry
barry, you can try it first, yours is not working,
and cell edit with "if" statement is easy code and running faster than vlook or index
when number of rows is huge
So take this and compare the execution speed of three formula when drag it 5000 cells or 10000 cells
=IF(AND(A2>0,A2<6),"Micro"
drag formula from staring B2
A B
1 ID Company Size
2 0
4
3 -1
4 1 Micro
5 250 Small
6 5001 X-Large
and cell edit with "if" statement is easy code and running faster than vlook or index
when number of rows is huge
So take this and compare the execution speed of three formula when drag it 5000 cells or 10000 cells
=IF(AND(A2>0,A2<6),"Micro"
drag formula from staring B2
A B
1 ID Company Size
2 0
4
3 -1
4 1 Micro
5 250 Small
6 5001 X-Large
>barry, you can try it first, yours is not working
which one? For positive numbers (which are the only numbers that make sense here) I think both of my suggestions are working fine - can you give me an example where they don't give the correct results?
regards, barry
which one? For positive numbers (which are the only numbers that make sense here) I think both of my suggestions are working fine - can you give me an example where they don't give the correct results?
regards, barry
Please take this, it will be faster than other marco
=IF(AND(A2>0,A2<6),"Micro"
drag formula from staring B2
A B
1 ID Company Size
2 0
4
3 -1
4 1 Micro
5 250 Small
6 5001 X-Large
=IF(AND(A2>0,A2<6),"Micro"
drag formula from staring B2
A B
1 ID Company Size
2 0
4
3 -1
4 1 Micro
5 250 Small
6 5001 X-Large
ASKER
Guys thank you for the replies. The very first post works great, especially that in some instances my form has 0, which is stupid, so vlookup methods give me N/A.
Thank you for replies, I chose the first one because it was the fastest reply, otherwise you're all very helpful.
Thank you for replies, I chose the first one because it was the fastest reply, otherwise you're all very helpful.