how do i average the 2 smallest number in a range of six numbers

I tried (small(a1:a6,1)+small(a1:a6,2)/2, but got an incorrect number.
Is there problem with syntax, or perhaps another to accomplish what I am 
trying to achieve.

Zetstrat
0
11/20/2004 5:31:01 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
372 Views

Similar Articles

[PageSpeed] 52

Try:

    (small(a1:a6,1)+small(a1:a6,2))/2
or
    small(a1:a6,1)/2+small(a1:a6,2)/2

Regards

Trevor


"zetatstrat" <zetatstrat@discussions.microsoft.com> wrote in message 
news:063ED87E-D4C9-4088-AD9B-4BAD563C5A86@microsoft.com...
>I tried (small(a1:a6,1)+small(a1:a6,2)/2, but got an incorrect number.
> Is there problem with syntax, or perhaps another to accomplish what I am
> trying to achieve.
>
> Zetstrat 


0
Trevor9259 (673)
11/20/2004 5:41:28 PM
Hi
the following should do
=SUM(SMALL(A1:A6,{1,2}))/2

"zetatstrat" wrote:

> I tried (small(a1:a6,1)+small(a1:a6,2)/2, but got an incorrect number.
> Is there problem with syntax, or perhaps another to accomplish what I am 
> trying to achieve.
> 
> Zetstrat
0
frank.kabel (11126)
11/20/2004 5:51:04 PM
This should do it:
=((SMALL(A1:A6,1))+(SMALL(A1:A6,2)))/2

Your formula calculates out of order. Excel always divides before it adds in 
a formula, unless you group parts of the equation together with parentheses.

tj

"zetatstrat" wrote:

> I tried (small(a1:a6,1)+small(a1:a6,2)/2, but got an incorrect number.
> Is there problem with syntax, or perhaps another to accomplish what I am 
> trying to achieve.
> 
> Zetstrat
0
tjtjjtjt (488)
11/20/2004 5:51:06 PM

"zetatstrat" wrote:

> I tried (small(a1:a6,1)+small(a1:a6,2)/2, but got an incorrect number.
> Is there problem with syntax, or perhaps another to accomplish what I am 
> trying to achieve.
> 
> Zetstrat

Thanks to everyone for their help
0
11/20/2004 5:59:03 PM
Why not simplify to
  =AVERAGE(SMALL(A1:A6,{1,2}))

Jerry

Frank Kabel wrote:

> Hi
> the following should do
> =SUM(SMALL(A1:A6,{1,2}))/2
> 
> "zetatstrat" wrote:
> 
> 
>>I tried (small(a1:a6,1)+small(a1:a6,2)/2, but got an incorrect number.
>>Is there problem with syntax, or perhaps another to accomplish what I am 
>>trying to achieve.
>>
>>Zetstrat

0
post_a_reply (1395)
11/21/2004 12:21:32 PM
You are missing a few parentheses, but Frank and Jerry both provided more 
concise formulas anyway.

tj

"zetatstrat" wrote:

> 
> 
> "zetatstrat" wrote:
> 
> > I tried (small(a1:a6,1)+small(a1:a6,2)/2, but got an incorrect number.
> > Is there problem with syntax, or perhaps another to accomplish what I am 
> > trying to achieve.
> > 
> > Zetstrat
> 
> Thanks to everyone for their help
0
tjtjjtjt (488)
11/23/2004 2:33:04 AM
Reply:

Similar Artilces:

2. Data Entry
Using Excel 97 SR-2. Never had this problem till yesterday. Anytime I enter into a cell "2.", it auto enters "2.0.". If I enter "22.", it auto enters "22.0.". It enters it even before pressing the enter key. It seems to auto display "0." every time I enter "." after a "2". This doesn't happen with any other number besides "2". Happens if I enter "2" from the normal keyboard, or from the numeric keypad. Thanks in advance. Click Tools > Options > Edit tab Uncheck "Fixed...

alternate recipient #2
Hello We have one user who wants to get his company mail fowarded to an external e-mail address. In 5.5 we created an alternate recipient and then choose that recipient in the delivery options. I cant see how to do that in Exchange 2000. Can anyone advise. Regards "Jared" <jbarnes@ccsouth.com> wrote: >We have one user who wants to get his company mail >fowarded to an external e-mail address. In 5.5 we created >an alternate recipient and then choose that recipient in >the delivery options. I cant see how to do that in >Exchange 2000. Can anyone advise. Subs...

Linker errors #2
I get these errors when linking my program. I link with a static library I created that has a CComboBox derived class. I expected the correct libraries for these functions would already be linked into my static library. Thanks mp MYPROG error LNK2001: unresolved external symbol "public: virtual long __thiscall CWnd::put_accValue(struct tagVARIANT,unsigned short *)" (?put_accValue@CWnd@@UAEJUtagVARIANT@@PAG@Z) MYPROG error LNK2001: unresolved external symbol "public: virtual long __thiscall CWnd::put_accName(struct tagVARIANT,unsigned short *)" (?put_accName@CWnd@@UAE...

Averaging numbers but ignoring < and
I have a column of data as illustrated below than contains numbers, blank cells, dashes and < entries. Is it possible to average only the numbers ensuring that the divsor is the number of cells that contain number entreis rather than all cells containing an entry (eg for below example answer should be 0.137) 0.3 <0.001 0.01 <0.01 0.1 Thank you On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote: >I have a column of data as illustrated below than contains numbers, blank >cells, dashes and < entries. Is it possible to average only ...

Newsgroup #2
I have outlook 2003. How do I set up a newsgroup using outlook? Thanks, Alice In OE... outlook doesn't have it's own newsreader, it uses OE. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlook newsletter: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM "Alice"...

Outlook hangs #2
This is a strange one. If I restart my computer and run outlook it will start up and as soon as I access my email it will hang. I get a "Outlook is not responding" error message in the command line area at the top left of my screen. I have to shut down outlook, restart it, disable the auto send/receive option, restart my computer, restart outlook, wait a few minutes and them re-enable auto send/receive. Now here's the kicker, I was only having this problem at work and not at home until I transferred the .pst file from work to home. Now I'm having this problem at hom...

xla #2
What is an xla file? What is it used for? ...

averaging less than values
How do I average a column of numbers where less than values are calculated as that number? 2 <2 3 4 <2 5 In the average I want the<2 to = 2. So the answer should be 3 -- JD Assuming source data as posted in A2:A7 Paste this in say, B2, then press CTRL+SHIFT+ENTER to array-enter the formula: =AVERAGE(SUBSTITUTE(A2:A7,"<","")+0) Adapt the ranges to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "JD" wrote: > How do I average ...

Gold #2
Hello. Does anyone know if and how I can automatically get the price of gold? Thanks, Andrew What I do is enter gold bullion as being a stock with symbol AU:GOLD. After creating a stock with name AU:GOLD, go to "details" for that stock and set "Country/Region" to "Australia". Each share of AU:GOLD corresponds to 0.1 ounces of gold bullion. What AU:GOLD actually is is an Exchange Traded Fund for gold bullion trading on the Australian stock exhange. Its share proce is quoted in Austrialian Dollars but setting "Country/Region" to "Australia&qu...

printer prints 1/2 page then ejects
When I print a excell or windows document on my Epson colcor 880 printer it prints about 1/2 page then ejects. The full page does not print. The last half of each page just dissapears. Any suggestions? In article <8560644E-9128-4972-B220-27423D7A79A5@microsoft.com>, =?Utf-8?B?cmZpbGVy?= <rfiler@discussions.microsoft.com> wrote: >When I print a excell or windows document on my Epson colcor 880 printer it >prints about 1/2 page then ejects. The full page does not print. The last >half of each page just dissapears. Any suggestions? Have you got a print area selected ? ...

2 data files with same address....
When looking at the acct. settings for my data files, I noticed I have two Personal Folders showing, each with the the file name: Outlook.pst\Documents and Settings\Lenovo User\Desktop\Company Name. One is marked default. When I click on the "open folder" button, I get the same file in the above referenced folder on my desktop. Why would I have two and should one be deleted? BTW, I use OL2007 and Win XP Pro SP 3. Thanks- asf66 This would indicate a corruption in your mail profile. You can recreate it via the Mail applet in Control Panel. See http://www.how...

how can i print postcard onto A5 with 2 copies to a page
how can i print postcard onto A5 with 2 copies to a page without any margin If your postcard is the standard 14.65cm x 11.05cm you can't. If you don't care if it is standard or not just divide your A5 stock in half, each card will be 14.8cm x 10.5cm -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "shree" <shree@discussions.microsoft.com> wrote in message news:4444CA0A-6BD2-4724-A512-6DE9744F1623@microsoft.com... > how can i print postcard onto A5 with 2 copies to a page without any margin Hi Mary, than...

Average of Averages
Another seemingly simple process which I can't figure. :) I have a list of averages, and I want to get the average of them. As such, A 1 25% 2 42% 3 100% 4 50% I need a formula to average something like the above. Thanks, Alan =AVERAGE(A1:A4) is one possible solution. Hope it helps. -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838 View this thread: http://www.excelforum.com/showthread.php?threadid=513937 Hi Alan It really depen...

Deleting Range name's listed in the range address box.
In Excel, in the top corner of the application there is a box which list the name of a select range (e.g F19 etc..). when I use the querytables method to import a CSV onto a worksheet. This range address box, has the name of the data I have imported. when I use clear, or qyertytables delete to remove the imported data - I am unable to remove the name of the data appearing in the drop-down list for the range box. Is there anyway to delete this ? or is it not a problem ? I am concerned because I this rangelist seems to fill up pretty quickly with imported data ranges.. Choose Insert&...

Outlook Archiving Problem #2
Outlook will ask me if I want to Archive, I hit ok. Mine in set to archive items 6mos old. Nothing shows up in my archived folder list. Help. I am the main email address for our company, my inbox is huge. Beth Check the Modified date on the items that aren't being archived as you think they should. That's the date Outlook uses to determine whether or not an item is ready to be AutoArchived, and a simple action such as moving the item to another folder can change the Modified date. More information and a list of actions that change the Modified date can be found here: http:...

Averages
Hello again, I have an interesting situation. I basically inherited an older database that is used to enter dates, usage and charges for accounts. The situation is that we are attempting to do averages filtered by account, and averaged against the number of days that data has been entered. So, for example, we had 7 months of data entered, our query should total the number of days in the seven months (x), then average that with the total usage number (y). This data currently resides in the same table labeled "data" in the form of from and to dates (6/1/1999 to 7/1/1999) and usage (32...

How do I prevent duplicate numbers in a range of validated cells?
I have a validation rule for a series of cells. The 3 cells are only allowed to have a value of 3, 5, or 1. I want to fix it so that each number may only be used once in a range of 3 cells. .... So in A1:A3, each number may only appear once or an error message pops up or the cell starts blinking ~ something needs to happen to notify the user that there is an error that needs correcting. THanks ...

Jeff #2
it says it's under c:\POSroot\Reports Isn't this where I was supposed to save it? (sorry for being such a pain) Cyn Jeff! I figured it out, I had to copy the file go to POSroot and paste it in these files. THANKS so much for all your help!! Cyn >-----Original Message----- >it says it's under c:\POSroot\Reports >Isn't this where I was supposed to save it? >(sorry for being such a pain) >Cyn >. > Cyn, Go into Store Operations Administrator > File > Configuration > Paths tab > Reports, the very first line on this screen and write down the com...

Random numbers
How exactly do i generate random numbers in VC++.Is the a function similar to random() in C++.Thanks in advance Fenn On Mon, 10 Jan 2005 22:42:52 -0800, "Fenn" <fenn_j@yahoo.com> wrote: >How exactly do i generate random numbers in VC++.Is the a function similar >to random() in C++.Thanks in advance > VC++i is C++. Besides, there is no random() in C++. I think you mean rand(). And besides, that is a C function imported into C++. In any event, generate random numbers in Visual C++ using MFC exactly the same way you would in any C++ (or C) program. .. yeah, b...

Physical Inventory #2
Another one! How best to input new quantities after doing a physical inventory. OK, you go to Manager>Inventory>Physical Inventory and select "Generate for all items..." and select "All items". After hafing generated the items list, how do you input the new count for the items? Is manual entry the only way? Thanks, Note the "Import File" button... Create a new Physical inventory - Blank, for manual entry Generate a CSV file with lookup code, quantity , and Date counted (optional). This is the type of content that most handheld scanners will gene...

Average Lead Time
Does anybody know if the average lead time in Item Vendor Maintenance counts weekends, or only business days? The average lead time is calcualted as follows: Enter the average number of days that pass between the time you place an order with this vendor and the time you receive the order. If you're using Purchase Order Processing, this field will be updated each time a shipment or shipment/invoice is posted in Purchase Order Processing. The average lead time is calculated using the following formula: [(Number of Receipts) * (Average Lead Time) + (Received Date - Ordered Date)] / (...

Sum of column #2
Hi I have a worksheet containing 25 columns In the column A I have categories and the other columns B to Z represent periods Looks like this PLB 1 2 3 1 2 2 2 3 .................. ADD 3 4 2 4 1 5 6 4 .................. AVL 2 1 4 2 3 7 1 4 ................... PLB 2 3 2 1 5 2 4 3 ..................... I am looking for a formula that will add each columns for the same Categorie So the result for the categorie PLB should be 1+2+3+1+2+2+2+3 (first row) + 2+3+2+1+5+2+4+3 (last row) = total = 38 ADD = 29 AVL = 24 thanks for helping Try this: =SUMPRODUCT((A2:...

PDF file #2
I have Publisher 2002 and would like to insert a .pdf file into a document I am working on. How do / Can I do this? Many thanks, Ger ...

GAL question #2
We are running Windows 2003 and Exchange 2003. We have one forest with two domains. A domain for students and a domain for faculty. We don't want people from one domain to be able to see the people in the other domain in the GAL. Anyone know of a way to hide one domain from the other domain? Any Q Articles? Thanks for any help. Clayton "Clayton Sutton" wrote: > We are running Windows 2003 and Exchange 2003. We have one forest with two > domains. A domain for students and a domain for faculty. We don't want > people from one domain to be able to...

tangent excel question #2
i appreciate the reply but i dont have autocad installed and dont reall have access to it. i do like the idea of superimposing the c over the but im not sure ill be able to do that in excel. the only way may be t make a tiny picture file of CL. if i knew of a way to make my own fon then that would work as well, but unfortunately i dont -- lsu-i-lik ----------------------------------------------------------------------- lsu-i-like's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1431 View this thread: http://www.excelforum.com/showthread.php?threadid=27102 ...