24/جمادى الثانية/1433 11:06 م
I have project server 2010 and created a custom field for ROI%. However, it does not allow me to represent the number in the field itself as a percentage. This is not a huge issue, but would be nice to resolve. My larger question is now to get the number to round to a value such as 55% . Currently I instead see 0.547851215851 etc...
I have tried to set the field up as text, number, etc. Tried to use some excel RND commands I found online, but to no avail.
Any assistance is appreciated.
24/جمادى الثانية/1433 11:45 مالمشرفAshley --First of all, to meet your reporting requirement, you would need to create your formula in a custom enterprise TEXT field and not a Number field. Secondly, you will need to use the Format function to properly format the number resulting from the formula. Write your formula in the Text field similar to the following example:Format(Insert Formula Here, "0%")The preceding will format the number resulting from the formula as a percentage with no decimal points. Just a thought. Hope this helps.
- تم الاقتراح كإجابة بواسطة Marc Soester [MVP]MVP, Moderator 25/جمادى الثانية/1433 12:44 ص
25/جمادى الثانية/1433 05:17 م
I've got my simpl ROI formula set up as a Text Custom field :
Format(([Billable] / [Actual Cost]) / [Actual Cost], "0%")
It sill displays results as:
0.643717164007815 (or similar )
I am sure this is user error-help is appreciated !
25/جمادى الثانية/1433 07:34 مالمشرفAshley --I think your formula should be:Format(([Billable] - [Actual Cost]) / [Actual Cost], "0%")Let us know if that helps.
25/جمادى الثانية/1433 09:47 م
it's a bit better, now showing as 4.998989898097 (or similar), with no % sign (which I can deal with) .
Format(([Billable] - [Actual Cost]) / [Actual Cost], "0%")
I tried the below thinking that might work, but no go.
Format(([Billable] - [Actual Cost]) / [Actual Cost], "0.00%")
25/جمادى الثانية/1433 11:07 مالمشرفAre you really, really sure its a Text custom field (not number)? Try creating a new Text custom field with this formula.
Rod Gill Project Management
26/جمادى الثانية/1433 04:59 م
Yes, it is setup as a custom
I tried creating a new field with the same results.
Attached a copy of the field setup.
30/جمادى الثانية/1433 03:54 م
To add to the confusion, the field is representing in different ways in different projects.
See attached-it is the far right field.
These are all using the above formula and field setup
01/رجب/1433 12:45 صالمشرف
Forgive me for being dense, but from the screenshot you included, it looks like the formula is working as desired. Correct me if I am wrong.
01/رجب/1433 06:26 م
I apologize for lack of clarity. The screen shot shows various values for the field that don't seem consistent:
Some have the percent sign, as you can see. some throw a NaN, some -1, and others just a number to two decimal places with no %. For one particular project, I have a value in PWA of 94.0152024323892 (SEE CAPTURE 1). In project pro, under project information, it shows the same field as 9402% (SEE Capture 2). I can tell project is rounding (not the way I want, but it is rounding), but PWA does not round, nor does it show the %. Any thoughts? Sorry, no administrator here:) On my own. Usually I can google for answers, but this is a tough one.)
01/رجب/1433 07:37 م
01/رجب/1433 08:16 مالمشرف
What you see is totally bizarre. This should not be happening. Out of curiosity, where are the NaN and Infinity values derived from? Are they values in a Lookup Table associated with another field? Let us know.
01/رجب/1433 08:46 م
See attached. I have no idea where those values come from. I included a screen shot of the ROI % setup and formula. It includes a custom field 'billable.' I also included the setup for this field. Note that it is a 'cost' field. I tried also setting it up as 'number' and as 'text' but yielded the same results.
What truly makes me feel nervous, is that the values actually change without me modifying any field at all. For example, I just checked out, and checked in a project, and made no changes. When I published it, the PWA value is now 9402% , just as the project version was in the example I showed above previously. If you do the math, it should be 94%. What on earth might be occurring?
02/رجب/1433 12:46 صالمشرف
I am as puzzled as you. Humor me: what happens it you delete the field with the formula and then recreate it? You would also need to open each project, press the F9 function key to recalculate the formula, and then save and publish the project. Does this help at all? Let us know what you find.
02/رجب/1433 06:46 م
Ok, deleted and recreated, and refreshed a few projects- it's looking a little better-more consistent (for now :) ).
now, how might I get it to show 99.02% or 99% instead of 9902%?
02/رجب/1433 11:43 م
I modified some of the values for a few test cases and now I see the below. For example, in the 'Veronica's test plan' you'll see 15.0025604096655
ROI is 15%. So, it appears that in some cases, when ROI is 100% (pos or neg) or greater, it represents it correctly (% sign, rounded, etc).
However, when it is under 100, it does not. Weird! :)
I wish this were totally consistent, but I have one at 143.02304368699 so it's not entirely reliable.
- تم التحرير بواسطة AZITGal 02/رجب/1433 11:46 م
03/رجب/1433 02:07 صالمشرفAshley --It just dawned on me tonight that your underlying ROI formula logic may be wrong. Shouldn�??t the formula be something more like the following?Format(([Billable] - [Cost]) / [Cost], "0%")Experimenting with your original formula tonight, I also got wacky values that changed over as progress was entered over the life of the task. Alternately, maybe the formula should be more like the following:Format(([Billable] - [Actual Cost]) / [Cost], "0%")Furthermore, if your original formula is correct (maybe I don�??t understand how you calculate ROI), then you might try the following technique with whatever formula you use:Cstr(Format(([Cost1]-[Actual Cost])/[Actual Cost],"0%"))Using the CSTR function in the preceding formula, you are forcing a formal conversion of the calculated percentage to a text string. Just a thought. Hope this helps with your very perplexing problem! :)
03/رجب/1433 04:58 م
you are correct! The more I thought about the calc we really want to show for total project cost (using original estimates), the first formula makes sense.
Format(([Billable] - [Cost]) / [Cost], "0%")
I think I need another calc for POST project review to ensure we didn't overrun-I'll play with this and approach it over the next few days.
For now, I think I am good! (for now :p ) Thank you!!
03/رجب/1433 05:28 مالمشرف