IF FORMULA ERROR

[guscy]

HI,

Any idea why i get in last row result PREPAID while balance is zero (0). Since balance is zero if formula should return blank.

Thannks in advance.


Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​
1​	=TODAY()​	​
2​	house 1​	​	​	​	​
3​	DATE​	DESCRIPTION​	DUE​	PAID​	BALANCE​
4​	1/1/2020​	2019​	​	​	-443,20​		=IF(A4="","",IF(A4<=$A$1,ROW()-4,""))​
5​	1/1/2020​	JAN​	-38,68​	​	=SUM((E4+C5)+D5)​		=IF(A5="","",IF(A5<=$A$1,ROW()-4,""))​
6​	1/2/2020​	FEB​	-38,68​	​	=SUM((E5+C6)+D6)​		=IF(A6="","",IF(A6<=$A$1,ROW()-4,""))​
7​	1/3/2020​	MAR​	-38,68​	​	=SUM((E6+C7)+D7)​		=IF(A7="","",IF(A7<=$A$1,ROW()-4,""))​
8​	1/4/2020​	APR​	-38,68​	​	=SUM((E7+C8)+D8)​		=IF(A8="","",IF(A8<=$A$1,ROW()-4,""))​
9​	1/5/2020​	MAY​	-38,68​	​	=SUM((E8+C9)+D9)​		=IF(A9="","",IF(A9<=$A$1,ROW()-4,""))​
10​	1/6/2020​	JUN​	-38,68​	​	=SUM((E9+C10)+D10)​		=IF(A10="","",IF(A10<=$A$1,ROW()-4,""))​
11​	2/6/2020​	ADITIONAL​	-1214,91​	1890,19​	=SUM((E10+C11)+D11)​		=IF(A11="","",IF(A11<=$A$1,ROW()-4,""))​
12​	1/7/2020​	JUL​	-38,68​	​	=SUM((E11+C12)+D12)​		=IF(A12="","",IF(A12<=$A$1,ROW()-4,""))
13​	1/8/2020​	AUG​	-38,68​	​	=SUM((E12+C13)+D13)​		=IF(A13="","",IF(A13<=$A$1,ROW()-4,""))
14​	1/9/2020​	SEP​	-38,68​	​	=SUM((E13+C14)+D14)​		=IF(A14="","",IF(A14<=$A$1,ROW()-4,""))
15​	1/10/2020​	OCT​	-38,68​	​	=SUM((E14+C15)+D15)​		=IF(A15="","",IF(A15<=$A$1,ROW()-4,""))
16​	1/11/2020​	NOV​	-38,68​	​	=SUM((E15+C16)+D16)​		=IF(A16="","",IF(A16<=$A$1,ROW()-4,""))
17​	1/12/2020​	DEC​	-38,68​	​	=SUM((E16+C17)+D17)​		=IF(A17="","",IF(A17<=$A$1,ROW()-4,""))
18​	​	​	​	​	=SUM((E17+C18)+D18)​		=IF(A18="","",IF(A18<=$A$1,ROW()-4,""))
19​	​	​	​	​	=SUM((E18+C19)+D19)​		=IF(A19="","",IF(A19<=$A$1,ROW()-4,""))
20​	​	​	​	​	=SUM((E19+C20)+D20)​		=IF(A20="","",IF(A20<=$A$1,ROW()-4,""))
21​	​	​	​	​	=SUM((E20+C21)+D21)​		=IF(A21="","",IF(A21<=$A$1,ROW()-4,""))
22​	​	​	​	​	=SUM((E21+C22)+D22)​		=IF(A22="","",IF(A22<=$A$1,ROW()-4,""))
23​			​	​
24​	TOTAL:	=IF(E24<0,"AMOUNT DUE",IF(E24=0,"",IF(E24>0,"PREPAID")))​	​	​	=OFFSET($G$4,MATCH(LARGE($G$5:$G$22,1),$G$5:$G$22,0),-2)​

Sheet: HOUSE 1

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​
1​	11/6/2020​	​
2​	house 1​	​	​	​	​
3​	DATE​	DESCRIPTION​	DUE​	PAID​	BALANCE​
4​	1/1/2020​	2019​	​	​	-443,20​		0​
5​	1/1/2020​	JAN​	-38,68​	​	-481,88​		1​
6​	1/2/2020​	FEB​	-38,68​	​	-520,56​		2​
7​	1/3/2020​	MAR​	-38,68​	​	-559,24​		3​
8​	1/4/2020​	APR​	-38,68​	​	-597,92​		4​
9​	1/5/2020​	MAY​	-38,68​	​	-636,60​		5​
10​	1/6/2020​	JUN​	-38,68​	​	-675,28​		6​
11​	2/6/2020​	ADITIONAL​	-1214,91​	1890,19​	0,00​		7​
12​	1/7/2020​	JUL​	-38,68​	​	-38,68​
13​	1/8/2020​	AUG​	-38,68​	​	-77,36​
14​	1/9/2020​	SEP​	-38,68​	​	-116,04​
15​	1/10/2020​	OCT​	-38,68​	​	-154,72​
16​	1/11/2020​	NOV​	-38,68​	​	-193,40​
17​	1/12/2020​	DEC​	-38,68​	​	-232,08​
18​	​	​	​	​	-232,08​
19​	​	​	​	​	-232,08​
20​	​	​	​	​	-232,08​
21​	​	​	​	​	-232,08​
22​	​	​	​	​	-232,08​
23​			​	​
24​	TOTAL:	PREPAID​	​	​	0,00​

Sheet: HOUSE 1

 

[gaz_chops]

Are you sure it is zero?

Try =E24=0

And you don't need the last IF

=IF(E24<0,"AMOUNT DUE",IF(E24=0,"","PREPAID"))

 

[guscy]

Thanks for your reply.
Yes E24 is zero (0,00). Is the result of formula =SUM((E10+C11)+D11) and then using formula =OFFSET($G$4,MATCH(LARGE($G$5:$G$22,1),$G$5:$G$22,0),-2) I get zero in E24.

I try your suggestion but id didnt work.

 

[gaz_chops]

What if you try

=IF(E24<0,"AMOUNT DUE",IF(E24>0,"PREPAID",""))

 

[guscy]

still same issue.
issue starts from cell E11 where formula =SUM((E10+C11)+D11) returns zero (0).
If I type a number in E11 all works fine. But when E24 shows the result of the formula in E11 then I have the issue.

No idea why...

 

[gaz_chops]

Did you do the test i suggested? =E24=0

 

[guscy]

I tried your formula =IF(E24<0,"AMOUNT DUE",IF(E24=0,"","PREPAID")) but didnt work.
Did you mean something else?

i tried a simple one formula =IF(E24=0;"YES";"NO") and it returns NO.
it doesnt regognise zero which is taken from E11 where formula =SUM((E10+C11)+D11) returns zero (0)

 

[guscy]

I found out that E24 isnt excactly 0,00 but after increasing decimal results is 0,00000000000000227373675443232 so that what is going wrong.
Any idea how to avoid this. I want to keep only two demical.

Shall I post a new thraed for that now?

 

[gaz_chops]

That is why i was asking you to try =E24=0 to see if it was really zero.

Try
=IF(E24<0,"AMOUNT DUE",IF(round(E24,2)=0,"","PREPAID"))

 

[gaz_chops]

Or may be better in this one

=round(SUM((E10+C11)+D11),2)