Tell me more ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.
up vote 1 down vote favorite

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!

share|improve this question
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 :) – Arsalan Adam Khatri 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! – Arsalan Adam Khatri 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

1 Answer

up vote 0 down vote

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"))

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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