go.for.alyssa
New member
- Joined
- Feb 23, 2022
- Messages
- 7
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Business 365 (V2201)
Hello all! I'm having a very bizarre issue and I know my conclusion can't be right but there doesn't seem to be any explanation besides excel evaluating the formula and getting the wrong answer! Unless this is some crazy type of currency formatting I'm not understanding correctly. The situation is this:
I've got a formula that shows the status for invoices by looking at the total due for the invoices in that table and comparing it against a helper cell with the current balance, which looks at a different table in which payments are stored and runs a basic sumifs function on payments with the same invoice number.
I noticed the status for an invoice in the table shows as "partial"ly paid although the balance remaining helper cell shows a $0.00 balance remaining. The invoice is in fact fully paid. So naturally I assumed an error in my status formula. When evaluating the formula it shows the helper cell balance remaining evaluating as: "5.68434E-14"
Naturally the next thing I investigated was how that helper cell evaluates. I've been through it a hundred times and can't understand what it happening. It does some lookup info within the table and determines the total cost of the invoice is $475.48, then some more lookup in the payment table and determines the total amount of payments equal $475.48. It clearly shows the next step: $475.48 - $475.48.
Clearly this has to be zero, but when I hit next step to evaluate the formula it shows: "5.68434E-14" Note that there is no multiplication of percentages or erroneous extra decimal places compromising my math. All currency values are only two decimal places.
When I exit the formula the cell shows it's evaluating to $0.00. If I remove the currency formatting and go to general formatting, for a moment it shows the "5.6843E-14" but then due to table formatting behavior it pretty quickly reverts to currency as the rest of the row is formatted that way.
Both the Status formula and the Balance Remaining formula are performing PERFECTLY in all the table cells above and below this one. There are no other errors in the evaluation. I've got WAY more complicated stuff in this spreadsheet not giving me any trouble, but this issue has me stumped.
I know it's got to be user error somehow, but I can't figure out what I'm doing wrong. What on earth is happening in this one cell???
For reference, here are the two formulas I'm using (quite lengthy due to table nomenclature):
Status:
=IF([@[Balance Owing]]=0,$A$4,IF([@[Balance Owing]]<0,
IF(ISNUMBER(SEARCH("CR",[@[Invoice '#]])),$A$9,$A$8),
IF([@[Balance Owing]]<[@[CAD TOTAL]]+[@[USD TOTAL]], $A$5,IF(TODAY()<=[@[Invoice Sent]]+[@[Due In Days]],$A$6,$A$7))))
A4: Paid in Full
A5: Partial
A6: Current
A7: Past Due
A8: Overpaid
A9: Credit
The helper cell within this table for balance owing references a different table: invoicepayments.
Balance Owing:
=[@[USD TOTAL]]+[@[CAD TOTAL]]-SUMIFS(invoicepayments[USD PAYMENT $],invoicepayments[Invoice '#],[@[Invoice '#]])-SUMIFS(invoicepayments[CAD PAYMENT $],invoicepayments[Invoice '#],[@[Invoice '#]])
I've got a formula that shows the status for invoices by looking at the total due for the invoices in that table and comparing it against a helper cell with the current balance, which looks at a different table in which payments are stored and runs a basic sumifs function on payments with the same invoice number.
I noticed the status for an invoice in the table shows as "partial"ly paid although the balance remaining helper cell shows a $0.00 balance remaining. The invoice is in fact fully paid. So naturally I assumed an error in my status formula. When evaluating the formula it shows the helper cell balance remaining evaluating as: "5.68434E-14"
Naturally the next thing I investigated was how that helper cell evaluates. I've been through it a hundred times and can't understand what it happening. It does some lookup info within the table and determines the total cost of the invoice is $475.48, then some more lookup in the payment table and determines the total amount of payments equal $475.48. It clearly shows the next step: $475.48 - $475.48.
Clearly this has to be zero, but when I hit next step to evaluate the formula it shows: "5.68434E-14" Note that there is no multiplication of percentages or erroneous extra decimal places compromising my math. All currency values are only two decimal places.
When I exit the formula the cell shows it's evaluating to $0.00. If I remove the currency formatting and go to general formatting, for a moment it shows the "5.6843E-14" but then due to table formatting behavior it pretty quickly reverts to currency as the rest of the row is formatted that way.
Both the Status formula and the Balance Remaining formula are performing PERFECTLY in all the table cells above and below this one. There are no other errors in the evaluation. I've got WAY more complicated stuff in this spreadsheet not giving me any trouble, but this issue has me stumped.
I know it's got to be user error somehow, but I can't figure out what I'm doing wrong. What on earth is happening in this one cell???
For reference, here are the two formulas I'm using (quite lengthy due to table nomenclature):
Status:
=IF([@[Balance Owing]]=0,$A$4,IF([@[Balance Owing]]<0,
IF(ISNUMBER(SEARCH("CR",[@[Invoice '#]])),$A$9,$A$8),
IF([@[Balance Owing]]<[@[CAD TOTAL]]+[@[USD TOTAL]], $A$5,IF(TODAY()<=[@[Invoice Sent]]+[@[Due In Days]],$A$6,$A$7))))
A4: Paid in Full
A5: Partial
A6: Current
A7: Past Due
A8: Overpaid
A9: Credit
The helper cell within this table for balance owing references a different table: invoicepayments.
Balance Owing:
=[@[USD TOTAL]]+[@[CAD TOTAL]]-SUMIFS(invoicepayments[USD PAYMENT $],invoicepayments[Invoice '#],[@[Invoice '#]])-SUMIFS(invoicepayments[CAD PAYMENT $],invoicepayments[Invoice '#],[@[Invoice '#]])
