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]

Sorry I meant this one

=round(SUM((E4+C5)+D5),2)

Then copy down.

 

[guscy]

Really thanks for your reply!
Both are working! I prefer second one.

Thats great issue solved!

Thanks!

 

[gaz_chops]

You're welcome, glad we sorted it in the end.

 

[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?

Here again...
Issue is fixed. But...
Why in E11 result isnt excactly zero?
If D11 is blank in balance column shoes -1890,19. Typing this in D11 result in column E11 should be exactly zero.
Any idea why is not?

 

[gaz_chops]

Where are these values from? Are they entered manually or the result of another formula in those cells?

-1214,91

1890,19

 

[guscy]

-1214,91 is entered manually. -1890,19 is result of a formula

 

[gaz_chops]

And the Opening Balance figure -443,20, is that entered manually?

 

[Fluff]

It's down to floating point precision, have a look here

Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Discusses that floating-point arithmetic may give inaccurate results in Excel.

docs.microsoft.com

 

[guscy]

And the Opening Balance figure -443,20, is that entered manually?

Yes

 

[gaz_chops]

It's down to floating point precision, have a look here

Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Discusses that floating-point arithmetic may give inaccurate results in Excel.

docs.microsoft.com

Will that apply to something this basic, as in simple manually entered 2 decimal numbers, i thought it was when using formula and beyond 2 decimal places.