Display Text and Forumla Result in Cell (excel 2007)

By forbes.dan ·
I have a formula to display text and a formula result in one cell as follows:

="BALANCE ("&MIN(B3:WU3)&")"

The MIN range shows me the lowest daily balance in a range of numbers. The problem is that I can't get the MIN formula result to diplay only 2 decimals. Instead it is showing a long string:

BALANCE (297.97290834795

I want it to show:

BALANCE (297.97)

I have changed the formating on the cell to variety of different things (number, accounting, currenty, text, etc) with no change.

Any suggestions would be appreciated!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

round or truncate options

by Jelk In reply to Display Text and Forumla ...

Depending how you want the value to show you can use either =round (), =roundup(), =rounddown(), =trunc() (this will just cut off after # digits you specify). I imagine that you'd be interested in either the round option, but possibly the truncation option if you just want to cut off the rest of the digits.

This changes how the formula shows after calculation, but I do believe that the full calculated number is retained in the background. If you need to use this number further and you do not want the full number retained, then copy, and paste special values overtop and you will lose the formula and just have the value displayed for you to be able to use in further calculations.

For paste special values you can alt + e, s, v or right click and paste special select values from the top paste section.

The other option is to just show less digits with the format options(format the type as a number and there is # of digits option).

Collapse -

Here's how

by TobiF In reply to Display Text and Forumla ...

Formally speaking, your formula creates a text value. Therefore any formatting you try to apply on the cell level won't have the effect you want.

The function you're looking for is TEXT.

Try this:
="BALANCE ("&TEXT(MIN(B3:WU3);"0.00"&")")

There's one more way to get the same result.
If you leave just the formula =MIN(B3:WU3) in the cell, and then apply a custom format "Balance ("0.00")"
The good thing with this approach is that you'll still be able to refer to this value from other cells.
Note: The value of this cell would can have many decimals, but you'll always see 2 decimals. If you refer to this value from another place with different format, you may see these decimals again.
If you want to adapt the value itself to not have more than 2 decimals, then you should use TRUNC, FLOOR, CEILING, ROUND, ROUNDDOWN or ROUNDUP.
However, be careful with ROUNDUP, since it kills all vegetation you might hit. :)

Related Discussions

Related Forums