Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My Excel is totaling calculation to a 0.
    I want it to display 0.0

    The formulas are like below:
    =EssCell(end,"IT-"&B37,FT,curyr,month) - I am using Essbase with Excel.

    Displaying 0.0 is possible if I will alter the formula to a
    =Values(EssCell(end,"IT-"&B37,FT,curyr,month))
    However in order to do this I have to go and alter hundreds of cells one by one.

    Is there way to fomat the cell to show 0.0

    I have used Format>Cell>Number with 1 decimal - it does work for every value except when it is a zero.
    I had tried Custom but it works on selected 10% of the cells.

    Please, advise,
    thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'll probably have more success if you ask this in one of the forums dedicated to EssBase.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Assuming that you wish to format an Excel range of cell(s).

    • Select the cell(s)
    • Key in Ctrl + 1
    • In the Format Cells window:
      • Select the Number tab
      • Select the Custom category
      • Enter "0.0" (without the quotes), in the Type box
      • Click "OK"
    [attachment=83534:Format_0_0.gif]
    Attached Images Attached Images
    Regards
    Don

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,
    All Essbase forums I've tried are dead since 2002! :-)

    wdwells,
    thanks for this solution. It works on cells that are not using Essbase formula.
    With Essbase - no go. I did use Value but it took me a day to add to half of cells.
    Another half will wait...thanks to all very much!

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Post edited to comply with suggestions made by Hans.[quote name='MS_fan' post='773095' date='30-Apr-2009 11:53']wdwells,
    thanks for this solution. It works on cells that are not using Essbase formula.
    With Essbase - no go. I did use Value but it took me a day to add to half of cells.
    Another half will wait...thanks to all very much![/quote]
    Try selecting the problematic range and then running the following code:[codebox]Public Sub ReworkFormula()
    Dim myfrm As String
    Dim rng As Range
    Dim ctr As Long
    Set rng = Selection
    With rng
    For ctr = 1 To .Cells.Count
    myfrm = .Cells(ctr).Formula
    If Left(myfrm, 9) = "=EssCell(" Then
    myfrm = Mid(myfrm, 2)
    .Cells(ctr).Formula = "=Value(" & myfrm & ")"
    End If
    Next
    End With
    End Sub
    [/codebox]
    Regards
    Don

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Shouldn't it be

    .Cells(ctr).Formula = "=VALUE(" & myfrm & ")"

    By the way,

    myfrm = Right(myfrm, Len(myfrm) - 1)

    can be simplified to

    myfrm = Mid(myfrm, 2)

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='773141' date='30-Apr-2009 15:21']Shouldn't it be

    .Cells(ctr).Formula = "=VALUE(" & myfrm & ")"

    By the way,

    myfrm = Right(myfrm, Len(myfrm) - 1)

    can be simplified to

    myfrm = Mid(myfrm, 2)[/quote]

    Thank you Hans, on the first point, I blindly copied the formula from MS_fan's oiginal post; on the second, I have habitually used this inelegant approach (bad habit).

    I will correct the code now.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •