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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
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)


 

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.

<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
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)



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
Joined
Jun 29, 2014
Messages
5,146
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,146
Hi,

Correction to FIRST formula above:

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

Forum statistics

Threads
1,085,538
Messages
5,384,307
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top