Sorry it's a long one, but it's really simple math that isn't returning the
right answer.
In "dealer worksheet" workbook to find amount due for July and August:
Cell R4 is
=SUMIF(est_comp_date,"=07/01/2008",amt_due)-SUMIF(est_comp_date,"07/31/2008",amt_due) answers correctly "$ - " (there are no entries)
Cell R5 is
=SUMIF(est_comp_date,"=08/01/2008",amt_due)-SUMIF(est_comp_date,"08/31/2008",amt_due) answers correctly "$ 77.70" (there's only one entry of 77.7)
In same worksheet to find amoung paid for July and August:
Cell V4 is
=SUMIF(date_pd_sams,"=07/01/2008",amt_pd)-SUMIF(date_pd_sams,"07/31/2008",amt_pd) answers correctly "$ -" (there are no entries)
Cell V5 is
=SUMIF(date_pd_sams,"=08/01/2008",amt_pd)-SUMIF(date_pd_sams,"08/31/2008",amt_pd) answers correctly "$ 77.70" (there's only one entry of 77.7)
New workbook copies these values:
Cell AB17 =SUM('[dealer worksheet.xls]America''s #1'!$R$4:$R$5) which adds
the amounts due above of "$ -" and "$77.70" correctly returning "$77.70"
Cell AL17 =SUM('[dealer worksheet.xls]America''s #1'!$V$4:$V$5) which
adds the amounts paid above of "$ -" and "$77.70" correctly returning
"$77.70"
PROBLEM: Now I have in cell AM17 of this same sheet of the 2nd workbook:
=SUM(AB17-AL17) and my answer is 1.4210854715202E-14
displayed as "0.000000000000014210854715"... or "$ 0.00"
It's simple math of $77.70-$77.70 = 0 and should be displayed as "$ -"
I want it to display "$ -" as it does for 11 identical entries in the
same worksheet.

Thanks for your help and patience reading this.

Excel sometimes adds insignificant digits to your numbers. To fix the
problem, just use the round function to round your answers to 2
decimal places and you'll get the 0 you are looking for.

On Jul 18, 9:45*am, KMC wrote:
Sorry it's a long one, but it's really simple math that isn't returning the
right answer.
In "dealer worksheet" workbook to find amount due for July and August:
Cell R4 is * *
=SUMIF(est_comp_date,"=07/01/2008",amt_due)-SUMIF(est_comp_date,"07/31/2008",amt_due) answers correctly "$ * *- *" (there are no entries)
Cell R5 is * *
=SUMIF(est_comp_date,"=08/01/2008",amt_due)-SUMIF(est_comp_date,"08/31/2008",amt_due) answers correctly "$ 77.70" (there's only one entry of 77.7)
In same worksheet to find amoung paid for July and August:
Cell V4 is * *
=SUMIF(date_pd_sams,"=07/01/2008",amt_pd)-SUMIF(date_pd_sams,"07/31/2008",amt_pd) answers correctly "$ * -" (there are no entries)
Cell V5 is * *
=SUMIF(date_pd_sams,"=08/01/2008",amt_pd)-SUMIF(date_pd_sams,"08/31/2008",amt_pd) answers correctly "$ 77.70" (there's only one entry of 77.7)
New workbook copies these values:
Cell AB17 * =SUM('[dealer worksheet.xls]America''s #1'!$R$4:$R$5) which adds
the amounts due above of "$ * -" and "$77.70" correctly returning "$77.70"
Cell AL17 * *=SUM('[dealer worksheet.xls]America''s #1'!$V$4:$V$5) which
adds the amounts paid above of "$ * -" and "$77.70" correctly returning
"$77.70"
PROBLEM: Now I have in cell AM17 of this same sheet of the 2nd workbook:
=SUM(AB17-AL17) and my answer is 1.4210854715202E-14
displayed as "0.000000000000014210854715"... or "$ *0.00"
It's simple math of $77.70-$77.70 = 0 and should be displayed as "$ * *-"
I want it to display "$ * *-" as it does for 11 identical entries in the
same worksheet.

Thanks for your help and patience reading this.

Can I round as a format (rather than function) for the entire column for
consistency so I'm not constantly looking for errors?

"Tim879" wrote:

Excel sometimes adds insignificant digits to your numbers. To fix the
problem, just use the round function to round your answers to 2
decimal places and you'll get the 0 you are looking for.

On Jul 18, 9:45 am, KMC wrote:
Sorry it's a long one, but it's really simple math that isn't returning the
right answer.
In "dealer worksheet" workbook to find amount due for July and August:
Cell R4 is
=SUMIF(est_comp_date,"=07/01/2008",amt_due)-SUMIF(est_comp_date,"07/31/2008",amt_due) answers correctly "$ - " (there are no entries)
Cell R5 is
=SUMIF(est_comp_date,"=08/01/2008",amt_due)-SUMIF(est_comp_date,"08/31/2008",amt_due) answers correctly "$ 77.70" (there's only one entry of 77.7)
In same worksheet to find amoung paid for July and August:
Cell V4 is
=SUMIF(date_pd_sams,"=07/01/2008",amt_pd)-SUMIF(date_pd_sams,"07/31/2008",amt_pd) answers correctly "$ -" (there are no entries)
Cell V5 is
=SUMIF(date_pd_sams,"=08/01/2008",amt_pd)-SUMIF(date_pd_sams,"08/31/2008",amt_pd) answers correctly "$ 77.70" (there's only one entry of 77.7)
New workbook copies these values:
Cell AB17 =SUM('[dealer worksheet.xls]America''s #1'!$R$4:$R$5) which adds
the amounts due above of "$ -" and "$77.70" correctly returning "$77.70"
Cell AL17 =SUM('[dealer worksheet.xls]America''s #1'!$V$4:$V$5) which
adds the amounts paid above of "$ -" and "$77.70" correctly returning
"$77.70"
PROBLEM: Now I have in cell AM17 of this same sheet of the 2nd workbook:
=SUM(AB17-AL17) and my answer is 1.4210854715202E-14
displayed as "0.000000000000014210854715"... or "$ 0.00"
It's simple math of $77.70-$77.70 = 0 and should be displayed as "$ -"
I want it to display "$ -" as it does for 11 identical entries in the
same worksheet.

Thanks for your help and patience reading this.