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

 

[Fluff]

Yes it will

+Fluff New.xlsm
	A	B
1
2	43.1
3	43.2	0.89999999999999900000000
Main

Cell Formulas
Range	Formula
B3	B3	=A2-A3+1

 

[guscy]

Thanks for reply.
I read it. What about **Precision as displayed **option. Will it help?

 

[gaz_chops]

Ok i have never ever come across this with simple calcs like that!

If I enter the same i get .9 as the result, is this down to a setting or version issue?

 

[gaz_chops]

Mine is not set to "Precision as displayed" and I don't get the issue.

 

[Joe4]

I read it. What about **Precision as displayed **option. Will it help?

Yes, that is one option. But bear in mind that could have possible have negative consequences though.
For example, let's say you are showing two decimals, and you have three fractions of 10/3 in separate cells that are being added up. They will now add up to 9.99, instead of 10.

 

[guscy]

It is complicated. If at the end results will be correct maybe is better to keep it as it is.

 

[Fluff]

If I enter the same i get .9 as the result, is this down to a setting or version issue?

Increase the number of decimals displayed to 14