Returning a range from an inner function

I have a range of data, which has had the DOLLAR() function performed on it. 
I would like to get the total of this range using the SUM() function. doing 
SUM(F1:F4) will not work, so I need to convert the data into values that 
will work. The VALUE() function is perfect for converting the data into 
values that will work for SUM(), but the formula SUM(VALUE(F1:F4)) does not 
work because VALUE() will not accept a range as a parameter (and also does 
not return a range). I need to perform the VALUE() function on each cell 
before submitting it to SUM(). There must be some way to do this, 
considering how much people display totals of dollar values, and considering 
that SUM() is probably the most used function in Excel, people must need to 
convert data from it's natural form sometimes, right? Any help would be 
appreciated. Thanks.
-- 
Nathan Sokalski
njsokalski@hotmail.com
http://www.nathansokalski.com/ 


0
njsokalski (32)
8/11/2007 3:23:23 AM
excel 39879 articles. 2 followers. Follow

6 Replies
1183 Views

Similar Articles

[PageSpeed] 53

Try, array-entered*:
=SUM(--F1:F4)

*Press CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing 
ENTER

The "--" will coerce the text values to numbers
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"Nathan Sokalski" <njsokalski@hotmail.com> wrote in message 
news:%238Bn4b82HHA.3916@TK2MSFTNGP02.phx.gbl...
>I have a range of data, which has had the DOLLAR() function performed on 
>it. I would like to get the total of this range using the SUM() function. 
>doing SUM(F1:F4) will not work, so I need to convert the data into values 
>that will work. The VALUE() function is perfect for converting the data 
>into values that will work for SUM(), but the formula SUM(VALUE(F1:F4)) 
>does not work because VALUE() will not accept a range as a parameter (and 
>also does not return a range). I need to perform the VALUE() function on 
>each cell before submitting it to SUM(). There must be some way to do this, 
>considering how much people display totals of dollar values, and 
>considering that SUM() is probably the most used function in Excel, people 
>must need to convert data from it's natural form sometimes, right? Any help 
>would be appreciated. Thanks.
> -- 
> Nathan Sokalski
> njsokalski@hotmail.com
> http://www.nathansokalski.com/
> 


0
demechanik (4694)
8/11/2007 3:54:18 AM
Also the OP's original formula =SUM(VALUE(F1:F4)) would work array entered



-- 

Regards,

Peo Sjoblom

"Max" <demechanik@yahoo.com> wrote in message 
news:eDbdSt82HHA.4184@TK2MSFTNGP06.phx.gbl...
> Try, array-entered*:
> =SUM(--F1:F4)
>
> *Press CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing 
> ENTER
>
> The "--" will coerce the text values to numbers
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> --- 
> "Nathan Sokalski" <njsokalski@hotmail.com> wrote in message 
> news:%238Bn4b82HHA.3916@TK2MSFTNGP02.phx.gbl...
>>I have a range of data, which has had the DOLLAR() function performed on 
>>it. I would like to get the total of this range using the SUM() function. 
>>doing SUM(F1:F4) will not work, so I need to convert the data into values 
>>that will work. The VALUE() function is perfect for converting the data 
>>into values that will work for SUM(), but the formula SUM(VALUE(F1:F4)) 
>>does not work because VALUE() will not accept a range as a parameter (and 
>>also does not return a range). I need to perform the VALUE() function on 
>>each cell before submitting it to SUM(). There must be some way to do 
>>this, considering how much people display totals of dollar values, and 
>>considering that SUM() is probably the most used function in Excel, people 
>>must need to convert data from it's natural form sometimes, right? Any 
>>help would be appreciated. Thanks.
>> -- 
>> Nathan Sokalski
>> njsokalski@hotmail.com
>> http://www.nathansokalski.com/
>>
>
> 


0
terre081 (3244)
8/11/2007 5:15:43 AM
Another one:

Normally entered:

=SUMPRODUCT(F1:F4+0)

-- 
Biff
Microsoft Excel MVP


"Nathan Sokalski" <njsokalski@hotmail.com> wrote in message 
news:%238Bn4b82HHA.3916@TK2MSFTNGP02.phx.gbl...
>I have a range of data, which has had the DOLLAR() function performed on 
>it. I would like to get the total of this range using the SUM() function. 
>doing SUM(F1:F4) will not work, so I need to convert the data into values 
>that will work. The VALUE() function is perfect for converting the data 
>into values that will work for SUM(), but the formula SUM(VALUE(F1:F4)) 
>does not work because VALUE() will not accept a range as a parameter (and 
>also does not return a range). I need to perform the VALUE() function on 
>each cell before submitting it to SUM(). There must be some way to do this, 
>considering how much people display totals of dollar values, and 
>considering that SUM() is probably the most used function in Excel, people 
>must need to convert data from it's natural form sometimes, right? Any help 
>would be appreciated. Thanks.
> -- 
> Nathan Sokalski
> njsokalski@hotmail.com
> http://www.nathansokalski.com/
> 


