Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by it1000
at 2024-08-11 23:15:29
ModifyPoint:500 Replies:13 POST_ID:828735USER_ID:11639
Topic:
Microsoft Excel Spreadsheet Software;;
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
Author: it1000 replied at 2024-08-12 20:57:22
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.
Expert: duncanb7 replied at 2024-08-12 13:05:16
Please take this, it will be faster than other marco
=IF(AND(A2>0,A2<6),"Micro",(IF(AND(A2>5,A2<251),"Small",(IF(AND(A2>249,A2<501),"Medium",(IF(AND(A2>499,A2<5001),"Large",(IF(A2>5000,"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
=IF(AND(A2>0,A2<6),"Micro",(IF(AND(A2>5,A2<251),"Small",(IF(AND(A2>249,A2<501),"Medium",(IF(AND(A2>499,A2<5001),"Large",(IF(A2>5000,"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
Expert: barry houdini replied at 2024-08-12 13:01:07
>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
Expert: duncanb7 replied at 2024-08-12 05:30:07
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",(IF(AND(A2>5,A2<251),"Small",(IF(AND(A2>249,A2<501),"Medium",(IF(AND(A2>499,A2<5001),"Large",(IF(A2>5000,"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
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",(IF(AND(A2>5,A2<251),"Small",(IF(AND(A2>249,A2<501),"Medium",(IF(AND(A2>499,A2<5001),"Large",(IF(A2>5000,"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
Expert: barry houdini replied at 2024-08-12 05:03:18
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(A1>500,"Large",IF(A1>250,"Medium",IF(A1>5,"Small",IF(A1>0,"Micro","")))))
regards, barry
=IF(A1>5000,"X-Large",IF(A1>500,"Large",IF(A1>250,"Medium",IF(A1>5,"Small",IF(A1>0,"Micro","")))))
regards, barry
Expert: Steve replied at 2024-08-12 04:52:58
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.
Expert: duncanb7 replied at 2024-08-12 04:47:22
But index and vlookup method will have "#N/A" when ID(at A1 or A2) is less than 0
=INDEX({"","Micro","Small","Medium","Large","X-Large"},MATCH(A1,{0,1,6,251,501,5001},1))
=VLOOKUP(A1,{0,"";1,"Micro";6,"Small";251,"Medium";501,"Large";5001,"X-Large"},2)
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",(IF(AND(A2>5,A2<251),"Small",(IF(AND(A2>249,A2<501),"Medium",(IF(AND(A2>499,A2<5001),"Large",(IF(A2>5000,"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
=INDEX({"","Micro","Small","Medium","Large","X-Large"},MATCH(A1,{0,1,6,251,501,5001},1))
=VLOOKUP(A1,{0,"";1,"Micro";6,"Small";251,"Medium";501,"Large";5001,"X-Large"},2)
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",(IF(AND(A2>5,A2<251),"Small",(IF(AND(A2>249,A2<501),"Medium",(IF(AND(A2>499,A2<5001),"Large",(IF(A2>5000,"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
Expert: barry houdini replied at 2024-08-12 04:44:54
....or you could use LOOKUP....:)
=LOOKUP(A1,{0,1,6,251,501,5001;"","Micro","Small","Medium","Large","X-Large"})
regards, barry
=LOOKUP(A1,{0,1,6,251,501,5001;"","Micro","Small","Medium","Large","X-Large"})
regards, barry
Expert: Steve replied at 2024-08-12 04:33:17
here's another using Index & Match:
=INDEX({"","Micro","Small","Medium","Large","X-Large"},MATCH(A1,{0,1,6,251,501,5001},1))
Looking at ssaqibh and mine I would possibly use:
=VLOOKUP(A1,{0,"";1,"Micro";6,"Small";251,"Medium";501,"Large";5001,"X-Large"},2)
=INDEX({"","Micro","Small","Medium","Large","X-Large"},MATCH(A1,{0,1,6,251,501,5001},1))
Looking at ssaqibh and mine I would possibly use:
=VLOOKUP(A1,{0,"";1,"Micro";6,"Small";251,"Medium";501,"Large";5001,"X-Large"},2)
Expert: duncanb7 replied at 2024-08-12 00:21:27
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>5,A2<251),"Small",(IF(AND(A2>249,A2<501),"Medium",(IF(AND(A2>499,A2<5001),"Large",(IF(A2>5000,"X-large","")))))))))
=IF(AND(A2>0,A2<6),"Micro",(IF(AND(A2>5,A2<251),"Small",(IF(AND(A2>249,A2<501),"Medium",(IF(AND(A2>499,A2<5001),"Large",(IF(A2>5000,"X-large","")))))))))
Expert: duncanb7 replied at 2024-08-11 23:45:16
=IF(AND(A2>0,A2<6),"Micro",(IF(AND(A2>5,A2<251),"Small",(IF(AND(A2>249,A2<501),"Medium",(IF(AND(A2>499,A2<5001),"Large",(IF(A2>5000,"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
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
Expert: Saqib Husain, Syed replied at 2024-08-11 23:40:03
=IF(A1="","",VLOOKUP(A1,{1,"Micro";6,"Small";251,"Medium";501,"Large";5001,"X-Large"},2))
Accepted Solution
Expert: duncanb7 replied at 2024-08-11 23:38:05
500 points EXCELLENT
IF(AND(A2>0,A2<6),"Micro",(IF(AND(A2>5,A2<251),"Small",(IF(AND(A2>249,A2<501),"Medium",(IF(AND(A2>499,A2<5001),"Large",(IF(A2>4999,"X-large",0)))))))))
