excel if condition or vba

Question

0.00/5 (No votes)

See more:

i have got data in an excel sheet in 3 columns, A,B,C

What i want is B to be populated automaticaly.

I want B to be populated if the following conditions are met,
PART 1
if A = 2012 and C = 1 then B = 1
if A=2011 and C =1 then B =2
if A =2010 and C= 1 then B=3
PART 2
if A = 2011 and C = 2 then B = 1
if A=2010 and C =2 then B =2
if A =2019 and C= 2 then B=3
PART 3
if A = 2010 and C = 3 then B = 1
if A=2009 and C =3 then B =2
if A =2008 and C= 3 then B=3
PART 4
if A = 2009 and C = 4 then B = 1
if A=2008 and C =4 then B =2
if A =2007 and C= 4 then B=3

PART 5
if A = 2008 and C = 5 then B = 1
if A=2009 and C =5 then B =2
if A =2006 and C= 5 then B=3

Please help me, can all the parts be combined in one formula

Posted 12-Apr-12 3:55am

mayeso

Add a Solution

Comments

Fredrik Bornander 12-Apr-12 15:32pm

Glad I could help :)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Fredrik Bornander · Accepted Answer · 2012-04-12T04:34:00

Solution 1

Assuming a spread sheet when column A holds your A values, C holds your C values and B holds the formula (starting at row two), then the formula for row 2 can be written as;

=IF(C2<5,2012-A2+1-(C2-1),IF(A2=2008,1,IF(A2=2009,2,3)))

Then copied to the other rows should fill those as well.

Hope this helps,
Fredrik

Posted 12-Apr-12 4:34am

Fredrik Bornander

Comments

Nelek 12-Apr-12 13:01pm

nice anser. +5

VJ Reddy 12-Apr-12 13:36pm

Good answer. 5!

Maciej Los 12-Apr-12 13:51pm

Great answer! My 5!

VJ Reddy · Accepted Answer · 2012-04-12T07:45:00

The Solution 1 given by Fredrik Bornander is very good.
I want to add that, as observed from the pattern of values in columns A, C, the total of A+C is 2013, 2012, 2011 under each part in the consecutive three rows, except for row 3 in Part 2 and row 2 in Part 5. I think by following the pattern there may be a typing error in these rows. If that is the case then the correct values could be 2009 in row3 of Part 2 and 2007 in row 2 of Part 5 in Column A and the following expression can also be used.

VB

=IF( AND(A2 <= 2012, A2 >= 2006, C2 >= 1, C2 <= 5),1-(A2+C2-2013),0)