1 to 10 - Need to sum the number from 1 to 10

[Sakthikumar Chinnasamy]

Is there any formula to sum the numbers from first digit to last digit. Eg. if cell "A1" contain "1 to 10", then i need to sum the value from 1 to 10 and output to be display as 55. Also, i want to find the average of the same,

 

[Naresh Yadav]

Hi,

Pls see attached file
this formula will produce the result up to only 2 digit number

regards
Naresh

 

[bosco_yip]

In B1, copied down :

=SUM(MMULT(ROW(INDIRECT(SUBSTITUTE(A1," to ",":"))),1))

Regards
Bosco

 

[Khalid NGO]

Hi Naresh,

Replace your LEFT & RIGHT formula with SUBSTITUTE like Bosco did:

=SUM(INDEX(ROW(INDIRECT(SUBSTITUTE(A1," to ",":"))),))

Regards,

 

[Naresh Yadav]

OK, and thanks Khalid sir for giving attention towards me..

 

[Deepak]

Mathematical formula for the SUM

((upper+1-lower)*(upper+lower))/2

 

[shrivallabha]

Mathematical formula for the SUM

((upper+1-lower)*(upper+lower))/2

Loose translation to excel would be :
=(MID(A1,SEARCH("to ",A1,1)+3,99)+1-LEFT(A1,FIND(" ",A1)-1))*(MID(A1,SEARCH("to ",A1,1)+3,99)+LEFT(A1,FIND(" ",A1)-1))/2