0
biffinpitt (3172)
8/11/2007 5:22:44 AM
"Peo Sjoblom" <terre08@mvps.org> wrote in message 
news:%23qoi5a92HHA.4184@TK2MSFTNGP06.phx.gbl...
> Also the OP's original formula =SUM(VALUE(F1:F4)) would work array entered

granted .. but only at the cost of another 3 keystrokes <g>
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 


0
demechanik (4694)
8/11/2007 11:27:59 PM
Thanks, that works great for this situation, and I appreciate finding a 
solution. But is there a general way to perform a series of functions on the 
value of each cell before submitting those values as a range to the 
outermost function? I would like to note that even though your solution 
works great for this specific scenario, there may be cases where the 
operation is much more complex, and will probably involve other functions. 
For example, you may want to round up each value in a range before 
submitting it, or perform an IF function to use 0 in some cases and the 
original value in others, or who knows what else someone might want. Is it 
really impossible to perform an operation on each cell value before 
performing the outermost function? Thanks.
-- 
Nathan Sokalski
njsokalski@hotmail.com
http://www.nathansokalski.com/

"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:%23WMYne92HHA.536@TK2MSFTNGP06.phx.gbl...
> Another one:
>
> Normally entered:
>
> =SUMPRODUCT(F1:F4+0)
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "Nathan Sokalski" <njsokalski@hotmail.com> wrote in message 
> news:%238Bn4b82HHA.3916@TK2MSFTNGP02.phx.gbl...
>>I have a range of data, which has had the DOLLAR() function performed on 
>>it. I would like to get the total of this range using the SUM() function. 
>>doing SUM(F1:F4) will not work, so I need to convert the data into values 
>>that will work. The VALUE() function is perfect for converting the data 
>>into values that will work for SUM(), but the formula SUM(VALUE(F1:F4)) 
>>does not work because VALUE() will not accept a range as a parameter (and 
>>also does not return a range). I need to perform the VALUE() function on 
>>each cell before submitting it to SUM(). There must be some way to do 
>>this, considering how much people display totals of dollar values, and 
>>considering that SUM() is probably the most used function in Excel, people 
>>must need to convert data from it's natural form sometimes, right? Any 
>>help would be appreciated. Thanks.
>> -- 
>> Nathan Sokalski
>> njsokalski@hotmail.com
>> http://www.nathansokalski.com/
>>
>
> 


0
njsokalski (32)
8/12/2007 1:46:53 AM
Your follow-up is impossible to answer! But I'll say this, you should be 
able to manipulate those values almost any way you want *after* you coerce 
them to numeric numbers. That's what this is doing: F1:F4+0. This has to 
done as an array. You can round them or use them if IF functions but you'd 
need to site specific needs.

-- 
Biff
Microsoft Excel MVP


"Nathan Sokalski" <njsokalski@hotmail.com> wrote in message 
news:OIgGnKI3HHA.3940@TK2MSFTNGP05.phx.gbl...
> Thanks, that works great for this situation, and I appreciate finding a 
> solution. But is there a general way to perform a series of functions on 
> the value of each cell before submitting those values as a range to the 
> outermost function? I would like to note that even though your solution 
> works great for this specific scenario, there may be cases where the 
> operation is much more complex, and will probably involve other functions. 
> For example, you may want to round up each value in a range before 
> submitting it, or perform an IF function to use 0 in some cases and the 
> original value in others, or who knows what else someone might want. Is it 
> really impossible to perform an operation on each cell value before 
> performing the outermost function? Thanks.
> -- 
> Nathan Sokalski
> njsokalski@hotmail.com
> http://www.nathansokalski.com/
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message 
> news:%23WMYne92HHA.536@TK2MSFTNGP06.phx.gbl...
>> Another one:
>>
>> Normally entered:
>>
>> =SUMPRODUCT(F1:F4+0)
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Nathan Sokalski" <njsokalski@hotmail.com> wrote in message 
>> news:%238Bn4b82HHA.3916@TK2MSFTNGP02.phx.gbl...
>>>I have a range of data, which has had the DOLLAR() function performed on 
>>>it. I would like to get the total of this range using the SUM() function. 
>>>doing SUM(F1:F4) will not work, so I need to convert the data into values 
>>>that will work. The VALUE() function is perfect for converting the data 
>>>into values that will work for SUM(), but the formula SUM(VALUE(F1:F4)) 
>>>does not work because VALUE() will not accept a range as a parameter (and 
>>>also does not return a range). I need to perform the VALUE() function on 
>>>each cell before submitting it to SUM(). There must be some way to do 
>>>this, considering how much people display totals of dollar values, and 
>>>considering that SUM() is probably the most used function in Excel, 
>>>people must need to convert data from it's natural form sometimes, right? 
>>>Any help would be appreciated. Thanks.
>>> -- 
>>> Nathan Sokalski
>>> njsokalski@hotmail.com
>>> http://www.nathansokalski.com/
>>>
>>
>>
>
> 


