EndUserSharePoint.com: Resource for Calculated Columns
In addition to Dessie’s series on “Taming the Elusive Calculated Column” and his downloadable 51 page Ebook on Calculated Columns, there is another End User resource for calculated columns. Christophe at Path to SharePoint has provided a few insights that might help in your investigation.
Christophe has two articles of interest:
Here is something from one of his columns that will come in handy as you are getting started:
Some limitations of calculated columns
- You cannot reference a value in a row other than the current row (as opposed to Excel for example).
- You cannot reference a value in another list or library.
- You cannot reference lookup fields in a formula.
- You cannot reference the ID of a row for a newly inserted row. The ID does not yet exist when the calculation is performed.
Thanks Mark :-)
I’ve had for example the case of a user trying to do the equivalent of a vlookup in SharePoint, and this is not something you can achieve with calculated columns.
I still have a couple of articles about calculated columns, I’ll publish them later this month.
I have just added to my blog 3 more posts related to calculated columns:
Using calculated columns to write HTML
Apply color coding to your SharePoint lists
How to open hyperlinks in a new window (or not)
The link:
http://PathToSharePoint.wordpress.com
Christophe
hello,
based upon the first bullet point in the list of limitations above, I’m wondering if you have any workaround/suggestion for my scenario:
I have a list of projects that I need to calculate the average % complete for. Each project falls under 1 of 3 categories e.g. Level 1, Level 2, Level 3 so I need to calculate the % complete for all projects in Level 1, then Level 2, finally Level 3. The data I’m using is stored in custom list which I also use for another “report” built using the DFWP in SPD. I’d like to use the same list for each “report” so users don’t have to double entry. Or, maybe there is a way to pull the % complete column and Category into another list in order to calculate the avg % complete. Suggesions?
Hi beargal,
As you are already using the DFWP for your project, you should be able to directly get the avg % complete.
I don’t have SPD right now, off the top of my head here is how it should work:
Use grouping – by level then by project. In the header or footer, drag the % complete field, SPD will automatically calculate the sum for the group. Right-click on the result and change from sum to average.
I am assuming that your % complete is the average of the tasks’ % complete (all tasks have the same weight).
Hi Christophe,
Your solution is close. I grouped by Level and then project and selected both header/footer. In the footer, a “count” of rows is automatically created. When I click and drag %Complete to the header, the number defaults to the %Complete number in the first row of that data and disregrads any rows below it. When I click and drag to the footer, it captures the %Complete number of the row above it. I don’t know how to create a formula to enter in the XPath Expression field that will calculate the average automatically. I’ve tried sum(PercentComplete) div count(PercentComplete) and a few others, but each results in an error.
One other quick note – I’m not using tasks to determine the %Complete, just entering the number manually in the list as this is a high level example.
OK, I opened SPD and this is how it works.
After you drag % complete to the header or footer (you’re correct about the displayed value, but just ignore it), you have 3 context menus available.
Start by clicking on the number: a menu with a flash icon will show up UNDER the number, select “AVERAGE”.
Then right-click on the number, select:
Format item as > number > percentage
You should now have the average % complete for the group.
Hey C,
That worked!! I had seen the flash icon earlier, but it didn’t dawn on me to click on it. I then applied a condition to “hide” that field when Greater Than/Equal to ‘0′ so that it doens’t show on the web part. Very cool!!! thanks!!
~beargal
I have the exact issue- I want to display an average of the % complete values for a set of items.
When I click the flash icon in the footer after dragging hte % complete field over, my only choice is “count.” Why can’t I also see the “average” option?