#Value! error when adding cells and - and is neg number

[CrashOD]

Automated 2022 Tax Certification Form.xlsm
	A	B	C	D	E
26	SCHOOL 2022		July/Aug	Sept/Oct	Nov/Dec
27	Bill #:	01700102	Discount (-2%)	Face:	Penalty (+10%)
28	122	#VALUE!	$1,170.89	$1,194.79	$1,314.27
29	Date Paid:	#VALUE!	HomeStead Amount	HomeStead	Assessed Value
30			$100.60	NO	31,450
31		PARTIALS	DATE	#VALUE!	<- SHORT IN FACE
32		PAYMENT #1	7/18/2022 #1	$398.27	FACE
33		PAYMENT #2	09/08/2022	$398.26	FACE
34		PAYMENT #3
Tax Cert Form 2022-2021

Cell Formulas
Range	Formula
A28	A28	=INDEX('[Rle_School 2022.xls]Real Estate'!$E:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1)
C28	C28	=INDEX('[Rle_School 2022.xls]Real Estate'!$U:$AL,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),11)
D28	D28	=INDEX('[Rle_School 2022.xls]Real Estate'!$U:$AL,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),12)
E28	E28	=INDEX('[Rle_School 2022.xls]Real Estate'!$U:$AL,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),13)
B27	B27	=INDEX('[Rle_School 2022.xls]Real Estate'!$B:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1)
B28	B28	=IF(B29="SHORT","SHORT",IF(B29="","UNPAID","PAID"))
B29	B29	=IF(D28<=SUM(D32,D33,D34),C34,IF(D28>D31,"SHORT",IF(INDEX('[Rle_School 2022.xls]Real Estate'!$C:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$C:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1))))
E30	E30	=INDEX('[Rle_School 2022.xls]Real Estate'!$T$2:$U$7008,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U$2:$U$7008,0),1)
D30	D30	=INDEX('[Rle_School 2022.xls]Real Estate'!$U:$AI,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),15)
D31	D31	=IF(SUM(D32:D34)=0,"",IF((D32:D34)<D28,SUM(D32:D34)-(D28)))
C32	C32	=IF(INDEX('[Rle_School 2022.xls]Real Estate'!$H:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$H:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1))
D32	D32	=IF(INDEX('[Rle_School 2022.xls]Real Estate'!$I:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$I:$U,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$U:$U,0),1))
E32	E32	=IF(C32="","", "FACE")
C33	C33	=IF(INDEX('[Rle_School 2022.xls]Real Estate'!$J:$AN,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AN:$AN,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$J:$AN,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AN:$AN,0),1))
D33	D33	=IF(INDEX('[Rle_School 2022.xls]Real Estate'!$K:$AN,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AN:$AN,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$K:$AN,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AN:$AN,0),1))
E33:E34	E33	=IF(C33="","",IF($D$52<=D33,"FACE",IF($D$52>D33,"PENALTY")))
C34	C34	=IF(INDEX('[Rle_School 2022.xls]Real Estate'!$L:$AO,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AO:$AO,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$L:$AO,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AO:$AO,0),1))
D34	D34	=IF(INDEX('[Rle_School 2022.xls]Real Estate'!$M:$AO,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AO:$AO,0),1)="","",INDEX('[Rle_School 2022.xls]Real Estate'!$M:$AO,MATCH($C$10,'[Rle_School 2022.xls]Real Estate'!$AO:$AO,0),1))

Should show 398.26

SCHOOL 2022	July/Aug	Sept/Oct	Nov/Dec
Bill #:	01700102	Discount (-2%)	Face:	Penalty (+10%)
122	#VALUE!	$1,170.89	$1,194.79	$1,314.27
Date Paid:	#VALUE!	HomeStead Amount	HomeStead	Assessed Value
		$100.60	NO	31,450
	PARTIALS	DATE	#VALUE!	<- SHORT IN FACE
	PAYMENT #1	7/18/2022 #1	$398.27	FACE
	PAYMENT #2	09/08/2022	$398.26	FACE
	PAYMENT #3

 

[steve the fish]

They usually occur when you try to sum etc things that arent numbers. For those values that show as dollar values go to the underlying data and see if they have indeed a dollar in the cell or if its just formatting. You can tell by clicking in a cell and looking in the formula bar. If dollar shows then thats your problem.

 

[CrashOD]

They usually occur when you try to sum etc things that arent numbers. For those values that show as dollar values go to the underlying data and see if they have indeed a dollar in the cell or if its just formatting. You can tell by clicking in a cell and looking in the formula bar. If dollar shows then thats your problem.

on the example it is just formated. there is not $ typed in just numbers