help with the syntax for using range name in a formula?

I have --------=3D+AVERAGE(SMALL(B7:B19,{1,2,3}))-------------.  I want
to replace B7:B19 with =93Myrange=94 but I can=92t seem to get the syntax
right. excel says error in formula. Thks in advance for any help BRC
0
BRC
1/9/2010 5:59:14 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
960 Views

Similar Articles

[PageSpeed] 28

Hi

=AVERAGE(SMALL(Myrange,{1,2,3}))

have you created Myrange?
have you spelt it correctly?
-- 
Regards
Roger Govier

"BRC" <brc1051-googrps@yahoo.com> wrote in message 
news:d0e09817-22dc-4c36-b60d-67a397067441@h9g2000yqa.googlegroups.com...
> I have --------=+AVERAGE(SMALL(B7:B19,{1,2,3}))-------------.  I want
> to replace B7:B19 with �Myrange� but I can�t seem to get the syntax
> right. excel says error in formula. Thks in advance for any help BRC
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 4756 (20100109) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

__________ Information from ESET Smart Security, version of virus signature database 4756 (20100109) __________

The message was checked by ESET Smart Security.

http://www.eset.com



0
Roger
1/9/2010 6:06:15 PM
On Jan 9, 10:06=A0am, "Roger Govier"
<roger@technology4unospamdotcodotuk> wrote:
> Hi
>
> =3DAVERAGE(SMALL(Myrange,{1,2,3}))
>
> have you created Myrange?
> have you spelt it correctly?
> --
> Regards
> Roger Govier
>
> "BRC" <brc1051-goog...@yahoo.com> wrote in message
>
> news:d0e09817-22dc-4c36-b60d-67a397067441@h9g2000yqa.googlegroups.com...
>
> > I have --------=3D+AVERAGE(SMALL(B7:B19,{1,2,3}))-------------. =A0I wa=
nt
> > to replace B7:B19 with Myrange but I can t seem to get the syntax
> > right. excel says error in formula. Thks in advance for any help BRC
>
> > __________ Information from ESET Smart Security, version of virus
> > signature database 4756 (20100109) __________
>
> > The message was checked by ESET Smart Security.
>
> >http://www.eset.com
>
> __________ Information from ESET Smart Security, version of virus signatu=
re database 4756 (20100109) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com

Thank you Roger, I thought range name had to be in quotes and needed
something like...... range.("myrange")...Thk again BRC
0
BRC
1/9/2010 6:16:52 PM
Reply:

Similar Artilces:

link custom field to resource names
hello, i have created a custom field "Primary contact" is there a way to link it to the list of Resource names? maybe turn it into a drop down list? thanks "greg" <greg@nospam.com> wrote in message news:%23hSQ6HJsEHA.3396@tk2msftngp13.phx.gbl... > hello, > i have created a custom field "Primary contact" > is there a way to link it to the list of Resource names? > maybe turn it into a drop down list? > > thanks > > ...

Display Field Name as Text Listing if Value True from Checkbox Fields
Hi everyone! I have 40 or so columns in a table using the yes/no data type. Each record in the table represents an audit of a paper form. Those items filled incorrectly or incompletely on the paper form receive a check on an access form during the audit process. I would like to create a feedback form based on the audit record that displays a listing of fields that received the check boxes, or rather, were incomplete or incorrect on paper form that was audited. On the feedback form, I only want to display something like this: "Your form had the following errors: ErrorFieldOneName Erro...

I'm having a vacant headed momment.-- variable in a range?
I need to search a large table- the size of the table can change- I can't for the life of me think of the command to make a variable part of an address the current data range is about 400 items, the ultimate data range will hit about 6000 items. (I have 8760 vlookup statements processing... on one worksheet) so rather than my Vlookup searching a1:g6000 every time, (because that would be checking an extra 4,9056,000 cells needlesly while my data set is small) I've counted the # of rows in A-- and I'd like my vlookup to be looking in a1:gVARIABLE where variable is the curre...

Using a Total from a Sub Report
I have a report for Orders, and a subreport for each Order for Invoices. The subreport shows the total amount for Invoices against each Order. The Orders are grouped by Area. On the Area header/footer I have an Order total. I now want to show the total of ALL Invoices by the Order total. Please can someone tell me as simply as possible how to do this? For ease I will call my main report ORDERS and my subreport INVOICES. Any help is greatly appreciated - I have searched through relevant questions but have not found an expression that works! I would create a totals query t...

Formula result shows as zero Excel 2003
Formula as shown =IF(C12<>"",+C11-C12,"") appears in D12. This is the same formula as rows above but rows above show correct result whereas D12 and subsequent rows show only zero. If I do an F2 and F9, the correct result shows in the Formula Editing bar so formula is working correctly. I have tried copying both formula and cell formatting from previous rows which do display their result correctly but still doesn't fix the problem. Any assistance would be much appreciated Hi maybe automatic calculation is disabled. Check 'Tools - Options - Calculate...