0
biffinpitt (3172)
8/12/2007 2:06:44 AM
Reply:

Similar Artilces:

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

How do I count odd numbers in a range?
Could someone please help me!!! I am trying to count how many odd and even numbers there in a range. I have tried to use the 'countif' , 'iseven' and 'isodd' formulas. However, I have been unsuccessful. Can someone please let me know if this can be done? Here's a formula from an older post by Peo Sjoblom that seems to do what you want: =SUMPRODUCT(--(MOD(A1:A22,2)=1)) Adjust the range as necessary. tj "Rob" wrote: > Could someone please help me!!! > > I am trying to count how many odd and even numbers there in a range. I have > trie...

Error with Copy function
Hi, I'm trying to copy data from workbook A to workbook B. However, whenever I select workbook B, the Paste function is blur, which means I can not paste it. If I create a new workbook , then I can paste the data there. In workbook B, I created a macro to open workbook A. Pls help me how to fix this. Many thanks! ...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Function In A Text Box
Hi, I am trying to put a concatenate function in a text box. The error message is as follows: The text you have entered is not a valid reference or defined name. I want to concatenate some text with a number from the cell, A2. Any ideas would greatly appreciated. Regards Tubbsy -- tubbsy ------------------------------------------------------------------------ tubbsy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24743 View this thread: http://www.excelforum.com/showthread.php?threadid=388332 Its not a perfect solution but you could perform the concat...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

Move cell info and info in range of cells on new entry
I would like to enter info into exsiting cell and when I hit enter the existing info would move down one row. B C D E 5 Enter new info her existing info moves down 6 7 Thanks for the help. -- JoAnn You could use a worksheet_change event macro within your sheet module to do this when you enter a value into the last column of the existing row. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "abc" <abc@discussions.microsoft.com> wrote in message news:8378874C-D0D0-4E73-98F6-F48E61B60898@microsoft.com......

Re: How can I stop Outlook to run subsequent rules from VBA function bound to currently running rule
And I thought to use Rule's olRuleActionStop in VBA. . Submitted using http://www.outlookforums.com ...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

Returning the Beginning of the week
Returning the beginning of the week. e.g. if the date == 02/03/04 (Friday 2nd March 2004) the beginning week for that date == 01/03/04 (Thursday 1st March 2004) Hows does one go about achieving this? Regards -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=314968 This formula will take a date in cell A1 and give the start of the wee in B1 * assumes that monday is the start of the week, so i...

return
Capability of the system to automatically change the status of the PO to change order, and add line items that have been returned. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...

Return
When returning an item, can you return the item by selecting the matrix rather than the individual item and changing the quantity to -1? Hi KIm, Normally this won't work as it works in sale where for each matrix type you can put the qunatites. HOWEVER when you select the matrix item it bring the matrix window but allow only one item at a time to be returned and make the -1 qty automatically in return document type. It work but one by one. Regards Akber "Kim" wrote: > When returning an item, can you return the item by selecting the matrix > rather than the individua...

Returning Multiple Values in a Single Cell
Hello, I posted this same question a couple of weeks ago but forgot to mention a key point. My question was as follows: I have read a few posts regarding this subject but am still somewhat lost. I have a two sheet workbook (Excel 2000). On the first sheet is a list of projects that my department will complete during the year. A description of each project is given, a start date, an end date, as well as which crew will be completing the work (Crew 1 or Crew 2). What I am trying to do on the second sheet is to be able to type in any date, and have the description of the project on that da...

Partition function with time-type arguments
I cant' cope with mayby a simple question of how to use Partition function with time-type arguments. I would like to group data based on [timeCol] column which shows time detailed down to the second 'hh:mm:ss' using partition(col,start,end,interval) function for groups with time from 9 AM to 5 PM (17:00) every 30 minutes. I entered something like this to the project grid in A2000: new filed name: Partition([timeCol];#09:00:00#;#17:00:00#;minutes(30)) or new filed name: Partition([timeCol];#09:00:00#;#17:00:00#;#00:30:00#) or new filed name: Partition([timeCol];#09:00:00#;#17:00:00...

