Formula to create a classification

[linksavage]

This will be an easy one for someone out there.

I need a formula that will evaluate 3 combinations of 2 variables (Sales and Employees) and lookup the correct classification. Tried using Nested IFs but it got very confusing very quickly for me!

For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sales[/TD]
[TD]Employees[/TD]
[TD]Classification[/TD]
[/TR]
[TR]
[TD]< 1600[/TD]
[TD]< 10[/TD]
[TD]Micro[/TD]
[/TR]
[TR]
[TD]< 8000[/TD]
[TD]< 50[/TD]
[TD]Small[/TD]
[/TR]
[TR]
[TD]< 40000[/TD]
[TD]< 250[/TD]
[TD]Medium[/TD]
[/TR]
[TR]
[TD]If there is not a[/TD]
[TD]match on both, then:[/TD]
[TD]Large[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!

 

[shg]

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]

Sales​

[/td][td]

Employees​

[/td][td]

Classification​

[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

1,600​

[/td][td]

10​

[/td][td]Micro[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

8,000​

[/td][td]

50​

[/td][td]Small[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

40,000​

[/td][td]

250​

[/td][td]Medium[/td][td][/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

1,000​

[/td][td]

9​

[/td][td]Micro[/td][td]C7: =IFERROR(INDEX($C$2:$C$4, MATCH(1, INDEX((A7<$A$2:$A$4) * (B7<$B$2:$B$4), 0), 0)), "Large")[/td][/tr]

[tr][td]

8​

[/td][td]

1,000​

[/td][td]

10​

[/td][td]Small[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

40,000​

[/td][td]

1​

[/td][td]Large[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

2,000​

[/td][td]

40​

[/td][td]Small[/td][td][/td][/tr]
[/table]

 

[linksavage]

Thank you! Seems to be just what I needed ...

 

[shg]

You're welcome.

 

[linksavage]

Oops, I spoke too soon ...

Your recommended formula works exactly as desired in my Excel 2010, but I need the formula to work in a system that uses an "embedded" set of Excel 97-2003 capabilities, as shown here:

http://poi.apache.org/spreadsheet/formula.html

Unfortunately, the guidance on supported vs non-supported Excel functions is limited ... from the link above:

Supported Features

• References: single cell & area, 2D & 3D, relative & absolute
• Literals: Number, text, boolean, error and array
• Operators: arithmetic and logical, some region operators
• Built-in functions: over 350 recognised, 280 evaluatable
• Add-in functions: 3 from Analysis Toolpack

Not yet supported

• Manipulating array/table formulas (In Excel, formulas that look like "{=...}" as opposed to "=...")
• Region operators: union, intersection
• Parsing of previously uncalled add-in functions
• Preservation of whitespace in formulas (when POI manipulates them)

Can this be accomplished in any other way?

 

[shg]

I would have no way to test a suggestion. Maybe you should try an Apache POI forum.

 

[linksavage]

Based upon this, it appears that ISERROR can be used in place of IFERROR using this syntax ... and I believe would be supported in the POI framework:

if(iserror(your formula),"",your formula)

So will work on trying this next ...

 

[linksavage]

I've now determined that I cannot use ISERROR or IFERROR, but that I can use the IF and ERROR.TYPE formulas ... so can someone please help me convert the formula provided by shg (below) to using IF and ERROR.TYPE instead:

=IFERROR(INDEX($C$2:$C$4, MATCH(1, INDEX((A7<$A$2:$A$4) * (B7<$B$2:$B$4), 0), 0)), "Large")

Thanks!