Help with array formula

I need help with an array formula,
I have to columns, I need to find the min, the max and average form the
two columns added, but I cant create a new column.
E.g.;

name        s1    s2
John           1     1
Peter          3      5
Frank          6      3

min= 2  ( John)
Max= 9  (Frank)
average= 6.33

Thanks

0
11/28/2006 3:56:45 PM
excel 39879 articles. 2 followers. Follow

5 Replies
343 Views

Similar Articles

[PageSpeed] 7

One way (array entered):

    =MIN(B2:B4+C2:C4)

    =MAX(B2:B4+C2:C4)

    =AVERAGE(B2:B4+C2:C4)

In article <1164729405.003619.272780@j72g2000cwa.googlegroups.com>,
 GastonFranzini@gmail.com wrote:

> I need help with an array formula,
> I have to columns, I need to find the min, the max and average form the
> two columns added, but I cant create a new column.
> E.g.;
> 
> name        s1    s2
> John           1     1
> Peter          3      5
> Frank          6      3
> 
> min= 2  ( John)
> Max= 9  (Frank)
> average= 6.33
> 
> Thanks
0
jemcgimpsey (6723)
11/28/2006 4:05:33 PM
Thanks!

0
11/28/2006 4:28:06 PM
try this idea
=SUMPRODUCT(MAX((I2:I4)+(J2:J4)))
isn't the average 7.33
-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
<GastonFranzini@gmail.com> wrote in message 
news:1164729405.003619.272780@j72g2000cwa.googlegroups.com...
>I need help with an array formula,
> I have to columns, I need to find the min, the max and average form the
> two columns added, but I cant create a new column.
> E.g.;
>
> name        s1    s2
> John           1     1
> Peter          3      5
> Frank          6      3
>
> min= 2  ( John)
> Max= 9  (Frank)
> average= 6.33
>
> Thanks
> 


0
dguillett1 (2487)
11/28/2006 4:50:52 PM
"Don Guillett" <dguillett1@austin.rr.com> wrote in message 
news:ufonb1wEHHA.992@TK2MSFTNGP03.phx.gbl...
> isn't the average 7.33

How do you get that?  If I substitute AVERAGE for MAX in you formula I get 
6.3333333 the same as if I sum the values in a different calumn and then 
average that.

-- 
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk



0
sandymann2 (1054)
11/28/2006 7:52:35 PM
I was using a different set of numbers.

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Sandy Mann" <sandymann2@mailinator.com> wrote in message 
news:eXZ5KbyEHHA.4120@TK2MSFTNGP02.phx.gbl...
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message 
> news:ufonb1wEHHA.992@TK2MSFTNGP03.phx.gbl...
>> isn't the average 7.33
>
> How do you get that?  If I substitute AVERAGE for MAX in you formula I get 
> 6.3333333 the same as if I sum the values in a different calumn and then 
> average that.
>
> -- 
> Regards,
>
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> sandymann2@mailinator.com
> Replace@mailinator.com with @tiscali.co.uk
>
>
> 


0
dguillett1 (2487)
11/28/2006 9:01:22 PM
Reply:

Similar Artilces:

Click in cell w/ formula and get colors in referenced cells
Hi, When you click in a cell with a formula, Excel will then put color around the cells that are referenced. Somehow I turned that feature off. Now I do not get colors in th other cells. Where is that option to turn it back on???? I've looke and looked but I just can't find that option. Thanks for the help -- albea ----------------------------------------------------------------------- albean's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2875 View this thread: http://www.excelforum.com/showthread.php?threadid=48436 ...

Need Help #2
I have recently fallin' on hard economic times, I would really like you all to send me donations so I may purchase the new Office 2003 Professional Suite next month. Please send funds to Excel Guru P.O. Box 321 Grosse Pointe, MI Thank you. What's the zip code there? >-----Original Message----- >I have recently fallin' on hard economic times, I would >really like you all to send me donations so I may >purchase the new Office 2003 Professional Suite next >month. Please send funds to > Excel Guru > ...

