change number format in cell depending on value in another cell

[maxgolaz]

Hi

Using Excel for Mac 2011. I want to change the number format of a cell depending on the value of text in an adjacent cell. So for example, in cell A i have the a drop down list of options "%" , "M2" , "Unit" etc, and in cell B, if cell A is "%", then I want the number format to be percentage, but if Cell A is M2, I want it to be a whole number, as per example below

     A            B

%	10.00%
M2	10

I looked in conditional formating, but in Mac  2011, the only options are for colour highlights, font changes etc, not number formats. Am I missing something, or is there another way to achieve this without using macros? If not in excel Mac 2011, is this feature avaialble in later editions, I am well due an upgrade!

many thanks

max 

[#2]

Hello @maxgolaz,

That could be done with a series of IF() and TEXT() functions. For example, to search for "%" or "M2" as a unit would be:

=IF($A1="%",TEXT($B1,"0%"),IF($A1="M2",TEXT($B1,"0"),"NO SUCH FORMAT"))

 The series of IF() statements could be extended by replacing "NO SUCH FORMAT" with additional IF() statements.