Multiple Logical Tests

[NerdyRN]

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]

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:

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

 

[NerdyRN]

that did it! thanks!

 

[Jazzmine16]

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]

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]

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]

Hi,

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

Excel 2010
	A	B	C	D	E	F
1	Move in Month	Rent	Fees	Current Fees	1st %	2nd %
2	2013/10	1450	116.15	116.15	78%	7.20%
3	2015/05	1225	84.76	84.76	78%	7.20%
4	2016/01	1895	106.42	91.04	78%	7.20%
5	0	1250	0.00	0	78%	7.20%
6
7	If this column is formatted as General or Text
Sheet2

Cell Formulas
Range	Formula
C2	=IF(A2="",0,IF(LEFT(A2,4)+0<2016,D2,B2*E2*F2))

Excel 2010
	A	B	C	D	E	F
1	Move in Month	Rent	Fees	Current Fees	1st %	2nd %
2	2013/10	1450	116.15	116.15	78%	7.20%
3	2015/05	1225	84.76	84.76	78%	7.20%
4	2016/01	1895	106.42	91.04	78%	7.20%
5		1250	0.00	0	78%	7.20%
6
7	Make sure this column is formatted Custom: yyyy/mm
8	Instead of 0, leave blank, otherwise will show: 1900/01
Sheet1

Cell Formulas
Range	Formula
C2	=IF(A2="",0,IF(YEAR(A2)<2016,D2,B2*E2*F2))

Either way, C2 formula copied down.

 

[jtakw]

Hi,

Correction to FIRST formula above:

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

 

[Joe4]

Regarding the issue you had with posting your formulas, see this here: http://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html