Number format

[Ufoo]

Hello gurus, I have struggled to format a value to show something like TZS 40,000.00 (39%). The number which I want to format is 3,398,100. I have managed to come up with this formula: ="TZS "&TEXT(G8-F9-B21,"#,###,###.00 "&" ("&TEXT((G8-F9-B21)/G8,"#"&"%)")); which has led to a wrong answer: TZ 339,810,000.00 (12%). will appreciate your help in resolving this problem.

 

[Hui]

What values are in F9 and B21 ?

 

[Hui]

Your structure is a little bit wrong
Try this:
="TZS " & TEXT(G8-F9-B21,"#,###,##0 ") & "(" & TEXT((G8-F9-B21)/G8,"#,000"&"%)")

 

[Ufoo]

Your structure is a little bit wrong
Try this:
="TZS " & TEXT(G8-F9-B21,"#,###,##0 ") & "(" & TEXT((G8-F9-B21)/G8,"#,000"&"%)")

Thanks a lot Ninja. It has worked perfectly. When you have time please elaborate how you have come up with the formula.

 

[Hui]

You wanted TZS 40,000.00 (39%)
so instead of trying to incorporate it all in one Custim Number format it is easier to break it into each component

Namely

TZS ="TZS "
40,000.00 & TEXT(G8-F9-B21,"#,###,##0 ")
( & "("
39% ) & TEXT((G8-F9-B21)/G8,"#,000"&"%)")

Then concatenate them all together