How can I remove Chinese characaters in Word 2010 beta Help
Is there a way to remove Chinese characters in Word 2010 beta Help results? ...

How do declare an array of structs?
Hi all, I try to use an array of structs in my program. I define it in a header file like this struct fPath { int nOfPoints; double lon, lat; }; Now when I add just the following line into the header file fPath* flyPath[10]; the program compiles well, but when the program is built in Realease mode and it's run, it produces three message boxes "An unsupported operation was performed" when the relevant window is first displayed. This doesn't happen in Debug mode. If I add further code I can use the array in the program successfully, but I cannot get rid of the...

monthly budget/check registry spreadsheet formula
I'm working on a monthly budget/check registry spreadsheet. What I want to do is have the check register link to the allowed balances in the monthly budget. I have two fields in my monthly budget for each item. A projected cost and actual cost. I want to link entries in the check registry to the actual cost of the fields in the monthly budget. I plan to use account numbers to link these fields to each other. For example . . . Say I buy groceries for $50. When I enter this into the check registry I enter all the necessary information along with an account number that links this tra...

Help Creating A Formula #2
I need to make a formula for alook up. The first spreadsheet i inventory, the second one is pg 103 strapping. I want to enter the fee and inches for pg 103 on the inventory and have it look up the feet an inches on strapping chart and enter the appropriate gallons in column Attachment filename: p & g 101 strapping.zip Download attachment: http://www.excelforum.com/attachment.php?postid=50210 -- Message posted from http://www.ExcelForum.com ...

Help #5
Below is what I have on a spreadsheet. I need sum up the P&L values for each period (month) by counterparty. If the sum is positive it is an AR, If negative an AP. Anyone know a quick way to do this? Period Counterpart P/L AR AP Jul-05 Coke 5000 Jul-05 Coke 10000 In D2 =IF(C2>0,"AR",) In E2 =IF(C2>0,"AP",) Then highlight D2 to E3 and hit control d "Tim T via OfficeKB.com" wrote: > > Below is what I have on a spreadsheet. I need sum up the P&L values for each > period (month) by count...

Time duration formula
I would like to know the formula to work out my employees employment duration, from a given start date to the current day. many thanks Take a look at DATEDIF. Explained at http://www.cpearson.com/excel/datedif.htm -- HTH RP (remove nothere from the email address if mailing direct) "Ska" <Ska@discussions.microsoft.com> wrote in message news:B10818AE-1CAD-4CE5-BDED-BC0DDC96C876@microsoft.com... > I would like to know the formula to work out my employees employment > duration, from a given start date to the current day. > > many thanks Thanks Bob, I have us...

Excel formula help #4
Hello! Can somone help me with this formula 2004:21-0-1-1-11 in this cell i wan't to get out "21" 2005:2-0-0-0-1 in this cell i wan't to get out "2" My result from the formula is "-21" and ":2-" is there a formula that i can use on both and get the result "21" and "2"? I want to add the together at the end and get the result "23" but with : and - i only get error. -- johhny ------------------------------------------------------------------------ johhny's Profile: http://www.excelforum.com/member.php?act...

HELP ME RESTORE OUTLOOK
I accidentally removed my Outlook e-mail program with the add/remove wizard. I know it's still on the computer, but just HOW do I get it back? HELP ME RESTORE OUTLOOK wrote: || I accidentally removed my Outlook e-mail program with the add/remove || wizard. I know it's still on the computer, but just HOW do I get it || back? If you used Add/remove then it's NOT on the computer. You need to re-install it from your Office CD -- Interim Systems and Management Accounting Gordon Burgess-Parker Director www.gbpcomputing.co.uk HELP ME RESTORE OUTLOOK wrote: > I accidentally r...

