Microsoft Microsoft office excel 2007 ac...

I have a list of 350 companies and I am trying to assign a label next to them from "small", "medium" or "large".

Column B contains the name of the company, column G contains turnover value, column H contains the number of employees and column I contains the balance sheet total for the companies.There is a diagram I am following but trying to put it into formula is proving difficult. It is: Does the company meet 2 of the following criteria?: have turnover which is less than 5.6 million (cell Q4), balance sheet total less than 2.8 million (cell Q5), less than 50 (cell Q6) employees. If the answer is Yes, they are classified as "small", but if it is no, then...

does the company meet 2 of the following criteria?: have turnover between 5.6 million (cell Q6) and 22.8 million (cell Q12), balance sheet total between 2.8 million (cell Q5) and 11.4 million (cell Q13), between 50 (cell Q6) and 250 (cell Q14) employees. If the answer is yes, they are classified as "medium" but if it is no, they are classified as "large" (when 2 out of the 3 values exceed the limit).I can create an If formula: =IF(OR(G2<=Q4,H2<=Q6,I2<=Q5),"SMALL","NO")

but this means it is just giving the answer if just 1 of the criteria was met not 2.and this one: =IF(AND(G2<=Q4,H2<=Q6)*OR(G2<=Q4,I2<=Q5)*OR(H2<=Q6,I2<=Q5),"SMALL","NO")

but I'm not sure if this is working because I want it to calculate if 2 out of 3 are less than the limit, e.g. there would be 3 combinations/permutations G2 and H2, or G2 and I2, or H2 and I2. I'm not sure that "or" in the calculation is doing what I want it to do and whether there is a simpler way of doing this because I then need to do it for medium and large sized companies, maybe in the same formula.Thank you

Try this: It tests the "2 out 3" criteria for Small. If all of those fail, it tests the "2 out 3" criteria for Large. If all of them fail, it returns Medium.

=IF(OR(

AND(G2<Q4,H2<Q6),

AND(G2<Q4,I2<Q5),

AND(H2<Q6,I2<Q5)),"Small",

IF(OR(

AND(G2>Q12,H2>Q14),

AND(G2>Q12,I2>Q13),

AND(H2>Q14,I2>Q13)),"Large",

"Medium"))I've split formula into multiple lines to make it easier to read.

Thank you, it worked.

Ask Your Question

Weekly Poll