Conditionally concatenate text

[pdub]

Hello,

What I'm looking to do is compare 4 cells containing dollar values to determine the minimum value. The cell to the right of each should then either display the value of the original cell (immediately to its left) if it is the minimum of the 4 cells being compared or the difference between the cells. This is fairly straight forward. I have accomplished this as follows (this is a formula in cell W14, comparing four values in cells R14, V14, Z14, and AC14):

=IF(V14=MIN($R14,$V14,$Z14,$AC14),V14,V14-MIN($R14,$V14,$Z14,$AC14))

I'd like to display a plus symbol when the value is greater than the minimum cell. For example:


[TABLE="width: 600"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bid 1[/TD]
[TD]Bid 1*[/TD]
[TD]Bid 2[/TD]
[TD]Bid 2*[/TD]
[TD]Bid 3[/TD]
[TD]Bid 3*[/TD]
[TD]Bid 4[/TD]
[TD]Bid 4*[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Price[/TD]
[TD]$5.00[/TD]
[TD]$5.00[/TD]
[TD]$7.00[/TD]
[TD]+$2.00[/TD]
[TD]$7.50[/TD]
[TD]+$2.50[/TD]
[TD]$6.50[/TD]
[TD]+$1.50[/TD]
[/TR]
</tbody>[/TABLE]

I tried to concatenate text in the IF statement for the [value_if_false] parameter, but only get an error.

I'd like to avoid using VBA, as this is for use by a client that is not familiar with VBA.

Thanks!

 

[jackhandey]

The concatenate should work fine - please post the formula you attempted to use if this doesn't help:

=IF(V14=MIN($R14,$V14,$Z14,$AC14),V14,concatenate("+ $",V14-MIN($R14,$V14,$Z14,$AC14)))

 

[pdub]

The concatenate should work fine - please post the formula you attempted to use if this doesn't help:

=IF(V14=MIN($R14,$V14,$Z14,$AC14),V14,concatenate("+ $",V14-MIN($R14,$V14,$Z14,$AC14)))

Thanks jackhandey, that is the formula I used. The problem is it removes any other number formatting from the cell, like commas and decimals. IE I get "+ $2309" rather than "+ $2,309.00"

Thoughts?

 

[jackhandey]

Ahhh, gotcha. You can actually accomplish this with Conditional formatting. The process should be about the same with 2007 and 2010...

Looking at your cell W14 to start (keep the formula you're using in the original post):

Highlight the cell, click Conditional Formatting-> New Rule-> Use a formula to determine which cells to format

Enter this formula in the box: =not(W14=V14)
Click Format...
Under the Number tab, click Custom
Enter this in the "Type:" box: "+ "$#,##0.00

All it's doing is saying if the comparison value is not the same as the formula result, give the currency result but add a "+ " in front. If you don't want the space between the plus and the dollar sign, you can remove the space from within the quotes.

Since your comparison calculation cells are all the same relative position away from the cell they're looking at, you can copy the cell with the conditional formatting and paste only the format into the other cells. As long as the formula you entered in the box was in relative format, it will adjust to wherever you copy it.