Multiple Logical Tests

NerdyRN

New Member
Joined
Mar 10, 2016
Messages
2
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,206
Office Version
  1. 365
Platform
  1. Windows
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]
 

Jazzmine16

New Member
Joined
Mar 10, 2016
Messages
3
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)


IF%20STATEMENT_zpsziupsr2z.jpg
 

Jazzmine16

New Member
Joined
Mar 10, 2016
Messages
3

ADVERTISEMENT

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)


IF%20STATEMENT_zpsziupsr2z.jpg


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
Joined
Mar 10, 2016
Messages
3
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)


IF%20STATEMENT_zpsziupsr2z.jpg


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

formula_zpsihl9ziod.jpg


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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Correction to FIRST formula above:

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

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top