Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
"" does not seem to give a Blank cell | Excel Discussion (Misc queries) | |||
OK. I give. Why can't I use the "@" (at sign) in a text cell? | Excel Discussion (Misc queries) | |||
I want to place a "x" in a cell and give it a value of 1 (Excel) | Excel Worksheet Functions | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |