Cell will not display limited decimal places

Hello,

I am working on a spreadsheet to analyze voting data, and I have run into an annoying snag. I have several fields in which I'd like the values to only include 2 decimal places -- clearly, an easy thing to do.... However, I am finding that some values seem to be ignoring the "2 decimal places" format, and displaying the entire string of decimal places. I have noticed that this is only occurring where I have formulas that can return either text or numerical values. In those cases, when the formula returns a numerical value, it does treat the value as a number (i.e., I can run calculations on it), but it will not let me limit decimal places in the output cells (or on the subsequent calculations).

Any thoughts? Here's an example of one of the formulas:
=IF(C13="Viable", 1, "") & IF(C13="Excluded", 1, "") & IF(AND('Round 1'!$A$4="Surplus", C13="Elected"), D13*'Round 1'!$A$10, "")

Thanks!
Greg

Originally Posted by gscooper

I have several fields in which I'd like the values to only include 2 decimal places [...]. However, I am finding that some values seem to be ignoring the "2 decimal places" format, and displaying the entire string of decimal places. [....] Here's an example of one of the formulas:
=IF(C13="Viable", 1, "") & IF(C13="Excluded", 1, "") & IF(AND('Round 1'!$A$4="Surplus", C13="Elected"), D13*'Round 1'!$A$10, "")

The formula returns text. The cell numeric format applies only to numeric results, not to text. Ostensibly, change the formula as follows:

=IF(C13="Viable", 1, "") & IF(C13="Excluded", 1, "")
 & IF(AND('Round 1'!$A$4="Surplus", C13="Elected"), TEXT(D13*'Round 1'!$A$10,"0.00"), "")

However, I wonder if the following formula is really what you intended:

=IF(OR(C13="Viable", C13="Excluded"), 1,
 IF(AND('Round 1'!$A$4="Surplus", C13="Elected"), D13*'Round 1'!$A$10, ""))

In that case, the cell formula Number with 2 decimal places should do what you want -- as long as 1.00 is okay when C13 is "Viable" or "Excluded".

Thanks! Actually, your suggestions gave me a different idea, which worked -- I just put a VALUE function around the entire formula, and that solved it!
Best,
Greg

Originally Posted by gscooper

Here's an example of one of the formulas:
=IF(C13="Viable", 1, "") & IF(C13="Excluded", 1, "")
& IF(AND('Round 1'!$A$4="Surplus", C13="Elected"), D13*'Round 1'!$A$10, "")

Originally Posted by joeu2004

Ostensibly, change the formula as follows:

=IF(C13="Viable", 1, "") & IF(C13="Excluded", 1, "")
 & IF(AND('Round 1'!$A$4="Surplus", C13="Elected"), TEXT(D13*'Round 1'!$A$10,"0.00"), "")

However, I wonder if the following formula is really what you intended:

=IF(OR(C13="Viable", C13="Excluded"), 1,
 IF(AND('Round 1'!$A$4="Surplus", C13="Elected"), D13*'Round 1'!$A$10, ""))

Originally Posted by gscooper

Thanks! Actually, your suggestions gave me a different idea, which worked -- I just put a VALUE function around the entire formula, and that solved it!

I presume you mean:

=VALUE(IF(C13="Viable", 1, "") & IF(C13="Excluded", 1, "")
 & IF(AND('Round 1'!$A$4="Surplus", C13="Elected"), D13*'Round 1'!$A$10, ""))

In that case, not only is VALUE unnecessary, but also it is incorrect in some circumstances.

Your original formula returns the null string ("") under some conditions. Note that VALUE("") returns a #VALUE error.

If you always want a numeric result or the null string, my second suggestion is the most direct way to implement the intended logic.

PS.... I used OR() to combine two conditions of your original formula because they both return the same value (1). But since that is only "an example", it might be better for you to see the more general revision, to wit:

=IF(C13="Viable", 1, IF(C13="Excluded", 1,
 IF(AND('Round 1'!$A$4="Surplus", C13="Elected"), D13*'Round 1'!$A$10, "")))