How to Create Singular/Plural Formats in FORMAT CELL option?

How to Create Singular/Plural Formats in the FORMAT CELL option?

Dear Forum,

I tried researching on the below mentioned links provided to me by NBVC in the thread below which was for a different need..

http://www.excelforum.com/excel-work...-currency.html

Have a look at these 2 links to help you understand custom formatting....

http://www.ozgrid.com/Excel/CustomFormats.htm

http://peltiertech.com/Excel/NumberFormats.html

But unfortuanately, did not find anything regarding the present requirement..

I come across a lot of situations when I translate a Mathematical result in a sentence such as the mentioned in the following cases :

The answer i get in a Cell Let's say A1 is 18 Months..

Result : The Final Amount of Rs X /- can be paid in cell A1 i.e 18 months

Now if tthe value in cell is 1 then I wanto get "1 month" in the cell A1 as a text format to be used in the sentence as well..

So, basically I neeed to have a Custom Format which can differentiate between a PLURAL & SINGULAR result..

1st thing is to get a CUSTOM format for the cell A1 itself where the expected answer is just a number and by using this format it can simply be shown as 0 Months, 1 Month or 2 Months..so that it can also be usable for other Mathematical Operations but for display purpose = # Months.

The word "Month" should be dynamically changeable so that I can use the same format for anything such as 0 Mangoes, 1 Mango or 2 Mangoes..


2nd thing is to also have a TEXT format so that this can be used in the middle of sentence..

Everytime, I have to employ an IF condition for such scenarios however this can be useful if there exists a way in FORMATIZING zdat...!

Warm Regards
e4excel

Originally Posted by e4excel

get a CUSTOM format for the cell A1 itself where the expected answer is just a number and by using this format it can simply be shown as 0 Months, 1 Month or 2 Months.

NBVC's point was you can use:

Please Login or Register  to view this content.

Originally Posted by e4excel

The word "Month" should be dynamically changeable

You can not use "variable" units in a Custom Format - you would need a separate format for each.

Originally Posted by e4excel

...have a TEXT format so that this can be used in the middle of sentence.

above in TEXT format:

Please Login or Register  to view this content.

obviously here the Unit can be variable (ie tied to other cells)

Originally Posted by DonkeyOte

You can not use "variable" units in a Custom Format - you would need a separate format for each.

above in TEXT format:

Please Login or Register  to view this content.

obviously here the Unit can be variable (ie tied to other cells)

Utopian Thought..!

I <thought> if this would have been dynamic then it would have been easier to use the format to adjoining columns in a scenario such as Col A- Year, Col B- Month Col C - Day then a Term Deposit with a Period of 2 year 5 Months and 17 Days..

Like have the format just refer the value in the Cell of the Column in the First Row with the $Row Fixed or Absolute and the Column Movable so..

Please Login or Register  to view this content.

Then The values would show different Units depending on the Values in the First Row which or Any Fixed Row used for Column Heading..

Thanks a Lot DO..

Just wanted to share this with you as why I had thought of making it dynamic..

Warm Regards
e4excel