Add cell "50+55+50+50" to give answer 205
 

I have a cell where I need to keep the amounts separate to show workings
out, but I need this to be then added up in a sum, is there a way to do this
as at present I have to manual work this out and enter in another cell toget
the answer and this leaves too much room for error.

Add cell "50+55+50+50" to give answer 205
 

maybe

=50+55+50+50

that'll leave the individual amounts visible to anybody who selects the
cell, but will still show the total in the cell itself

"shyboy2k" wrote:

I have a cell where I need to keep the amounts separate to show workings
out, but I need this to be then added up in a sum, is there a way to do this
as at present I have to manual work this out and enter in another cell toget
the answer and this leaves too much room for error.

Add cell "50+55+50+50" to give answer 205
 

One cell only? Select that cell, put an equal sign in front of it in the
Formula Bar and then press return.

Rick


"shyboy2k" wrote in message
...
I have a cell where I need to keep the amounts separate to show workings
out, but I need this to be then added up in a sum, is there a way to do
this
as at present I have to manual work this out and enter in another cell
toget
the answer and this leaves too much room for error.

Add cell "50+55+50+50" to give answer 205
 

On Aug 28, 12:19 pm, shyboy2k
wrote:
I have a cell where I need to keep the amounts separate to show workings
out, but I need this to be then added up in a sum, is there a way to do this
as at present I have to manual work this out and enter in another cell toget
the answer and this leaves too much room for error.

Generally, you put the amounts in different cells and sum them
elsewhere, and that's how people get to see both the list of
individual numbers and the total.

If you always have exactly 4 values to add up, a repetitive series of
VALUE/LEFT & VALUE/MID functions can be used to create a sum from the
text. But I don't think it would be easy to do this.

VBA can, or UDFs written in VBA. I use morefunc, which can be
downloaded from download.com, and it has a function called EVAL that
can do this very easily.

Another alternative is to use the EVALUATE function. But it is tricky
to do so, since you can't actually use EVALUATE in a cell. (go ahead,
try) Try this: Define a Name with this formula:
=EVALUATE(INDIRECT("RC[-1]",FALSE))
Let's call it Special_Sum. Enter the following in these cells:
A1: 50+55+50+50
B1: =Special_Sum

This works if the Sum will always be 1 cell to the right of the
spelled-out text. If it will be somewhere else, you'll have to modify
the RC[-1] reference.

Add cell "50+55+50+50" to give answer 205
 

Hi,

Here is a slightly different thought:

In Excel you can press Ctrl+` this is a TOGGLE of the Formula/Value views.
The ` is usually located on the keyboard to the left of the "1" key on the
top row, it is not the apostrophy key '.

--
Cheers,
Shane Devenshire


"shyboy2k" wrote:

I have a cell where I need to keep the amounts separate to show workings
out, but I need this to be then added up in a sum, is there a way to do this
as at present I have to manual work this out and enter in another cell toget
the answer and this leaves too much room for error.

Add cell "50+55+50+50" to give answer 205
 

I've seen this request before and thought it was an interesting idea.

Let's say you have the following typed into A1:

4+5

Now you'd like to have the result automatically displayed in another cell.
The result for this equation, of course, is 9. However, if we don't know
what the equation will be, yet we still want the result automatically
displayed in another cell, then well need to use a UDF (User Defined
Function).
This is done through VBA.
-Press Alt+F11 to open the VB Editor.
Past the following code into a Module.