Help creating a script in SQL or Calculated field in Crystal
How do I take this script and manipulate it to give me one long string with static text as well as SQL data in specific positions within the string. Example result: Positions / Data: 1-3 / 173 (Static text) 4-6 / spaces 7-10 / "X_UPR30300"."YEAR1", (has to show up as 2007. Showing up as 2,007) 11 / 4 (Static text) 12-22 / "UPR00100"."SOCSCNUM" 23-57 / "UPR00100"."LASTNAME" 58-92 / "UPR00100"."FRSTNAME" 93 / "UPR00100"."MIDLNAME" 94-120 / "UPR00102"."ADDRESS1" 121-148 / &q...

CONCATENATE text to create a formula to be evaluated
Hi, I am wanting to concatenate a set of text to create a formula. I have done so below. =CONCATENATE("=MAX('",O1,"'!A3:A65536)") The result is: =MAX('2009'!A3:A65536) ....but it treats this as a text string when I try to use INDIRECT Cell O1 contains the year minus 1. In this case O1 = 2009. All of my worksheets are named as a year e.g. 2007....2008....2009...2010. I am entering this on sheet 2010. Now the real question: How do I make another cell evaluate this string as an actual formula and spit out the highest number for ...

text in formula
Help!!! Is there a way to have a formula where a cell with text is in it, but it is not included in the formula. Like I have a random cell that appears sometimes within the range but because it is text and I do not want to include it in the formula. Is there a certain "symbol" that could be used? HELP!! Hi maureen, It depends on what the actual formula is, but *some* functions ignore text entries. You could use the ISNUMBER function to include only those entries that are numbers. Post your formula for a more detailed reply. Biff >-----Original Message----- >Help...

Linking text within Excel-- help!
I have a mailing list that I will be importing into Excel, and am trying to link the names on the list to invoices for those people on the list. Can I do this in Excel by using links? The Excel help feature only seems to describe links with figures (numbers), not text. e.g. Mr. Bill Jones, 123 Main Street, Middletown, OK 40404 (each word in its own cell in the mailing list worksheet) ....would link to a worksheet that had Bill Jones' name and address but also indicted that he purchased a $100 product on June 15 and his bill has been paid in full. My questions: 1. Each of the 300 or...