Help with DLookup
I have a DLookup function on my form and it works by finding the information once I exit and then re-enter the form, it does not bring in the information as soon as I enter the information in the lookup field. =DLookUp("[Employee Name]","[Employee Information]","[Employee ID]=forms![Timesheet Entry]![Employee ID]") Does anybody know why it is not updating immediately? All help is greatly appreciated Where are you using this expression -- as the control source of a textbox? More details about the form's design and how you're using the form, please....

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...

Fire a Workflow using a Callout
Hi, I would like to fire a WorkFlow using a callout. I got problems to do that. Can you tell me how I can do that? I tried it like that: CrmService.ExecuteWFProcessRequest wf = new LetterSalutation.CrmService.ExecuteWFProcessRequest(); wf.ProcessId = GetWorkflowProcessId(workflowName, entityContext); myService.Execute(wf); But I get an error: Server was unable to process request. Can you help me? Simon ...

Use Copied Outlook PST file as default...How?
If I copy a PST file on my PC, how can I configure my Laptop Outlook to use that copied file as its default. Is it possible to copy new Defined Views in Contacts from one computer to another. Dmahanay <anonymous@discussions.microsoft.com> wrote: > If I copy a PST file on my PC, how can I configure my > Laptop Outlook to use that copied file as its default. Outlook version? > Is it possible to copy new Defined Views in Contacts from > one computer to another. I think views are registry items and not kept in the PST. -- Brian Tillman My outlook version is 2002. >--...

how to route messages using the internet and not the VPN c
Hi, we are currently in the process of evaluating exchange 2003 in the past we used 3rd party POP3 mail servers for each office, so each office also had it's own MX record no problem that way..BUT now we implemented AD and one of the exch2k3 server is the US is holding the Primary dns MX record for the company the same MX record that we want everyone else to use ( i.e someone@company.com ) regardless to which office they are located in. when the mail is being intercepted on the primary mail server, it's routing itself via the AD GC's servers internally on the vpn connection, becasu...

Conditional Formating Help
Hi I wonder if anyone could help me, I am after code for the following. cell e6 = Keys Sent Column f6 = Keys due Column g6 = Keys received What I want the script to do is if there is no value in g6 and todays date is greater then the date stated in F6, then the cell turns red (prompt to inform me that keys are late). Many thanks Dan Use a CF formula of =AND(G6="",TODAY()>F6) HTH Bob "housinglad" <housinglad@discussions.microsoft.com> wrote in message news:A5887369-33DA-489A-BEC9-8173707313C6@microsoft.com... > Hi > ...

Tricky Formula.. Please Help
I have created a time roster.. easy in each day I have start,end and break deduction.. still no probs I need to create an output formula for hours worked after a 16:00 from the start and end range on a day. So in the roster it will still display normal hours, however I will create a field for hours after 16:00. this is for calculating a different wage rate. Thanks Aaron See response in .programming -- HTH RP (remove nothere from the email address if mailing direct) "Aaron H" <aaron@istarnetworks.com.au> wrote in message news:uU96kXI$EHA.2076@TK2MSFTNGP15.phx.gbl... ...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

HELP- error Outlook
What's this mean and how to fix? in error dialogue box: "c:\program~1\micro~2 \office\outlook.exe" Abnormal program termination .. ...

help diagnosing what's going on in ExchangeServer2000
I'm fairly ignorant of exactly how ES2000 works and would like some general information to help in my diagnosing why some emails are not being delivered to certain email domains (more correctly, why some emails are not being forwarded actually...ES2K is part of a voicemail system where some users have ..wav copies of the voicemails forwarded to their various off-site email accounts) . Here are my questions: 1. How can I see what emails are being forward? Is there a file/folder akin to the SentItems folder in Outook? 2. If there are send failures, where can I find them and how can ...

