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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,950
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
5,422
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:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Move in Month</td><td style=";">Rent</td><td style=";">Fees</td><td style=";">Current Fees</td><td style=";">1st %</td><td style=";">2nd %</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">2013/10</td><td style="text-align: right;;">1450</td><td style="text-align: right;;">116.15</td><td style="text-align: right;;">116.15</td><td style="text-align: right;;">78%</td><td style="text-align: right;;">7.20%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">2015/05</td><td style="text-align: right;;">1225</td><td style="text-align: right;;">84.76</td><td style="text-align: right;;">84.76</td><td style="text-align: right;;">78%</td><td style="text-align: right;;">7.20%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">2016/01</td><td style="text-align: right;;">1895</td><td style="text-align: right;;">106.42</td><td style="text-align: right;;">91.04</td><td style="text-align: right;;">78%</td><td style="text-align: right;;">7.20%</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1250</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">0</td><td style="text-align: right;;">78%</td><td style="text-align: right;;">7.20%</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">If this column is formatted as General or Text</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">A2="",0,IF(<font color="Red">LEFT(<font color="Green">A2,4</font>)+0<2016,D2,B2*E2*F2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Move in Month</td><td style=";">Rent</td><td style=";">Fees</td><td style=";">Current Fees</td><td style=";">1st %</td><td style=";">2nd %</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">2013/10</td><td style="text-align: right;;">1450</td><td style="text-align: right;;">116.15</td><td style="text-align: right;;">116.15</td><td style="text-align: right;;">78%</td><td style="text-align: right;;">7.20%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2015/05</td><td style="text-align: right;;">1225</td><td style="text-align: right;;">84.76</td><td style="text-align: right;;">84.76</td><td style="text-align: right;;">78%</td><td style="text-align: right;;">7.20%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2016/01</td><td style="text-align: right;;">1895</td><td style="text-align: right;;">106.42</td><td style="text-align: right;;">91.04</td><td style="text-align: right;;">78%</td><td style="text-align: right;;">7.20%</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">1250</td><td style="text-align: right;;">0.00</td><td style="text-align: right;;">0</td><td style="text-align: right;;">78%</td><td style="text-align: right;;">7.20%</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Make sure this column is formatted Custom: yyyy/mm</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Instead of 0, leave blank, otherwise will show: 1900/01</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">A2="",0,IF(<font color="Red">YEAR(<font color="Green">A2</font>)<2016,D2,B2*E2*F2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Either way, C2 formula copied down.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,422
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,957
Messages
5,599,046
Members
414,281
Latest member
Engjamal2021

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