Two formulas in on cell based on two numbers in another cell?

  • Thread starter Thread starter Melody
  • Start date Start date
M

Melody

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.
 
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
 
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
 
Since we are multiplying by A2, the double negation is not needed to coerce
the text to number.
best wishes
Bernard
 
Perfect. Thanks. I wasn't sure if I put in '10 - 12 in the cell if it would
still calculate the formula but it worked!
 
Back
Top