none
Round a an Ent custom field to 0 decimal places RRS feed

  • Question

  • Hi there,

    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.


    ashley zipf

    Tuesday, May 15, 2012 11:06 PM

Answers

  • 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!  :)

    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Thursday, May 24, 2012 2:07 AM
    Moderator

All replies

  • 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.
     

    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Tuesday, May 15, 2012 11:45 PM
    Moderator
  • 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 !


    ashley zipf

    Wednesday, May 16, 2012 5:17 PM
  • Ashley --
     
    I think your formula should be:
     
    Format(([Billable] - [Actual Cost]) / [Actual Cost], "0%")
     
    Let us know if that helps.
     

    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    • Marked as answer by AZITGal Wednesday, May 16, 2012 7:45 PM
    • Unmarked as answer by AZITGal Wednesday, May 16, 2012 9:45 PM
    • Proposed as answer by Ofir Marco - PMO , MCTS Monday, October 19, 2015 5:28 AM
    Wednesday, May 16, 2012 7:34 PM
    Moderator
  • it's a bit better, now showing as 4.998989898097 (or similar), with no % sign (which I can deal with) .

    Currently:

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


    ashley zipf

    Wednesday, May 16, 2012 9:47 PM
  • Are you really, really sure its a Text custom field (not number)? Try creating a new Text custom field with this formula.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Wednesday, May 16, 2012 11:07 PM
    Moderator
  • Hi there.

    Yes, it is setup as a custom

    I tried creating a new field with the same results.

    Attached a copy of the field setup.

    text.


    ashley zipf

    Thursday, May 17, 2012 4:59 PM
  • 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


    ashley zipf

    Monday, May 21, 2012 3:54 PM
  • Ashely --

    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.


    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Tuesday, May 22, 2012 12:45 AM
    Moderator
  • I apologize for lack of clarity. The screen shot shows various values for the field that don't seem consistent:

    -1

    NaN

    4.99

    -100%

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



    ashley zipf

    Tuesday, May 22, 2012 6:26 PM
  • capture 2

    Capture 2


    ashley zipf

    Tuesday, May 22, 2012 7:37 PM
  • Ashley --

    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.


    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Tuesday, May 22, 2012 8:16 PM
    Moderator
  • 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?


    ashley zipf

    Tuesday, May 22, 2012 8:46 PM
  • Ashley --

    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.


    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Wednesday, May 23, 2012 12:46 AM
    Moderator
  • 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%?

    See attached


    ashley zipf

    Wednesday, May 23, 2012 6:46 PM
  • and another:

    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.


    ashley zipf


    • Edited by AZITGal Wednesday, May 23, 2012 11:46 PM
    Wednesday, May 23, 2012 11:43 PM
  • 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!  :)

    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Thursday, May 24, 2012 2:07 AM
    Moderator
  • 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!!


    ashley zipf

    Thursday, May 24, 2012 4:58 PM
  • Ashely --
     
    You are more than welcome for my help on this brain teaser, my friend!  :)
     

    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Thursday, May 24, 2012 5:28 PM
    Moderator