If Statement for Number Formatting

[dms04d]

For a cell that will display a percentage followed by a number associated with the percentage, I have the below formula. The issue I have with the formula has to due with the number formatting, which will display a number less than 1,000,000 with a "k" (e.g. 150k) and greater than 1,000,000 with an "m" (e.g. 15m). The formatting formula works fine with the "k" less than 1,000,000 numbers, but it shows "#VALUE!" error with numbers in the millions. Suggestions?

=IF(V48<0.005,"",TEXT(V48,"0%")&CHAR(10)&TEXT(V42,IF(V42<1000000,(("$#,##,")&"k"),(("$#,##0.00,,")&"m"))))

 

[dms04d]

I removed the last element of the formula &"m" and it worked properly... I just need to somehow get the letter m back into the formula

 

[kweaver]

Do you mean V42 or did you mean to write V48?

 

[dms04d]

Do you mean V42 or did you mean to write V48?

Yep, those fields are correct. V48 is the percentage value and V42 is the dollar value.

 

[dms04d]

Even stranger... the original formula works if you simply replace the letter M with any other letter!

 

[shaowu459]

Even stranger... the original formula works if you simply replace the letter M with any other letter!

how about change &"m" to &"\m"?

 

[dms04d]

how about change &"m" to &"\m"?

YES!!!!! Thank you so much!! ?

 

[shaowu459]

YES!!!!! Thank you so much!! ?

you are welcom there are several letters have specific meanings in the format code, for example, m stands for Month, =TEXT(TODAY(),"e-m-d") will return "2020-5-19", =TEXT(TODAY(),"e-\m-d") returns "2020-m-19". so if you want to use the letter m just as a common letter, you should add \ before the letter to eliminate the specifict meanning of Month.