Assigning a value to a cell based on another

September 3, 2010 at 03:46:37
Specs: Windows XP

What I am trying to do in excel 2007 is : According to the value of 2 cells I need to define a value to another cell. For example: if A3=0 and A4=0, A7 will be 3000. if A3=1 and A4=0, A7 will be 4000. if A3=1 and A4=1, A7 will be 5000.

Can somebody help me???


See More: Assigning a value to a cell based on another

Report •


#1
September 3, 2010 at 04:20:10

A Nested IF-AND statement should get you what you want, however, you seemed to have left out one condition. What should the result be if A3=0 and A4=1?

With this formula the result will be a "Invalid Combination":

=IF(AND(A3=0,A4=0),3000, IF(AND(A3=1,A4=0),4000,
IF(AND(A3=1,A4=1),5000, "Invalid Combination")))

Note: The formula has been split into 2 lines for ease of viewing.


Report •

#2
September 3, 2010 at 04:51:05

Thank you so much.The result of A3=0 and A4=1 should be 6000. I didn't write the last condition to my question but I wrote 6000 instead of "Invalid Combination" and it works. Thank you so much again.

P.S. Just formula with comma didn't worked (may be because it is excel 2007) , I used semicolon and it works. FYI.


Report •

#3
September 3, 2010 at 06:42:25

re: "Just formula with comma didn't worked (may be because it is excel 2007)"

I don't think that it is because you're running Excel 2007. Rather I believe it is due to either your Excel language version and/or Windows Regional settings.

The default separator for all US-English based Excel versions in the US in a comma.

P.S. Just be aware that if you are using the following formula, you are going to get 6000 for any combination of values in A3 and A4 other than the three specifically used in the formula.

=IF(AND(A3=0,A4=0),3000,IF(AND(A3=1,A4=0),4000,
IF(AND(A3=1,A4=1),5000,6000)))


Report •

Related Solutions


Ask Question