Manual Sales Methodology -- Need Help
Hi, Here is my situation. We are trying to implement a manual sales methodology. Basically I have a dropdown list called "Sales Stage" with values entitles "Stage 1", "Stage 2" and "Stage 3". I need the values here to effect the sales pipeline and the sales pipeline report. The other issue is that a lead can advance or retreat in the sales cycle. Meaning it could go from Stage 1 to Stage 2 and then back to Stage 1 or could go Stage 1 to Stage 3 and then to Stage 2. I know I need a workflow setup (as just effecting the "stepname&quo...

need formula help
I'm trying to have a formula do 3 scenerios in one cell and have a partial formula working: IF(AND(G39>30, G39<=7),3000) which equates to if cell G39 is greater than 0 and less than or equal to 7 use 3000. I would like to continue the formula that if G39 is greater than 7 and less than 14 use 5000 and if G39 is greater than 14 use 7500. How can I do this? Hi, =IF(AND(G39>0, G39<=7),3000,IF(AND(G39>7, G39<=14),5000,if(G39>14,7500))) "tom" wrote: > I'm trying to have a formula do 3 scenerios in one cell and have a partial > form...

Formula Help #66
I need help with a formula. I can't figure out how to calculate for missing number. '05 we sold x units, I know we had a X% increase over '04 so what wa the '04 sales? Column A = '05 Columb B = '04 Column C = % increas -- bab ----------------------------------------------------------------------- babz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2781 View this thread: http://www.excelforum.com/showthread.php?threadid=49307 '04 sales = '05 sales / 1.X% increase Vaya con Dios, Chuck, CABGx3 "babz" wrote: >...

Copying formulas to another worksheet
Can anyone please help me, I am trying to copy a formula =(sump1:p3) from one worksheet into a new worksheet. When I go to paste the formula the only thing that comes up is =SUM(#REF!). Can I even copy and paste a formula from one worksheet into a new worksheet? and if so how? Thanks When you paste a formula it relatively changes the cell references to suit where you have pasted it to. EG. If you pasted =sum(P1:P3) from cell P4 to cell B4 it would change to =sum(B1:B3). However, if you pasted it to cell B2 it could not reasonably change the references. If you are pasting because you...

Creating Formula with Embedded Variable ID
I need to create a single formula for my workbook that references cells from various tabs (over 20) in another workbook. I want to copy this formula into multiple tabs within my workbook. My workbook and the external workbook have the same number of and identically named tabs. I want to embed a variable into the formula that will take on the name of the tab that the formula is copied to. I imagine the formula would look something like this: ='K:\Sales Reports\[Ohio Team, Mar-21-2004.xls]variable_identifying_the_tab_goes_here'!D1 I know I can use the following formula to derive th...

Formula to reference another worksheet, locate data, then record i
Hi All, It has been over 10 years since I did my Excel studies and I've unfortunately forgotten everything I haven't used regularly. My office has recently upgraded to Office 2007 (upgrade not being the descriptor I'd have chosen!) and I'm struggling with Excel. I've found my way around most issues, but I'm REALLY stuck now and suffering Friday-itis on top of it all! Essentially I have a multi-sheet workbook for my debtors ledger. Each page has separate columns for the customer numbers, names, total debts in each age range (7 days, 14 days etc), totals and lastl...

Amount in Words Complete Right Formula
give me complete formula See http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Naveed" <Naveed@discussions.microsoft.com> wrote in message news:29B7C0D6-1DED-4321-B1EB-CA6DDCE73852@microsoft.com... > give me complete formula > ...

Header file help
Sir, I am presently doing an application work in VC++.I am doing the coding for "Virtual CD ROM Drive".In this I am not getting one headerfile i.e "#include<ntddcdrm.h>".In msdn you mentioned the path windows nt DDk which I am not having.I am kindly requesting you to provide this header file through mail. thanking you your faithfully Kiran. Hello, You downloads ntddcdrm.h from the following link, http://cvs.winehq.com/cvsweb/~checkout~/wine/include/ntddc drm.h?rev=1.3&content-type=text/plain Regards, R.Selvam >-----Original Message----- >Sir,...

How to reference workbook name in hyperlink formula?
In order to simplify the maintenance of workbook formulas I need to reference workbook name in hyperlink formula =[myWorkbok.xls]mySheet!$E4 in this way =[A1]mySheet!$E4 given the file name stored in cell A1 Cell A1 : myWorkbook How do I write the formula to make it work? Will it work even if the referenced workbook is closed? Regards Frank Krogh The function you'd want to use is =indirect(). But =indirect() won't work if the sending file is closed. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm ...

Need help, pls, with contacts...
As I enter names in Outlook 2000, I would like their title to appear before their name in both the contact listing AND when I send an email.. example... Dr. Joe Smith... instead of just Joe Smith.. And having to click 'Full Name' to reveal the title of Dr. Also, is their a way to add additional titles to the dropdown list? I also deal with religious that use Sr. (Sister) and Fr. (Father), as well as Deacon... Thank You! -- B'Regards, Vinnie ...

border help
When I go to print my page with my border on it, it prints the top and sides but not the bottom, anyone know what I'm doing wrong? I have tried to change the margins as well but I get the same thing. On Mon, 30 Nov 2009 18:25:01 -0800, michelle <michelle@discussions.microsoft.com> wrote: >When I go to print my page with my border on it, it prints the top and sides >but not the bottom, anyone know what I'm doing wrong? I have tried to change >the margins as well but I get the same thing. See http://www.word.mvps.org/FAQs/Formatting/BottomsDontPrint.htm ...