Prev: what is the maximum number of rows excel for mac 2008 has?
Next: Sumif does not calculate beyond 1000 rows
From: Melody on 19 Mar 2010 15:19
Hi,

Not sure this is possible but...I have a cell that has a number range in it
and based on an amount in another cell want to calculate a new range. For
example:

Initial Range: A1 = 10 - 12
Calc Amount: A2 = 5
Final Range: A3 = 50 - 60

I think I can get the results by concatenating two formulas I'm just not
sure how to enter the original numbers (A1) or how to distinguish between the
two in the final formula (A3)

Using Excel 2003. Hope this makes sense. Thanks.
From: Fred Smith on 19 Mar 2010 15:28
I would put the range in two different cells (eg A1 and B1). Then the
multiplication is easy. If you want to display the concatenated range, use:
=a1&" - "&b1

Regards,
Fred

"Melody" <Melody(a)discussions.microsoft.com> wrote in message
news:D01EBE06-5429-49B7-8850-322C32F0C120(a)microsoft.com...
> Hi,
>
> Not sure this is possible but...I have a cell that has a number range in
> it
> and based on an amount in another cell want to calculate a new range. For
> example:
>
> Initial Range: A1 = 10 - 12
> Calc Amount: A2 = 5
> Final Range: A3 = 50 - 60
>
> I think I can get the results by concatenating two formulas I'm just not
> sure how to enter the original numbers (A1) or how to distinguish between
> the
> two in the final formula (A3)
>
> Using Excel 2003. Hope this makes sense. Thanks.

From: Gary''s Student on 19 Mar 2010 15:30
Try this:

=A2*(--LEFT(A1,2)) & " - " & A2*(--RIGHT(A1,2))
--
Gary''s Student - gsnu201001


"Melody" wrote:

> Hi,
>
> Not sure this is possible but...I have a cell that has a number range in it
> and based on an amount in another cell want to calculate a new range. For
> example:
>
> Initial Range: A1 = 10 - 12
> Calc Amount: A2 = 5
> Final Range: A3 = 50 - 60
>
> I think I can get the results by concatenating two formulas I'm just not
> sure how to enter the original numbers (A1) or how to distinguish between the
> two in the final formula (A3)
>
> Using Excel 2003. Hope this makes sense. Thanks.
From: Bernard Liengme on 19 Mar 2010 15:41
Using 10 - 12 is going to be messy - Excel will be over helpful and turn it
into a date (10- Dec, or 12-Nov in USA)
If you can live with entering, for example: 10 to 12
Then this formula: =LEFT(A1,2)*A2&" to "&RIGHT(A1,2)*A2
will return: 50 to 60
Limitation: must have two digits on each side of the "to"

If you remember to format the A1 cell as Text then type 10 - 12, Excel will
no make it a date. Then you can use
=LEFT(A1,FIND("- ",A1)-1)*A2&" - "&MID(A1,FIND("-",A1)+1,10)*A2
to get the required output (only limitation is that the last number has less
than 10 digits)
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Melody" <Melody(a)discussions.microsoft.com> wrote in message
news:D01EBE06-5429-49B7-8850-322C32F0C120(a)microsoft.com...
> Hi,
>
> Not sure this is possible but...I have a cell that has a number range in
> it
> and based on an amount in another cell want to calculate a new range. For
> example:
>
> Initial Range: A1 = 10 - 12
> Calc Amount: A2 = 5
> Final Range: A3 = 50 - 60
>
> I think I can get the results by concatenating two formulas I'm just not
> sure how to enter the original numbers (A1) or how to distinguish between
> the
> two in the final formula (A3)
>
> Using Excel 2003. Hope this makes sense. Thanks.

From: Bernard Liengme on 19 Mar 2010 15:43
Since we are multiplying by A2, the double negation is not needed to coerce
the text to number.
best wishes
Bernard

"Gary''s Student" <GarysStudent(a)discussions.microsoft.com> wrote in message
news:8A7C5380-704D-4F82-9DD2-86D0FBB20538(a)microsoft.com...
> Try this:
>
> =A2*(--LEFT(A1,2)) & " - " & A2*(--RIGHT(A1,2))
> --
> Gary''s Student - gsnu201001
>
>
> "Melody" wrote:
>
>> Hi,
>>
>> Not sure this is possible but...I have a cell that has a number range in
>> it
>> and based on an amount in another cell want to calculate a new range.
>> For
>> example:
>>
>> Initial Range: A1 = 10 - 12
>> Calc Amount: A2 = 5
>> Final Range: A3 = 50 - 60
>>
>> I think I can get the results by concatenating two formulas I'm just not
>> sure how to enter the original numbers (A1) or how to distinguish between
>> the
>> two in the final formula (A3)
>>
>> Using Excel 2003. Hope this makes sense. Thanks.

 |  Next  |  Last
Pages: 1 2
Prev: what is the maximum number of rows excel for mac 2008 has?
Next: Sumif does not calculate beyond 1000 rows