1

I have a calculated column in a Sharepoint List that is simply adding up 5 other columns with the following formula:

=RWDPeriod1+RWDPeriod2+RWDPeriod3+RWDPeriod4+RWDPeriod5

The problem is that this calculation is working for all rows (list items) except for one, which returns a value of -428738486.

I have gone so far as to replace this formula with "=0" and still I get the same result in the same row. All other rows (300+) come back with the correct/desired result.

Does anyone have a clue what would cause this error?

  • What is the column type of all these fields? Does all this fields are having values for that particular item? – Ganesh Sanap Nov 28 '18 at 15:19
  • Each of the RWDPeriod columns are calculated columns. Each contain a formula: "=IF(AND(RWD1Return-RWD1Start=0,ISBLANK(RWD1Start)),"0",IF(RWD1Return-RWD1Start<0,Today-RWD1Start+1,IF(RWD1Return-RWD1Start=0,1,RWD1Return-RWD1Start)))" respective of their periods. – JSparrow1776 Nov 28 '18 at 15:35
  • I have more than 300 list items, but for some reason, only one item throughout the entire list results in the negative value -428738486. All the other values are correct. – JSparrow1776 Nov 28 '18 at 15:36
  • For those who might have a similar issue, I was able to correct this error. The formula is summing up RWDPeriods 1-5, each of these Columns (i.e., RWDPeriod1, RWDPeriod2, etc.) is using a DATEIF function to calculate between dates. I simply wrote over the dates in two columns (RWD5Start and RWD5Return) and the issue cleared itself providing me with the correct value for the formula provided initially above. – JSparrow1776 Nov 28 '18 at 16:44
  • Please answer your own question and mark it as answer. Dont leave it unanswered. – harshal gite Nov 28 '18 at 18:14
0

To answer the question posted, I can only provide some details that led to it clearing on its own. It's important to understand that out of 400+ item rows, only 1 contained an error, line #246.

The problem was a calculated column named "RWDTotalActual" was returning a value of -428738486 while summing five similar calculated columns "RWDPeriod1, RWDPeriod2...RWDPeriod5, using the following formula:

=RWDPeriod1+RWDPeriod2+RWDPeriod3+RWDPeriod4+RWDPeriod5

Each of the RWDPeriod_ columns contain a formula to arrive at the number of days between an RWD1Start and RWD1Return column (respective of each period 1-5). The formula in these columns is:

"=IF(AND(RWD1Return-RWD1Start=0,ISBLANK(RWD1Start)),"0",IF(RWD1Return-RWD1Start<0,Today-RWD1Start+1,IF(RWD1Return-RWD1Start=0,1,RWD1Return-RWD1Start)))

Working backwards, I started with the RWD5Start/RWD5Return columns and reentered their respective dates recorded. Completing this action returned the same value to the RWDPeriod5 column, which in return, cleared the incorrect value "-428738486" being shown in the RWDTotalActual column.

I'm not sure why this worked, considering the data was exactly the same. Note also that there were more than 400 line items in this list, and only one item (#246) had this issue. All other rows (list items) above and below were calculated correctly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.