We have a company page to track mileage on company vehicles, and at the end of every month the driver enters the ending odometer reading. I wrote a formula to figure out the mileage for the year by taking each month and subtracting the previous month, and then adding each of those results. It worked, and looks like this:

=([Jan 13]-[2012 Ending])+([Feb 13]-[Jan 13])+([Mar 13]-[Feb 13])+([Apr 13]-[Mar 13])+([May 13]-[Apr 13])+([Jun 13]-[May 13])+([Jul 13]-[Jun 13])+([Aug 13]-[Jul 13])+([Sep 13]-[Aug 13])+([Oct 13]-[Sep 13])+([Nov 13]-[Oct 13])+([Dec 13]-[Nov 13])

Problem is, until you have every month filled in, you get negative numbers and an incorrect result...Because "0" minus last month's mileage is a negative. So I wrote an IF statement into each month that evaluates whether or not the current month is still zero, and if so returns zero...which should fix the math problem...

=IF([Jan 13]>0, [Jan 13]-[2012 Ending],"0")

=IF([Feb 13]>0, [Feb 13]-[Jan 13],"0")

=IF([Mar 13]>0, [Mar 13]-[Feb 13],"0")

=IF([Apr 13]>0, [Apr 13]-[Mar 13],"0")

=IF([May 13]>0, [May 13]-[Apr 13],"0")

=IF([Jun 13]>0, [Jun 13]-[May 13],"0")

=IF([Jul 13]>0, [Jul 13]-[Jun 13],"0")

=IF([Aug 13]>0, [Aug 13]-[Jul 13],"0")

=IF([Sep 13]>0, [Sep 13]-[Aug 13],"0")

=IF([Oct 13]>0, [Oct 13]-[Sep 13],"0")

=IF([Nov 13]>0, [Nov 13]-[Oct 13],"0")

=IF([Dec 13]>0, [Dec 13]-[Nov 13],"0")

The problem now is to string them all together...And that's where I get the error. Putting it all together should look something like...?

=SUM(=IF([Jan 13]>0, [Jan 13]-[2012 Ending],"0")+(=IF([Feb 13]>0, [Feb 13]-[Jan 13],"0")+(=IF([Mar 13]>0, [Mar 13]-[Feb 13],"0")+(=IF([Apr 13]>0, [Apr 13]-[Mar 13],"0")+(=IF([May 13]>0, [May 13]-[Apr 13],"0")+(=IF([Jun 13]>0, [Jun 13]-[May 13],"0")+(=IF([Jul 13]>0, [Jul 13]-[Jun 13],"0")+(=IF([Aug 13]>0, [Aug 13]-[Jul 13],"0")+(=IF([Sep 13]>0, [Sep 13]-[Aug 13],"0")+(=IF([Oct 13]>0, [Oct 13]-[Sep 13],"0")+(=IF([Nov 13]>0, [Nov 13]-[Oct 13],"0")+(=IF([Dec 13]>0, [Dec 13]-[Nov 13],"0"))

But my syntax is wrong somewhere...Help!

  • Mike, easiest is to make the formula in Excel... Because SharePoint calculated column uses Excel formulas! So that will be easy for you to handle the formula and once you get the result in Excel just put to SharePoint... And volla it will work :) Nov 28 '12 at 16:49
  • That would be easier, but I will have multiple users at multiple sites using this sharepoint list. Too much "oh crap" factor in allowing them access to an excel file...I could also break this into 12 seperate calculated fields, but I'm having so much fun with this formula I want to see it work!
    – Mike
    Nov 28 '12 at 17:27
  • hehe for your fun you can play with it and see if it really works! but for production will suggest to break up into 12 separate columns for ease of handling! Nov 28 '12 at 18:47
  • Okay, duh, now I see what you mean...trouble shoot in Excel...okay, trying that...
    – Mike
    Nov 28 '12 at 19:04

Figured out the syntax, using Excel to troubleshoot...Works!

=SUM(IF([Jan 13]>0, ([Jan 13]-[2012 Ending]),"0"))+(IF([Feb 13]>0,( [Feb 13]-[Jan 13]),"0"))+(IF([Mar 13]>0, ([Mar 13]-[Feb 13]),"0"))+(IF([Apr 13]>0, ([Apr 13]-[Mar 13]),"0"))+(IF([May 13]>0, ([May 13]-[Apr 13]),"0"))+(IF([Jun 13]>0, ([Jun 13]-[May 13]),"0"))+(IF([Jul 13]>0, ([Jul 13]-[Jun 13]),"0"))+(IF([Aug 13]>0, ([Aug 13]-[Jul 13]),"0"))+(IF([Sep 13]>0, ([Sep 13]-[Aug 13]),"0"))+(IF([Oct 13]>0, ([Oct 13]-[Sep 13]),"0"))+(IF([Nov 13]>0, ([Nov 13]-[Oct 13]),"0"))+(IF([Dec 13]>0, ([Dec 13]-[Nov 13]),"0"))

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.