Counting of Names ?
I have some names in some cells like " Abraham" " Sathish " etc . I want the number of occurances of each name Counted ,within a range of cells and made known at a perticulat cell Like Name No of Times Abraham 110 Sathish 20 Regards Vijay Hi Vijay! Put the unique names in a column and use (eg) =COUNTIF($A$1:$A$25,D1) If you want a fast way of extracting the list of names see: Chip Pearson http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (...

map data (x-y axis) with diffent series name
I have a set of x-y coordinate data set and i would like to plot them showing the respective series name. Is there an automatic way of doing this without haing to plot the data points separately. eg Name x y A 1 6 B 2 3 C 3 4 D 4 2 E 5 5 .. . . .. . . .. . . I will like to have a plot of x and y with A, B, C as series name without having to plot the points one after the other. Hi, Here are a couple of free addins that allow you to link data labels to cells. Rob Bovey's Char...

OFFSET problems in dynamic range
Hi, I'm having the strangest problem with the OFFSET function. I have 5 dynamic ranges in my worksheet. Aimline =OFFSET('Weekly ORF'!$F$4,1,0,COUNTA('Weekly ORF'$F:$F)-1,1) Sessions =OFFSET ('Weekly ORF'!$D$4,1,0,COUNTA('Weekly ORF'$E:$E)-1,1) WCPM =OFFSET ('Weekly ORF'!$G$4,1,0,COUNTA('Weekly ORF'$G:$G)-1,1) Date =OFFSET ('Weekly ORF'!Aimline,0,-1) Date2 =OFFSET ('Weekly ORF'!WCPM,0,-1) The problem is that the two names for Date and Date2 keep reverting to =OFFSET ('Excel Template.xls'!Aimline,0,-1) and =OFFSET (&#...

how to count if the value falls between a date range
example 1/1/2005 1/2/2005 = 2 2/1/2005 2/2/2005 = 2 I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and similarly 2/1/2005 and 2/3/2005 =countif(a1:a10,">="&date(2005,1,1)) - countif(a1:a10,">"&date(2005,1,3)) (I'm not sure what between means--include the end dates or not???) joe wrote: > > example > 1/1/2005 > 1/2/2005 = 2 > 2/1/2005 > 2/2/2005 = 2 > > I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and > similarly 2/1/2005 and 2/3/2005 -- Dave Peterson Dave, ...

Urgent Question Please Help: Credit Card Setup Problem
We have an urgent question regarding credit card setup/cash receipts/bank rec We understand that in Great Plains when setting up a Credit Card to be used by a Customer, it must be setup as a "Bank Card", not a "Charge Card", in order for the transaction to hit Bank Rec. However, we mistakenly setup all the Credit Cards as "Charge Cards", therefore after posting Credit Card Cash Receipt transactions in Receivables, the transaction does not appear in Bank Rec for us to perform Bank Deposits. Please Please Help!!! Is there any way to "fix" those pos...

Free Quantities Using Extended Pricing
Hi, I would like to know if there is a way to enter a promotion using the Extended Pricing as "buy 5 get 1 free", all what I figured that you can build "buy 1 get 1 free" but my customer case is making it in layers each 5 Units with one free. How can I build that? Thanks in advance, ...

How to execute dos cmds in the prompt opened using VBScript?
Dim shell Dim oExec Set shell = CreateObject("wscript.shell") Set oExec = shell.Exec("cmd.exe") oExec.StdIn.Write("exit") oExec.StdIn.Write VbCrLf Do While oExec.Status = 0 WScript.Sleep 100 Loop WScript.Echo oExec.Status Above code is not executing the exit command in the command prompt opened by shell.Exec("cmd.exe"). Nothing is happening until i close the prompt manually by clicking the close button. How to execute dos commands in the prompt opened by shell.Exec("cmd.exe") ?. [i looked into the msdn library but its not clear...

Using Queries in Excel
What are the best practices for using database-like queries in Excel. Let's say you wish to join to sheets together och view a subset of columns in a third sheet. I've tried several different methods, but I dont think any of them are completely good. I've used VLookup, Index, MS Query. (MS Query must be the must forgotten MS product in history. It's like a time machine back to Windows 3.11) I've also tried alot of different methods for searching a range, based on more than one criteria, and display the result, either a single value or a sum based on several rows. Here i&#...

How do you copy a cell's content verses it's formula?
I have 2 cells and combined them into a third cell with the following formula... =a1&" "&b1. I was combining a person's first name (cell 1) with a person's second name (cell 2) so cell 3 included the first and second name. Now I want to copy and paste cell 3, but it copies the formula... I need to paste in the content (first and second name) not the formula. Hi Tammy, You could use a macro see JOIN macro on it's page http://www.mvps.org/dmcritchie/excel/join.htm not what you actually asked because if would change column A with the concatenated const...

Question about formula or marco
Hi all, Here is what I need to do: Column B will have a list of first names (John), Column C will have last names (Smith). What kind of formula or macro do I need so that I can have Column A read "jsmith" (all lower case)? Any ideas? Thanks very much. Try this formula in Cell A1:- =LOWER(CONCATENATE(LEFT(B1),C1)) "Gus Jae" <gusjae@verizon.net> wrote in message news:dn8Ue.186$sk2.153@trndny03... > Hi all, > Here is what I need to do: > Column B will have a list of first names (John), Column C will have last > names (Smith). > What kind of formu...