Returns immediate function
Hi all, I know that the function CeRapiInitEx returns immediately. No wait for processing. I want to implement such a function. Returns immediate. no wait. How do I implement such a function? Thanks Ko You might create a UI thread that do the work, launch it in the "non-wait" function and return immediatly to the caller. When the thread done it's works, you can post an application message to return the results.... Greetings, Gaetano Sferra "PPC DEV" <ppc_dev@yahoo.com> ha scritto nel messaggio news:033101c3b388$a7098910$a301280a@phx.gbl... > Hi all...

Using a named range in a Sumproduct comparison
Hi, Lets suppose I have in a worksheet of 500 rows of data with 4 columns. I want to apply sumproduct for determining count with certain conditions. Now one of the columns (column A) have data such that it is always only one of the following 6 possible values. "UK", "US", "IN", "AU", "FI" and "PA Now one of the conditions in the sumproduct is that column A has to be "UK", "AU" or "FIN". For one other column the (Column B) there are 4 possible conditions like 56, 78, 89 or 44. Now the next condition for ...

Sort function
Hi, I have a table which requires sorting due to changes in values everymonth. For eg.. We have 6 clients. The growth % of these clients changes from month to month. These are automatically picked from the system. Through a formula, I would like it sorted automatically in a decending order. Client %growth A 20% B -70% C 80% D 0 E -40% F 50% Output through some formula next to the list for the above range. C 80% F 50% A 20% D 0 E -40% B -70% I know that the same is possible through a macro, however I would prefe...

Lookup Values, return multiple.
I need help please. The formula I require has to in Worksheet A :- [B1] lookup DATA in Worksheet A [A1] SP001 in worksheet B [A] [B] [C] SP001 PL001 16 SP001 PL002 5 SP001 DR001 10 CR001 PL001 2 Search for all Text String starting with "PL" in Worksheet B [B] only for SP001 in [A] and return with values from Worksheet B [C] 16 and 5 I need the values to be seperated and not summed. Tx. Appreciate assistance. Try this... In the formulas: Rng1 refers to worksheet B $A$2:$A$5 Rng2 refers ...

% Return Line graph?
Hi- Can someone show me how to customize a graph in Money 2005 to show my stock portfolio's % change over time? I just want a simple line chart showing my percentage change since the beginning - as a way to just figure out relative performance but I can't seem to find the right graph to customize. I can't just chart the value of the portfolio as I have been adding money to it over time which has drastically changed the % returns. I am not sure if what I want is possible or if this is clear but I also have the plugin for MS Excel that I can import money data so if there is...

kit item return
There should be an option in the Store Op Manager --> Configuration --> Option Tab to allow the ability when returning a Kit Item to either return the quantity back to the Kit Items Inventory or to the Kit Item COMPONENTS Inventory. Effectively breaking out the Kit Item automatically when the item is returned to inventory. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to op...

VBA Functions
I am using Publisher 2002 to print membership cards with barcodes. I have successfully created the file in Publisher and the mail merge works beautifully. In order to print the barcode properly I need to use a VBA function (supplied by the barcode vendor) I am able to insert the functions in Excel with no problem (=function name (cell)). Is it possible to do that directly in Publisher on the merged field? If so how? I have been looking and I have not found the magic answer. Thanks A small child turns to Ed, and exclaims: "Look! Look! A post from Louise <anonymous@discussi...

Last cell in row range with a result
I need to see if anyone can tell me if it's possible to find the last cell in a range that has a result and not just a formulas. We have this file that has a row of formulas as each cell it a different day of the month. In another file I need to pull the current cell and at this time I have to change the cell reference. Please let me know, thanks if you have a row of data , say row 3, with no included blanks, then the last value in that row is: =INDEX($3:$3,COUNTA($3:$3)) -- Gary''s Student - gsnu200713 Gary''s Student wrote: > if you have a row of data , say ...

Formula that returns the sheetname
Does anyone know of a formula that would return the active sheetname? http://www.mcgimpsey.com/excel/formulae/cell_function.html "TimT" wrote: > Does anyone know of a formula that would return the active sheetname? Great Link! The formula was =MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255) ....yeah, like I was gonna figure that one out on my own. "David Hepner" wrote: > http://www.mcgimpsey.com/excel/formulae/cell_function.html > > > "TimT" wrote: > > > Does anyone know of a form...

Function to Return the IP Address of a known Host
Hi People, This function returns the IP Address of a known host. I hope you find it useful. Function xlGetIPAddress(strHost As String) 'Ping a host to see if it is alive Dim objPing Dim objRetStatus Dim strMessage If strHost = "" Then xlGetIPAddress = False Else Set objPing = GetObject("winmgmts: {impersonationLevel=impersonate}").ExecQuery("select * from Win32_PingStatus where address = '" & strHost & "'") For Each objRetStatus In objPing If IsNull(objRetStatus.StatusCode) Or objRet...