excel formula with multiple conditions

[thomasks]

I have a spreadsheet that lists motors with HP and options like brakes and VFD. I need to look at the horsepower, and the VFD option and select a type of drive to use.
The HP is in cell B9, the VFD option (which says "Yes" or "No") is in cell C9, the type of drive used will go in cell H9.

The drive types are set up as follows:
The "ArmorStart LT" goes up to 2 HP in a VFD model and up to 5 HP in a "Contact" model
The "ArmorStart Classic" goes up to 5 HP in a VFD model and up to 10 HP in a "Contact" model

It is desirable to use the LT type when it can be.
If the HP is greater than 10 HP cell H9 should then say "Powerflex 40"

Thanks

 

[mintjulep]

Probably I'd use a Lookup Table.

 

[SkipVought]

Hi,

Please post a sample of your table that "lists motors with HP and options like brakes and VFD."

From there we can offer some suggestions based on your table structure.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kjv1611]

How about using an array formula?

http://www.cpearson.com/excel/arrayformulas.aspx

Here's a reference I refer to oftentimes, b/c I sometimes forget the context. He covers several different scenarios. Load the page, and search for multiple and you should find something that'll work in your situation.

If you've never used an array formula, be sure to read on how to use them.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[thomasks]

Here is an example of the table, I am trying to determine the values for H and I based on the values in B and C.

[tab]A[tab][tab]B[tab][tab]C[tab][tab]D[tab][tab]E[tab][tab]F[tab][tab][tab]G[tab][tab][tab]H[tab][tab][tab]I
1 Unit[tab]Hp[tab]VFD[tab]Brake[tab]FLA[tab]BRFLA[tab]Branch[tab]A/S type[tab]I/O available
2 1214[tab]3.00[tab]Yes[tab]No[tab]4.80[tab]4.8 [tab][tab]1[tab][tab] LT VFD[tab][tab] 6 config.
3 1216[tab]1.00[tab]Yes[tab]No[tab]2.10[tab]6.9 [tab][tab]1[tab][tab] LT VFD[tab][tab] 6 config.
4 1218[tab]5.00[tab]Yes[tab]No[tab]7.60[tab]14.5[tab][tab]1[tab][tab]Classic VFD[tab]4 in, 2 out
5 1222[tab]5.00[tab]No[tab]Yes[tab]7.60[tab]22.1[tab][tab]1[tab][tab]Classic VFD[tab]4 in, 2 out

 

[SkipVought]

Where are [highlight #EDD400]THESE[/highlight] values in your incomplete example???

The drive types are set up as follows:
The [highlight #FCE94F]"ArmorStart LT"[/highlight] goes up to 2 HP in a VFD model and up to 5 HP in a "[highlight #FCE94F]Contact"[/highlight] model
The "[highlight #FCE94F]ArmorStart Classic"[/highlight] goes up to 5 HP in a VFD model and up to 10 HP in a "Contact" model

It is desirable to use the LT type when it can be.
If the HP is greater than 10 HP cell H9 should then say [highlight #FCE94F]"Powerflex 40"[/highlight]

It appears that you have a fairly complex logic structure. Have you constructed a Truth Table using the requisite criteria for each of the value types in you SOW?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[thomasks]

It was difficult to get the columns and rows to line up in the post. So I had to manipulate it to show you the spreadsheet.
LT VFD is a non "Contact" model.
Classic VFD is a non "Contact" model.
"VFD" is a variable frequency model.
There is a "Contact" model that is non VFD. So if the VFD column says NO then the Type would be "Contact"
There is another VFD type called "Powerflex" for drives over 10 HP.
I have not made a "Truth table" and I don't know what you mean by "SOW

 

[SkipVought]

Statement of Work (SOW)

Why haven't you made a truth table? It's a tool that a programmer or any logician would find helpful in a situation like this.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[thomasks]

Here is my solution:
=IF(C9="Yes",IF(B9<=2,"LT VFD",IF(B9<=5,"ClassicVFD","Powerflex")), IF(B9<=5, "LT Contact", IF(B9<=10, "Classic Contact", "Powerflex")))
Thanks for your help.

 

[SkipVought]

Well your formula does not result in what you posted 10 Jun 14 8:26 for column H?????

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}