• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to use CF Function for rounding off

V

VDS

Member
Dear All,

I have the data of following figures at A!

1,40,418.00
2,26,350.00
21,58,555.00


After data entry it should display into lacs

like 1.40 Lacs
2.26 lacs
21.58 Lacs I us function =Round(A1/100000,2) But How to use it in Conditional formatting.


VDS
 
Hi VDS,

You can use the same formula in conditional formatting formula bar it will work. However can you tell what actually you are looking for?

Regards
Abdul Matheen
 
@VDS

What is the purpose of conditional formatting? What I want to ask when do you want to apply CF, means above any value or below any value or equal to any value or something like that, what will be the condition?

Regards,
 
@Somendra Misra / Fasheesh,

The main issue what wanted to highlight is to display the figure in lacs (shortened form). For eg. the figure of Rs 8,35,426 will be displayed as Rs 8.35. In the heading, it will be displayed as Rs in lacs. This is required to avoid mistakes while data entry & multiple data entry becomes little bit difficult.

VDS
 
Ok so you want an entry to be highlighted if if it is entered as 8,35,426, if it is entered as Rs 8.35 then it should not be highlighted? Is that correct?
 
@VDS

What I understand from above is this.

You are entering values in one cell. Those values are getting converted in Lacs in another cell through a ROUND formula. & you want to highlight those cells where figures are not in Lacs.

Regards,
 
Fasheesh,

It is not highlighted, but want to display in shortened format. Data entry of 8,35,426 will be displayed as 8.35 just like in any of Bank Format.


SANTHOSH
 
@VDS

Wait one minute: Your problem is creating a confusion between Data Validation & CF. I would suggest you to upload a small sample file and explain the problem in terms of cell refs. where you want want & where you will enter what?

Keep the language simple.

Regards,
 
Hi VDS..

Custom Number Format & Conditional Format are two different thing..

Still with a lil bit doubt, trying to adjust both in a single custom Format.. try this Custom Number Format..

[>100000]"Rs. "0\.00, " L";#;#
 
@Debraj/Somendra

Attaching herewith the sampl data with comments. This is meant for Insurance policies for different sites. When an Insurance policy is entered /renewed, the total sum assured is in 8,35,426 to be entered. But it has to display 8.35. The words in Lacs already written in column Header. Hope attached data makes my question clear and dont confuse with it.


VDS
 

Attachments

  • DUMMY DATA FOR CHANDOO 17.04.2014.xls
    24.5 KB · Views: 1
@Debraj,

I got it. little change in the formula. [>100000]" "0\.00, " ";#;#. This is good too. But if the value is less than 1 Lac, it has no effect. How to rectify ?


VDS
 
Hi ,

I do not know why the above format is required ; if you are showing all amounts in lakhs , whether they are above 1 lakh or not is immaterial. A straightforward format like this should work :

" "0\.00, " ";-" "0\.00, " "

Narayan
 
@Narayan sir,

This is nice & perfect. Thank you so much.

Further, The specified report is required to be submitted to bank periodically.

I tried one more option . [>0]" "0\.00, " ";#;#. It also works nicely.


VDS
 
Hi Santhosh ,

You are right that what you have posted will also work , but if you go through the following link , you can see that the [>0] part is not required.

The format codes are compartmentalized into 4 sections , where the first section i.e. the section before the first semi-colon ( ; ) is the section which is applicable to positive numbers , the second section is applicable to negative numbers , the third is applicable to zero values , while the last section applies to text values.

Thus a distinction such as [>0] is not necessary in the first section , since this section is anyway applicable only to positive numbers.

Bracketed sections are used where you wish to compartmentalize within a particular section i.e. suppose you want that numbers between 0 and 1000 should be coloured RED , while those above 1000 should be coloured GREEN , you can use this feature ; all these numbers are positive numbers , but even within this section , you want to establish 2 sub-sections , so that one format applies to one sub-section , while another will be applicable to the second sub-section.

http://jonvonderheyden.net/excel/a-comprehensive-guide-to-number-formats-in-excel/

Narayan
 
Thanks Narayan. For support...

VDS.. Tonight i will be in anupam/citywalk.. Planning for Two States..
wanna join..:)
 
@Somendra

Thanks too. This is quite clear up to a far extend. Learning is a never ending process.


VDS
 
You must log in or register to reply here.
Back
Top