CODEWITHSUNDEEP
excelexcel-formula
Shanible
Shanible

Reputation: 3

Formula for subtracting number range in one cell from a number in another cell

I have:

What I'd like is a formula to subtract each the left and right numbers (X, Y) from the solo number to produce another range.

Example: 

Range cell        "5-9"  
Solo number cell  "2000"
Result wanted     "1995-1991"

How can I produce this?

I am currently using a formula that requires me to manually enter the numbers on either side of the dash. Example (notice 5 and 9 are hard-coded):

=SUM(A31,-5)&"-"&SUM(A31,-9)

Upvotes: 0

Views: 1209

Answers (3)

ergonaut
ergonaut

Reputation: 7057

Say A1=2000 And B1="5-9"

You can use a combination of FIND, LEFT/RIGHT, and INT functions:

C1 =(A1-INT(LEFT(B1,FIND("-",B1)-1))&"-"&(A1-INT(RIGHT(B1,FIND("-",B1)-1))))

This will work with any range like "100-135"

Upvotes: 2

Excel Hero
Excel Hero

Reputation: 14764

If A1 is 2000 and B1 is 5-9:

=A1-MID(B1,1,FIND("-",B1)-1)&"-"&A1-MID(B1,FIND("-",B1)+1,99)

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

Assuming your range numbers will be 0-9, you can use this:

=SUM(A31,-LEFT(B31,1))&"-"&SUM(A31,-RIGHT(B31,1))

(assuming the range number is in column B)

edit: @ergonaut has a better solution that will use any amount of digits.

Upvotes: 0

Related Questions