CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Tempyr
Tempyr

Reputation: 47

Limit Excel Formula Result Containting Numbers and Text to 2 Decimal Places

I have the following formula I am using in a profit and loss excel.

=IF('2015'!F5='2014'!F4,"No Change",IF('2015'!F5>'2014'!F4,"Profit: $",IF('2015'!F5<'2014'!F4,"Loss: $")))&'2015'!F5-'2014'!F4

This works fine in most cases, but sometimes it decides to spit out a ton of decimal places. Is there a way I can limit this output to 2 decimal points while still including the text? If not, the text can go, but ideally I would like to keep it.

Thanks in advance!

Upvotes: 0

Views: 2894

Answers (2)

KiwiSteve
KiwiSteve

Reputation: 116

You can use =VALUE("Text") to have Excel view your text string as a value, if it can be resolved into one. I use this when "Text" has been compiled on the fly, using &.

A bit like =INDIRECT("Text") resolves a text string into a range

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

Just wrap a Round() around the numerical parts: Round(f5,2) will round to Two decimal places.

Edit: Per your comment, wrap Text([],#,###.##). around your final result. If I read your formula correctly, you could use Text(ROUND('2015'!F301-'2014'!F318,2),"#,##0.00") I believe.

(Thanks @ScottCraner for text format)

Upvotes: 3

Related Questions