IF,AND Statements, and maybe drop down list

**Rieck2000** · 01-20-2011, 04:57 PM

Hi Everyone,

Im new to this site, but I have not been able to figure out this problem yet, so any help would be great!

Here it is,

I have in column A1 to A3 companies A,B,C.. These companies may be switched around (B,C,A) depending on which company is there at the time.

Column B1 to B3 will correspond to days, (could be anything from 1 to infinity)

Column C1 to C3 will be the main formula (anything from 0 to infinity again)

My problem is, I can make an if AND statement for each individual company, but its when they start changing around that it gets tricky for me.

I would like in the end, for the user to pick a company (A,B,C) and that corresponding Formula to be used in column C.

For example , here are my if AND statements

Company A : IF(AND(A:A="A",B:B<14),0,B:B-14)
Company B : IF(AND(A:A="B",B:B<14),0,B:B-14)
Company C: IF(AND(A:A="C",B:B<10),0,B:B-10)

These formulas will be used in Column C.. Is there a way to combine all these if statements in one single long statement.. I would like the user to just choose which company and how many days in column B, and the appropriate formula choosen

Thank you for all the help!!!

**Rieck2000** · 01-20-2011, 05:49 PM

Hi Everyone,

Im new to this site, but I have not been able to figure out this problem yet, so any help would be great!

Here it is,

I have in column A1 to A3 companies A,B,C.. These companies may be switched around (B,C,A) depending on which company is there at the time.

Column B1 to B3 will correspond to days, (could be anything from 1 to infinity)

Column C1 to C3 will be the main formula (anything from 0 to infinity again)

My problem is, I can make an if AND statement for each individual company, but its when they start changing around that it gets tricky for me.

I would like in the end, for the user to pick a company (A,B,C) and that corresponding Formula to be used in column C.

For example , here are my if AND statements

Company A : IF(AND(A:A="A",B:B<14),0,B:B-14)
Company B : IF(AND(A:A="B",B:B<14),0,B:B-14)
Company C: IF(AND(A:A="C",B:B<10),0,B:B-10)

These formulas will be used in Column C.. Is there a way to combine all these if statements in one single long statement.. I would like the user to just choose which company and how many days in column B, and the appropriate formula choosen

Thank you for all the help!!!

**DonkeyOte** · 01-20-2011, 06:50 PM

Perhaps you just want:

Please Login or Register  to view this content.

**Rieck2000** · 01-20-2011, 07:14 PM

Hi,

OMG! i would of never done it that way!! Thank you sooo much!!!!!

So now i can just change the company (A,B,C) and the formula will recognize my restraints!!

One more question:

Say i change one condition in my second if statement for company B from 14 to say 18.. or any number,, how different would your code read?

Thanks alot!!

**DonkeyOte** · 01-21-2011, 03:28 AM

Considering just 3 companies:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

the latter would throw an error if A1 were neither A nor B nor C.

**masteff** · 01-21-2011, 04:23 PM

Since your "then" portion of each formula is zero and since only one can be true at a time then simply string them all together using plus signs ( + ).

=IF(AND(A:A="A",B:B<14),0,B:B-14) + IF(AND(A:A="B",B:B<14),0,B:B-14) + IF(AND(A:A="C",B:B<10),0,B:B-10)

Edit: hmm, actually the way those are written, could have more than one true at a time... would need to rewrite them so only one true at a time for my suggestion to work.

Perhaps by changing your equalities and flipflopping the "then" and "else" parts:
=IF(AND(A:A="A",B:B>=14),B:B-14,0) + IF(AND(A:A="B",B:B>=14),B:B-14,0) + IF(AND(A:A="C",B:B>=10),B:B-10,0)

**DonkeyOte** · 01-21-2011, 04:45 PM

@masteff, this thread has since been duplicated:

http://www.excelforum.com/excel-gene...tatements.html

I did not notice the duplicate at the time but the above thread has had a few responses and as such I will close this thread
[edit: I will merge but note posts will appear a little out of sync]

@Rieck2000 - please be sure to read and follow the Board Rules when posting