Formula results inconsistent.

[CyberTootie]

I have created a multiple-worksheet workbook designed to be a monthy credit
card transaction log. In my summary, I calculate the difference of the total
amount of entries inputted into the log and a field to put in the total from
the machine. This field, which uses an IF statement, displays "N/A" if a
machine value hasn't been inserted, amount of difference, or "None" if the
difference is zero.

The only problem is, even though the difference should be zero, sometimes
the difference isn't zero. Even though the formulas are straightforward and
shouldn't be producing insignificant fractions of a penny, they are.

Here's the formula flow:

Cell 1: [=SUMIF(F5:F34,">0")] - Adds up credit card charges. (>0 is there
because sheet also handles credit card credits)

Cell 2: [No formula] - User enters total amount from machine

Cell 3: [=IF(Cell 2="","N/A",IF(Cell 1-Cell 2=0,"None",Cell 1-Cell 2))]

What I'm wondering is how Cell 3's formula could be producing something like
$0.000000000000227373675443232000.

 

[Ron Rosenfeld]

I have created a multiple-worksheet workbook designed to be a monthy credit
card transaction log. In my summary, I calculate the difference of the total
amount of entries inputted into the log and a field to put in the total from
the machine. This field, which uses an IF statement, displays "N/A" if a
machine value hasn't been inserted, amount of difference, or "None" if the
difference is zero.

The only problem is, even though the difference should be zero, sometimes
the difference isn't zero. Even though the formulas are straightforward and
shouldn't be producing insignificant fractions of a penny, they are.

Here's the formula flow:

Cell 1: [=SUMIF(F5:F34,">0")] - Adds up credit card charges. (>0 is there
because sheet also handles credit card credits)

Cell 2: [No formula] - User enters total amount from machine

Cell 3: [=IF(Cell 2="","N/A",IF(Cell 1-Cell 2=0,"None",Cell 1-Cell 2))]

What I'm wondering is how Cell 3's formula could be producing something like
$0.000000000000227373675443232000.

See:

http://support.microsoft.com/kb/214118/en-us
--ron

 

[Don Guillett]

You need to incorporate some rounding. Look in the help index for ROUND

 

[CyberTootie]

Thanks!

I have created a multiple-worksheet workbook designed to be a monthy credit
card transaction log. In my summary, I calculate the difference of the total
amount of entries inputted into the log and a field to put in the total from
the machine. This field, which uses an IF statement, displays "N/A" if a
machine value hasn't been inserted, amount of difference, or "None" if the
difference is zero.

The only problem is, even though the difference should be zero, sometimes
the difference isn't zero. Even though the formulas are straightforward and
shouldn't be producing insignificant fractions of a penny, they are.

Here's the formula flow:

Cell 1: [=SUMIF(F5:F34,">0")] - Adds up credit card charges. (>0 is there
because sheet also handles credit card credits)

Cell 2: [No formula] - User enters total amount from machine

Cell 3: [=IF(Cell 2="","N/A",IF(Cell 1-Cell 2=0,"None",Cell 1-Cell 2))]

What I'm wondering is how Cell 3's formula could be producing something like
$0.000000000000227373675443232000.

See:

http://support.microsoft.com/kb/214118/en-us
--ron