Formatting from Rupees to Lakhs

[Former Member]

Hi Experts,

As per Class 47 of SEBI in India, the result and consolidated report should be reported in lakhs (Figure/100000).

Currently in our consolidation project, we are facing an issue related to numbering format for lakhs. Usually we use rupees (as is figure) and millions (1000000) in EPMformatting for report. 

We are following the steps for formatting as below:

There are different ways of doing the formatting. But we want to use the custom formatting for reports.

1. We are setting Data Validation for formatting report
2. We are using the conditional formatting for Rupees and Millions.
3. But we are not able to get the lacs figures using the custom formatting.

Example:

If the value is 1014000 it should display as 10.1

If the value is 1016000 it should display as 10.2

Thanks in advance,

Ravindra

[former_member190501]

Hi,

Try custom formatting as follows :

[>99999]##\.00,;[<-99999]##\.00,;

Hope it helps...

regards,

Raju

[Former Member]

Hi varaprasad,

It is working using custom formatting...

Thanks,

Ravindra

[Former Member]

Hi VaraPrasad,

The negative figures are getting displayed as positive like -100 will displying as 100 using the formatting given by you. Any solution on the same?

Thanks,

Ravindra

[former_member190501]

Hi,

Try

[>99999]##\.00,;[<99999]##\.00,;

remove -ve sign.

hope it works..

regards,

Raju

[Former Member]

Hi VaraPrasad,

The values are now coming correctly.

Thanks,

Ravindra

[former_member230840]

Hi Ravindra

You could consider using a data manager package to scale the data.In case values rea requires in scaled and unscaled format both , depending on report cmplexity , you could either use a macro (this would be excel driven only report based ) or have a separate model for scaled data

Regards

Surabhi

[former_member186338]

Just to comment - with custom formatting you can only scale by power of 1000:

1000

1000000

...

Vadim

[former_member186338]

Another option is to use custom measures with division by 100000

Vadim

[Former Member]

Hi Vadim,

I have just tried [>99999]##\.00,;[<-99999]##\.00,; and it is working using custom formatting.

What is your point of view? Are you referring to EPMSCALEDATA function?

Thanks,

Ravindra

[former_member186338]

Hi Ravindra,

There is no perfect solution using Excel custom format...

Please, read here:

Excel Number Format: Indian Style Comma Separation

Custom Number Formats (Multiply &amp; Divide by any Power of 10) | Chandoo.org - Learn Microsoft Exc...

I was talking about creation of custom measures with division by 100000.

Vadim

[Former Member]

Hi vadim,

The information was very useful to me. Thanks...

Ravindra

[Shrikant_Jadhav]

Hi Ravindra,

I was facing same issue in my on of the project. We used EPMScaledata function in formatting sheet for content over ride.  Go through the following link, I hope it will help you.

If not you can use macro for scaling.

Regrds,

Shrikant 

[Former Member]

Hi Shrikant,

It is working using scale data.. Thanks for help.

Ravindra