How do I concatenate two currency cells to show a price range?

[nevermore627]

I have one median price, which I would like to change to a range (from
90% to 110% of original value).

Example: Cell A1 is formatted for currency and has a value of 3.50
(symbol set to none)
Cell A2 should be a dollar value range, from 90% to 110% of the value
of A1. The formula I have been using in A2 is this:

=CONCATENATE ("$",A1*.9, "-", "$",A1*1.1)

It works somewhat, but drops 0's and doesn't pay attention to digits.
When I try to format A2 to currency, it doesn't change.

For example, if A1 = 3.75, A2 = $3.375 - $4.125
if A1 = 4.00, A2 = $3.6 - $4.4

Does anybody know how to make cell A2 say "$3.38 - 4.13" or "$3.60 -
$4.40" instead?

Thanks in advance
Mike Simard

(e-mail address removed)

 

[Mark Lincoln]

Try this (untested):

=CONCATENATE (format(A1*.9,"$0.00"), " - ", format(A1*1.1,"$0.00"))

 

[Dave Peterson]

I think you're getting that VBA mixed up with worksheet functions:

=CONCATENATE (Text(A1*.9,"$0.00"), " - ", text(A1*1.1,"$0.00"))

Or drop the =concatenate() function and use the & operator:

=Text(A1*.9,"$0.00") & " - " & text(A1*1.1,"$0.00")

 

[nevermore627]

I tried it and I only get an general error message which highlights the
whole formula. Thanks

 

[nevermore627]

Thanks, Dave - both of those work like a charm. I appreciate the help!
Mike

 

[Mark Lincoln]

Oops! Serves me right for not testing. At least I had the right idea.
<g>