Displaying a "-" for a Zero value where the cell has an existing Indian Currency Format.

[e4excel]

Displayiing a "-" for a Zero value where the cell has an existing Indian Currency Format.

Dear Forum,

I use a specific Number format to display Indian Currency using the below mentioned formula in the Format Cells Options:

With Decimal Places

=[>9999999]"Rs "#\,##\,##\,##0.00;[>99999]"Rs "#\,##\,##0.00;"Rs "#,##0.00  

Without Decimal Places

=[>9999999]"Rs "#\,##\,##\,##0;[>99999]"Rs "#\,##\,##0;"Rs "#,##0  

This gives me a very neat numeric value with the Rs prefixed to the number...
Now I need to get a "-" or just a NEGATIVE MINUS SIGN incase the value is zero for specifically mentioning that the paticular cell has not been filled, I know I can do the same using a regular IF condition but I am not aware whether a Format Cell Option is available which can do the needful..

Warm Regards
e4excel

[NBVC]

See this link: Number Formats

Look at the last section of the table... it says you can't have more than two conditions where a condition is defined by including these symbols (Condition may be <, >, =, >=, <=, <>)

[e4excel]

Thanks NBVC for sharing this link, but I have not understood whether what I am looking to do is possible or not..

Honestly, one of my colleague had shared the Number Format which I often use, but this is the first time I wanted to actually not have a Zero as there are certain things which are included and not included at the same time so I want that to be shown as "-" to show that its not included..

If it is possible can you please help me with it and also for me to understand how would I normally do it even for regular numeric entries as I have never done this earlier just thought this might be remotely possible..

Warm Regards
e4excel

[NBVC]

This is just like an IF formula...

If this were an IF formula in say A1, then the formula for format selection would be something like:

=IF(A1>9999999,"Rs "#\,##\,##\,##0.00,IF(A1>99999,"Rs "#\,##\,##0,"Rs "#,##0))

Here you can use many nested IFs to accomplish the end task...

Unfortunately, with cell formatting, you are only allowed, one "nested if" per se... it's like having only 3 conditional formats in Excel 2003...

so the first condition is to check if cell number is greater than 9999999, if it is apply first format, if it is not, you have another condition to check, that is check if cell number is greater than 99999, if it is apply second format, else apply the third.

Now, I am not familiar with Indian currency and formatting, but that is the concept.

I found here: http://chandoo.org/wp/2010/07/26/ind...-format-excel/ another way that might help. You can try applying the "permanent solution" by changing settings, then you can apply a more basic format to change the 0's to "-".

[e4excel]

Thanks once again, NBVC as always you have helped me to understand the concept though I have not completely understood it but will be revising it again and again..
This is one of the things which is invariably used without much bother to actually know the intricacies..

Time and again your help has helped me so thanks once again..

One more thing, I was not able to add reputation as it mentioned spread it to someone else so whats the funda for that, dont re-collect that I have rep-ed you very recently so there should not have beed an issue..

Please explain me the time-frames..for this Add Reputation frequency..

Warm Regards
e4excel

[NBVC]

No worries about the rep.

You last gave me rep on March 1st on this: http://www.excelforum.com/excel-work...81#post2722481.... and it was appreciated.

if you have not handed out rep to anyone else since then, then you cannot give rep to me again until that is done (i.e. you can't give the same person rep twice in a row, even for different threads). Again, don't worry though. I know you appreciate the help.

[e4excel]

Oh thanks again for explaining me this Funda abot rep's as I wsa not sure why it would prevent me..
I Can always reserve and alternate the reps

[johnjohns]

@e4excel and NBVC

Sorry this is no answer to e4excel's query. Just thought of sharing this macro for Indian number format. After installing Rupee Foradian font you can select the cells and run this. It gives the rupee symbol itself instead of RS.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[e4excel]

Hwy thanks John,

It may not be completely relevant but definitely very useful indeed and very thoughtful of you to share the code, I can always try the other one, I think thats the one with the Regianl "R" symbol..

Thanks again....