Format Class ranges as currency

[stubarnes]

Hi I have the following but would like the formatting of numbers to be displayed as currency

=Dual(Replace(Class(TP,500),'<= x <','-'),Class(TP,500))

I tried inserting Money('£#,##0') before replace but did not work..

Any ideas?

[Gysbert_Wassenaar · Accepted solution]

Perhaps something like this:

Dual( Money( Floor(TP,500) , '£#,##0') & '-' & Money( Ceil(TP,500) , '£#,##0') , Floor(TP,500))

[MarcoWedel]

Hi,

some solutions might be:

=Dual('£'&Replace(Class(TP,500),' <= x < ',' - £'),Class(TP,500))

=Dual(Money(Floor(TP,500),'£#,##0')&'-'&Money(Floor(TP,500)+500,'£#,##0'),Floor(TP,500))

hope this helps

regards

Marco

[balabhaskarqlik]

May be this:

=Dual(Replace(Class(TP,500),'<= x <','to'), Num(Class(TP,500)))

[balabhaskarqlik]

Check this:

Using Money() with Class()

[stubarnes]

Thanks for your help on this, works perfectly!

If I could ask an additional question, is it possible to format the numbers as a percentage #,##0.0%

[Gysbert_Wassenaar]

Yes, with the num function instead of the money function.

[MarcoWedel]

Hello gwassenaar,

I noticed a difference in the results of the Class() vs. Floor()/Ceil() approach due to Floor and Ceil both letting integer values unchanged. Can you check?

thanks

regards

Marco

[Gysbert_Wassenaar]

Yeah, if a value falls exactly on a border value then an incorrect result is returned. Your expression always returns the correct result.