Question Locked

Display Text and Forumla Result in Cell (excel 2007)

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.972908347958)

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!

3 replies

by jelk

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).

by tobif

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. :) http://www.pestproducts.com/roundup_herbicide.htm

This discussion is closed to new replies.