Essbase formulas total zero

Topic

New Reply

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.

Reply | Quote

Replies

You’ll probably have more success if you ask this in one of the forums dedicated to EssBase.

Reply | Quote

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”

[/list]

Reply | Quote

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!

Reply | Quote

Post edited to comply with suggestions made by Hans.

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!

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]

Reply | Quote

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)

Reply | Quote

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)

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.

Reply | Quote