# Multiple Logical Tests

#### NerdyRN

##### New Member
Hello - I am trying to complete this formula. The first part works but when I add the 2nd part I just get a "#VALUE! returned.

I am trying to say if cell A is <30 enter "Incomplete" if =>30 enter "Delinquent" but if cell B has a data in it (ex: a date) then ignore cell A and just enter "Complete". Here's what I have so far:

=IF([@[Days Post DC]]<30,"Incomplete","Delinquent"), IF([@[Date Resolved]],"Complete")

Thanks, the Nerdy RN

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Joe4

Welcome to the Board!

When nesting IF statements, you need to determine the priority things should be checked in, and then work from Left to Right, because as soon as a true condition is met, it stops right there and does not check the rest.

So, you want something like:
Code:
``=[COLOR=#333333]IF([@[Date Resolved]],"Complete",[/COLOR][COLOR=#333333]IF([@[Days Post DC]]<30,"Incomplete","Delinquent"))[/COLOR]``

#### NerdyRN

##### New Member
that did it! thanks!

#### Jazzmine16

##### New Member
Hello.. Can you help me with my multiple IF statements? I tried to many ways and it keeps giving me #Value! returned.

I'm trying to say if a tenant move in in 2016 (doesn't matter what's the month), then the fee is a formula of B*E*F, otherwise the fee should be equal to column D; and IF column A=0, then the fees should be ZERO.

=IF(YEAR(A2)<YEAR(2016),D2,B2*E2*F2),IF(A2=0,0)

#### Jazzmine16

##### New Member

Hello.. Can you help me with my multiple IF statements? I tried to many ways and it keeps giving me #Value! returned.

I'm trying to say if a tenant move in in 2016 (doesn't matter what's the month), then the fee is a formula of B*E*F, otherwise the fee should be equal to column D; and IF column A=0, then the fees should be ZERO.

<year(2016),d2,b2*e2*f2),if(a2=0,0)
'=IF(YEAR(A2)<YEAR(2016),D2,B2*E2*F2),IF(A2=0,0)

Fixing the formula above:

'=IF(YEAR(A2)<YEAR(2016),D2,B2*E2*F2),IF(A2=0,0)

</year(2016),d2,b2*e2*f2),if(a2=0,0)

#### Jazzmine16

##### New Member
Hello.. Can you help me with my multiple IF statements? I tried to many ways and it keeps giving me #Value! returned.

I'm trying to say if a tenant move in in 2016 (doesn't matter what's the month), then the fee is a formula of B*E*F, otherwise the fee should be equal to column D; and IF column A=0, then the fees should be ZERO.

=IF(YEAR(A2)<year(2016),d2,b2*e2*f2),if(a2=0,0)

Sorry not sure what happened with the formula I typed in and cannot delete my post.
Here's the formula

I cannot figure it out what's wrong with it. </year(2016),d2,b2*e2*f2),if(a2=0,0)

#### jtakw

##### Well-known Member

Hi,

Looking at your sample, your A Column dates appears to be Text values, here are two ways to do this:

Excel 2010
ABCDEF
1Move in MonthRentFeesCurrent Fees1st %2nd %
22013/101450116.15116.1578%7.20%
32015/05122584.7684.7678%7.20%
42016/011895106.4291.0478%7.20%
5012500.00078%7.20%
6
7If this column is formatted as General or Text
Sheet2
Cell Formulas
RangeFormula
C2=IF(A2="",0,IF(LEFT(A2,4)+0<2016,D2,B2*E2*F2))

Excel 2010
ABCDEF
1Move in MonthRentFeesCurrent Fees1st %2nd %
22013/101450116.15116.1578%7.20%
32015/05122584.7684.7678%7.20%
42016/011895106.4291.0478%7.20%
512500.00078%7.20%
6
7Make sure this column is formatted Custom: yyyy/mm
8Instead of 0, leave blank, otherwise will show: 1900/01
Sheet1
Cell Formulas
RangeFormula
C2=IF(A2="",0,IF(YEAR(A2)<2016,D2,B2*E2*F2))

Either way, C2 formula copied down.

#### jtakw

##### Well-known Member
Hi,

Correction to FIRST formula above:

=IF(A2=0,0,IF(LEFT(A2,4)+0<2016,D2,B2*E2*F2))

Replies
7
Views
296
Replies
11
Views
510
Replies
3
Views
379
Replies
12
Views
558
Replies
8
Views
160

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,409
Messages
5,831,448
Members
430,069
Latest member
bubbleboom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back