Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add multiple numbers in one cell with those numbers remaining visi
I'm trying to perform a function where I input multiple numbers into one cell
and have their sum appear in an adjacent cell. I will need to do this repeatedly through many rows. Can someone please advise?! Many thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add multiple numbers in one cell with those numbers remaining visi
Pls restate your question with detail.
numbers separated by , or one number at a time to show a running total. -- Don Guillett Microsoft MVP Excel SalesAid Software "TD" wrote in message ... I'm trying to perform a function where I input multiple numbers into one cell and have their sum appear in an adjacent cell. I will need to do this repeatedly through many rows. Can someone please advise?! Many thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add multiple numbers in one cell with those numbers remainingvisi
TD wrote...
I'm trying to perform a function where I input multiple numbers into one cell and have their sum appear in an adjacent cell. I will need to do this repeatedly through many rows. Can someone please advise?! Many thanks. Meaning cell C5 could contain a string like 1 2 4 17 -5 2 0 -7 23 and you want a formula in cell D5 returning the sum of the numeric substrings, so 37, easiest to define a name like seq referring to the formula =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1)) and use it in the following array formula for cell D5. D5 [array formula]: =SUM(IF(MID(" "&TRIM(C5),seq,1)=" ", --MID(TRIM(C5),seq,FIND(" ",TRIM(C5)&" ",seq)-seq))) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add multiple numbers in one cell with those numbers remaining visi
Assuming Harlan's guess is correct (your cells could contain a string like 1
2 4 17 -5 2 0 -7 23 which you want to total up to get 37 as the answer)... if you are able to use VB code (I say able because you may have to adjust your security settings in order to be able to use VB code), there is a simple User Defined Function (UDF) that can be implemented to do this. Press Alt+F11 to get into the Visual Basic Editor, click Insert/Module on its menu bar and then copy/paste the following into the code window that opened up... Function SumCell(R As Range) As Double SumCell = Evaluate(Replace(R.Value, " ", "+")) End Function Now, go back to a worksheet, put a space-delimited set of numbers (say 1 2 4 17 -5 2 0 -7 23) into a cell (say, C5), and put this in the cell you want the sum to appear in... =SumCell(C5) and the formula should evaluate to 37. You can now use this formula like any other putting the cell you want to sum between the parentheses. -- Rick (MVP - Excel) "TD" wrote in message ... I'm trying to perform a function where I input multiple numbers into one cell and have their sum appear in an adjacent cell. I will need to do this repeatedly through many rows. Can someone please advise?! Many thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add multiple numbers in one cell with those numbers remainingvisi
"Rick Rothstein" wrote...
Assuming Harlan's guess is correct (your cells could contain a string like 1 2 4 17 -5 2 0 -7 23 which you want to total up to get 37 as the answer)... if you are able to use VB code . . . .... If security isn't an issue, and the OP is (and/or his/her users are) running Excel 2002 or later versions, then the OP could just use a defined name calling an XLM function. If the string of numeric substrings were in cell C5 and the sum of those substrings were supposed to be in cell D5, with cell D5 the active cell, define the name SUMNSS as the formula =EVAL(SUBSTITUTE(TRIM(C5)," ","+")) then enter the formula =SUMNSS in cell D5. This particular approach won't trigger macro security warnings when opening the workbook. If you're going to use VBA udfs, better to make the udfs general rather than overly particular. In this case, a simple VBA wrapper around the Excel OM's Evaluate would be the better approach. Function evaludf(s As String): evaludf = Evaluate(s): End Function Then try the formula =evaludf(SUBSTITUTE(TRIM(C5)," ","+")) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add multiple numbers in one cell with those numbers remaining visi
Small modification to my UDF (to account for the possibility of trailing
spaces in the series of numbers)... Function SumCell(R As Range) As Double SumCell = Evaluate(Replace(Trim(R.Value), " ", "+")) End Function -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Assuming Harlan's guess is correct (your cells could contain a string like 1 2 4 17 -5 2 0 -7 23 which you want to total up to get 37 as the answer)... if you are able to use VB code (I say able because you may have to adjust your security settings in order to be able to use VB code), there is a simple User Defined Function (UDF) that can be implemented to do this. Press Alt+F11 to get into the Visual Basic Editor, click Insert/Module on its menu bar and then copy/paste the following into the code window that opened up... Function SumCell(R As Range) As Double SumCell = Evaluate(Replace(R.Value, " ", "+")) End Function Now, go back to a worksheet, put a space-delimited set of numbers (say 1 2 4 17 -5 2 0 -7 23) into a cell (say, C5), and put this in the cell you want the sum to appear in... =SumCell(C5) and the formula should evaluate to 37. You can now use this formula like any other putting the cell you want to sum between the parentheses. -- Rick (MVP - Excel) "TD" wrote in message ... I'm trying to perform a function where I input multiple numbers into one cell and have their sum appear in an adjacent cell. I will need to do this repeatedly through many rows. Can someone please advise?! Many thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add multiple numbers in one cell with those numbers remaining visi
If you're going to use VBA udfs, better to make the udfs general
rather than overly particular. In this case, a simple VBA wrapper around the Excel OM's Evaluate would be the better approach. Function evaludf(s As String): evaludf = Evaluate(s): End Function Then try the formula =evaludf(SUBSTITUTE(TRIM(C5)," ","+")) We can generalize my UDF and save the user from having to remember to pass that combination of worksheet functions as an argument... Function Calc(R As Range, Optional Operator As String = "+") As Double Calc = Evaluate(Replace(WorksheetFunction.Trim(R.Value), " ", Operator)) End Function where the user can now include the operator symbol to place between the numbers in his/her series. So, for the general reader out there, the UDF can be used to, say, multiply the values in the string with this function call... =Calc(A1,"*") Just pass the operator symbol into the second argument. Note that the second argument is optional and, if omitted, defaults to summation, so the OP's originally requested summation would be performed by doing either this... =Calc(A1,"+") or, more simply, this... =Calc(A1) -- Rick (MVP - Excel) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
add multiple numbers in one cell with those numbers remainingvisi
"Rick Rothstein" wrote...
.... We can generalize my UDF and save the user from having to remember to pass that combination of worksheet functions as an argument... Function Calc(R As Range, Optional Operator As String = "+") As Double * Calc = Evaluate(Replace(WorksheetFunction.Trim(R.Value), " ", Operator)) End Function .... And in the off chance one wants the intersection of 2 ranges, one would have to enter something like =Calc("X:X 99:99","") so a useless use of Replace in the udf. More critically, why would you make the 1st arg a range rather than a string?! To reduce its useful problem domain? You seem to have an innate, subconscious resistance towards generality. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting specific numbers from a cell containing multiple numbers | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
can a single cell contain multiple numbers? | Excel Worksheet Functions | |||
Can you add multiple numbers in the same cell%3f | Excel Worksheet Functions | |||
Counting multiple numbers in one cell | Excel Worksheet Functions |