Function RetVal(Target As Range) As Variant
Dim str As String
'Target must be a single cell
If Target.Count 1 Then
RetVal = vbNullString
Exit Function
End If
'This section removes double quotes, if any.
str$ = Replace(Target.Value, """", vbNullString)
'Evaluate the cell entry.
RetVal = Evaluate("=" & str$)
End Function

Now you can use the following formula in any cell to return the desired
calculation of the equation entered in A1.

=RetVal(A1)


HTH,
Paul

--

"shyboy2k" wrote in message
...
I have a cell where I need to keep the amounts separate to show workings
out, but I need this to be then added up in a sum, is there a way to do
this
as at present I have to manual work this out and enter in another cell
toget
the answer and this leaves too much room for error.

Add cell "50+55+50+50" to give answer 205
 

Hi shyboy,

Spiky touched on the old EVALUATE macro function above.
This site has a bit more info on it.
http://www.ozgrid.com/News/excel-eva...ormula-VBA.htm
Scroll down to Excel tips and tricks.

HTH
Martin


"shyboy2k" wrote in message
...
I have a cell where I need to keep the amounts separate to show workings
out, but I need this to be then added up in a sum, is there a way to do
this
as at present I have to manual work this out and enter in another cell
toget
the answer and this leaves too much room for error.

Add cell "50+55+50+50" to give answer 205
 

Hello Paul,

This worked a treat once I allowed the MACRO to work on the spread sheet,
thnx 4 ur help

shyboy2k

"PCLIVE" wrote:

I've seen this request before and thought it was an interesting idea.

Let's say you have the following typed into A1:

4+5

Now you'd like to have the result automatically displayed in another cell.
The result for this equation, of course, is 9. However, if we don't know
what the equation will be, yet we still want the result automatically
displayed in another cell, then well need to use a UDF (User Defined
Function).
This is done through VBA.
-Press Alt+F11 to open the VB Editor.
Past the following code into a Module.

Function RetVal(Target As Range) As Variant
Dim str As String
'Target must be a single cell
If Target.Count 1 Then
RetVal = vbNullString
Exit Function
End If
'This section removes double quotes, if any.
str$ = Replace(Target.Value, """", vbNullString)
'Evaluate the cell entry.
RetVal = Evaluate("=" & str$)
End Function

Now you can use the following formula in any cell to return the desired
calculation of the equation entered in A1.

=RetVal(A1)


HTH,
Paul

--

"shyboy2k" wrote in message
...
I have a cell where I need to keep the amounts separate to show workings
out, but I need this to be then added up in a sum, is there a way to do
this
as at present I have to manual work this out and enter in another cell
toget
the answer and this leaves too much room for error.

Add cell "50+55+50+50" to give answer 205
 

Thanks MartinW

on your post I will look at this link for future reference on Macro extra
for the when I need one again...

Shyboy2k

"MartinW" wrote:

Hi shyboy,

Spiky touched on the old EVALUATE macro function above.
This site has a bit more info on it.
http://www.ozgrid.com/News/excel-eva...ormula-VBA.htm
Scroll down to Excel tips and tricks.

HTH
Martin


"shyboy2k" wrote in message
...
I have a cell where I need to keep the amounts separate to show workings
out, but I need this to be then added up in a sum, is there a way to do
this
as at present I have to manual work this out and enter in another cell
toget
the answer and this leaves too much room for error.

Add cell "50+55+50+50" to give answer 205
 

You're welcome. I credit Tom Hutchins for the code. It may or may not be
his...but I saw it in one of his posts.

Great stuff.

--

"shyboy2k" wrote in message
...
Hello Paul,

This worked a treat once I allowed the MACRO to work on the spread sheet,
thnx 4 ur help

shyboy2k

"PCLIVE" wrote:

I've seen this request before and thought it was an interesting idea.

Let's say you have the following typed into A1:

4+5

Now you'd like to have the result automatically displayed in another
cell.
The result for this equation, of course, is 9. However, if we don't know
what the equation will be, yet we still want the result automatically
displayed in another cell, then well need to use a UDF (User Defined
Function).
This is done through VBA.
-Press Alt+F11 to open the VB Editor.
Past the following code into a Module.

Function RetVal(Target As Range) As Variant
Dim str As String
'Target must be a single cell
If Target.Count 1 Then
RetVal = vbNullString
Exit Function
End If
'This section removes double quotes, if any.
str$ = Replace(Target.Value, """", vbNullString)
'Evaluate the cell entry.
RetVal = Evaluate("=" & str$)
End Function

Now you can use the following formula in any cell to return the desired
calculation of the equation entered in A1.

=RetVal(A1)


HTH,
Paul

--

"shyboy2k" wrote in message
...
I have a cell where I need to keep the amounts separate to show
workings
out, but I need this to be then added up in a sum, is there a way to do
this
as at present I have to manual work this out and enter in another cell
toget
the answer and this leaves too much room for error.