I'm working on a report where I'm auto-generating a text report based on this months numbers using several cells and IF functions etc. The result is something like this:
Inbound of $24.5M is up $3.8M from last month.
EBIT for the month of ($0.7)M was equally to forecast.
Compared to budget (8.9)M is FX related.
I'm using the TEXT-funtion to format the numbers, currently I'm using:
TEXT(ABS(Numbers!A1),"$0.0;($0.0)") but what I want to is to add M to all numbers directly instead of having to do it with a cell reference to a cell with the value M.
First of all it would make all the formulas a little shorter but the real reason is that it would hopefull allow me to formate the numbers as ($0.5M) instead of ($0.5)M. The differece might seem silly but my company is using the second format and I know that my boss will ask about it.
I know it is possible to do it using custom cell formating: $0.0"M";($0.0"M")
but since the cell contains text as well as numbers I can't custom formate the cell. Is there anyway to add text to the format_text part of the TEXT-function?
I thought that TEXT(A1,$0.0"M";($0.0"M")) might work but unfortunately it doesn't.
Inbound of $24.5M is up $3.8M from last month.
EBIT for the month of ($0.7)M was equally to forecast.
Compared to budget (8.9)M is FX related.
I'm using the TEXT-funtion to format the numbers, currently I'm using:
TEXT(ABS(Numbers!A1),"$0.0;($0.0)") but what I want to is to add M to all numbers directly instead of having to do it with a cell reference to a cell with the value M.
First of all it would make all the formulas a little shorter but the real reason is that it would hopefull allow me to formate the numbers as ($0.5M) instead of ($0.5)M. The differece might seem silly but my company is using the second format and I know that my boss will ask about it.
I know it is possible to do it using custom cell formating: $0.0"M";($0.0"M")
but since the cell contains text as well as numbers I can't custom formate the cell. Is there anyway to add text to the format_text part of the TEXT-function?
I thought that TEXT(A1,$0.0"M";($0.0"M")) might work but unfortunately it doesn't.