3 Long excel formulas, help needed, new at excel but trying to figure it out.

[kiki_123]

Please help, thank you in advance.


First formula:

C2= March 22, 13

B2= March 31, 2013

D2= 100%

F2,I 2 and M2= dates also


I started the formula but i get an error #value! when i try to add F2 is less than B2+5 then give me G2

This works:

=IF(AND(V2="yes"),IF(C2<B2,D2,0)),


But when I add the following (F2 is less than B2+5 then give me G2), I get the error:

IF(F2<B2+5,G2)


What am I doing wrong?


If U2= “Yes”

AND C2 is less than B2

V2 = D2

OR

If F2 is less than B2+5

V2 = G2

OR

If I2 is less than H2

V2 = J2

OR

If M2 is less than L2

V2 = N2

OR

If T2 is less than R2

V2 = S2

OR

If U2 is Yes

AND 2T is greater than 2R

2V = 0%


Second formula:

If 2R is Less than 2O+30

2S = 70%

OR

If 2R is Less than 2O+61

If 2R is Greater than 2O+30

2S = 65%

OR

If 2R is Less than 2O+91

If 2R is Greater than 2O+60

2S = 55%

OR

If 2R is Less than 2O+121

If 2R is Greater than 2O+90

2S = 45%

OR

If 2R is Less than 2O+151

If 2R is Greater than 2O+120

2S = 35%

OR

If 2R is Less than 2O+181

If 2R is Greater than 2O+150

2S = 25%

OR

If 2R is Greater than 2O+180

2S = 0%


Third formula:

C2 and O2 are dates.


If 2Q = Critical

2O = 2C + 60 days

If 2Q = Moderate

2O = 2C + 90 days

If 2Q = Minor

2O = 2C + 120 days

 

[Sajan]

Hello,

Please consider uploading a workbook. I tried to follow your chain of thought, but probably because it is nearly weekend, I am getting lost in your references.


Please see the notes at the top of the forum page for instructions on uploading files.


Cheers,

Sajan.

 

[NARAYANK991]

Hi Kiki ,


Let us take your first question :


C2 = March 22, 13

B2 = March 31, 2013

D2 = 100%

F2, I2 and M2 = dates also


=IF(AND(V2="yes"),IF(C2<B2,D2,0))


First , the AND operator needs to be used only if you have more than one condition ; here , you have only one condition viz. V2="YES". So , the above IF statement can be simplified to :


=IF(V2="yes",IF(C2<B2,D2,0))


What this statement will do is :


1. It will check if cell V2 contains the text YES ; since the condition check is case-insensitive , it doesn't matter whether V2 contains yes , YES , Yes or any other form of the word.


2. If cell V2 does not contain Yes , the result will be FALSE , since the IF statement has not specified what should be done in case the condition is not true.


3. If the cell V2 does contain the text YES , the statement will now check if cell C2 is less than cell B2 ; if it is , the result will be D2 , else the result will be 0.


As a result of the above IF formula , you have 3 possible outputs , FALSE , 0 , contents of D2.


Given the data you have posted , the formula will return 1 , or 100% if you have formatted the cell accordingly.


Now , to consider your next question :


Suppose you wish to add the following section :


If F2 is less than B2+5 then give me G2


Firstly , where do you wish to add this section ? This is one possibility :


=IF(V2="yes",IF(F2<B2+5,G2),IF(C2<B2,D2,0))


Narayan