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

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

mike@streffco.com

Try this (untested):

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

nevermore627 wrote:
> 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
>
> mike@streffco.com

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

Mark Lincoln wrote:
> Try this (untested):
>
> =CONCATENATE (format(A1*.9,"$0.00"), " - ", format(A1*1.1,"$0.00"))
>
> nevermore627 wrote:
> > 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
> >
> > mike@streffco.com

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




Mark Lincoln wrote:
>
> Try this (untested):
>
> =CONCATENATE (format(A1*.9,"$0.00"), " - ", format(A1*1.1,"$0.00"))
>
> nevermore627 wrote:
> > 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
> >
> > mike@streffco.com

--

Dave Peterson

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

Dave Peterson wrote:
> 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")
>
>
>
>
> Mark Lincoln wrote:
> >
> > Try this (untested):
> >
> > =CONCATENATE (format(A1*.9,"$0.00"), " - ", format(A1*1.1,"$0.00"))
> >
> > nevermore627 wrote:
> > > 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
> > >
> > > mike@streffco.com
>
> --
>
> Dave Peterson

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

Dave Peterson wrote:
> 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")
>
>
>
>
> Mark Lincoln wrote:
> >
> > Try this (untested):
> >
> > =CONCATENATE (format(A1*.9,"$0.00"), " - ", format(A1*1.1,"$0.00"))
> >
> > nevermore627 wrote:
> > > 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
> > >
> > > mike@streffco.com
>
> --
>
> Dave Peterson