#### array formulas-sumproduct and average

```Hello, I need hel. I am using this formula
=SUMPRODUCT(--(E2:E289=1),--(AVERAGE (H2:H289)))  I don't know what's wrong
with it.  I need to find the average for column H in relation to 1(under 2
yrs) in Column E.  Hope this makes sense.

Thanks,
Becky
``` 0 1/29/2005 10:51:03 PM excel.newusers  15348 articles. 2 followers. 4 Replies 430 Views Similar Articles

[PageSpeed] 39

```Try: =AVERAGE(IF(E2:E289=1,H2:H289))
Array-enter the formula with CTRL+SHIFT+ENTER
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Becky" <Becky@discussions.microsoft.com> wrote in message
news:38BA387B-4DA3-4274-9CB5-CCC7E976DDAE@microsoft.com...
> Hello, I need hel. I am using this formula
> =SUMPRODUCT(--(E2:E289=1),--(AVERAGE (H2:H289)))  I don't know what's
wrong
> with it.  I need to find the average for column H in relation to 1(under 2
> yrs) in Column E.  Hope this makes sense.
>
> Thanks,
>     Becky

``` 0 1/29/2005 11:42:14 PM
```And if an error trap is needed to return blanks: ""
instead of #DIV/0! , then try:

=IF(ISERROR(AVERAGE(IF(E2:E289=1,H2:H289))),"",AVERAGE(IF(E2:E289=1,H2:H289)
))

(Array entered as before)

--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----

``` 0 1/30/2005 12:03:48 AM
```Thanks, that did it.

"Max" wrote:

> Try: =AVERAGE(IF(E2:E289=1,H2:H289))
> Array-enter the formula with CTRL+SHIFT+ENTER
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8,  1° 22' N  103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Becky" <Becky@discussions.microsoft.com> wrote in message
> news:38BA387B-4DA3-4274-9CB5-CCC7E976DDAE@microsoft.com...
> > Hello, I need hel. I am using this formula
> > =SUMPRODUCT(--(E2:E289=1),--(AVERAGE (H2:H289)))  I don't know what's
> wrong
> > with it.  I need to find the average for column H in relation to 1(under 2
> > yrs) in Column E.  Hope this makes sense.
> >
> > Thanks,
> >     Becky
>
>
>
``` 0 1/30/2005 12:51:02 AM
```You're welcome !
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
Becky <Becky@discussions.microsoft.com> wrote in message
news:5ED758C2-19DF-41C9-BC06-589807473E04@microsoft.com...
> Thanks, that did it.

``` 0 1/30/2005 1:43:23 AM Similar Artilces:

formula #14
I got this response (which worked)for a question I had on a formula and not to sound silly but what does the double quotation mark mean in this formula? =IF(D22="Yes",IF(B10*0.05<D10,B10*0.05,D10),IF(D22="No",0,"")) thanks las It is a default action, if no other condition is met, it returns an empty string. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "las" <las@discussions.microsoft.com> wrote in message news:B40824F0-B019-46E4-ABEC-0D343F0549B8@microsoft.com... > I got this response (which worked)for a qu...

If statement with lookup array... any advice?
I am trying to automate a spreadsheet. The basic formula is this... Any suggestions or corrections would be appreciated. =if(Logical_test, lookup_array, lookup_array) actual formula =IF(B3="Back Mount",LOOKUP(B56,{2050,3250,5150,8500,1150,15400,18250,22500,26000,31000,41000},{128,158,188,228,248,278,308,338,368,408,408}),LOOKUP(B56,{3800,5300,6200,8700,13500,17000,21000,26000,33000},{"X12B39",'X15B39","X18B39","X22B39","X24B43","X27B47","X30B55","X33B55","X27B71"})) I have also tried =IF(...

cell formula not updating right after insert row
Hello, I have a formula in a column as follows - the details of what it does are for the most part unimportant (this one is at row 50): =IF(D50<>"",D50-SUM(G50:AA50), IF(C50<>"", E49 +C50-SUM(G50:AA50),"")) I frequently end up inserting rows and copying the above formula to the right column for the new row. What happens though is that this formula and all those following get messed up as so: =IF(D51<>"",D51-SUM(G51:AA51), IF(C51<>"", E49 +C51-SUM(G51:AA51),"")) Everything got adjusted except for...

nested formula help needed
This is what the help menu tells me. "Nest no more than seven functions You can enter, or nest, no more than seven levels of functions within a function. Learn about creating nested functions." I was curious if anyone could tell me how to write a IF statement with more than seven functions? Is it even possible? Is there a way to combine functions to give the same effect? I am trying to write one with approximately 25. Any help would be greatly appreciated. I have tried just about everything and am very stumped. Thanks. ------------------------------------------------ ~~ ...

Sheet names used in formulas
I have a question about using sheet names in a formula. I would like the sheet name to go up by one page for each row I put the formula in...(ie) 13A reads (="S40'!\$W\$5)... I need to have row 14 read (='S41'!\$W\$5) There are many many worksheets...260 in all that need this on them. Each worksheet is about 300 rows long. How can I do this without having to change the sheet name by hand??? Any help is greatly appreciated!!! Hi! Here's one way: =INDIRECT("S"&ROW(A40)&"!\$W\$5") Which evaluates to: =S40!\$W\$5 As you drag copy down: =S40!...

Copying result of formula into another worksheet???
I am having problems copying a formula result into another wksht if the formula is not used...hard to explain... D10=Sum(A1:A5) I want to transfer D10 to wksht Data E12, however if there is no data in A1:A5, I want E12 to remain blank. It looks like it is reading the formula, so it places a "0" or a "-".... Any ideas? Tools -> Options -> View Turn off zero values should get you part of the way. "Amanda" wrote: > I am having problems copying a formula result into another wksht if the > formula is not used...hard to explain... > > D10=S...

Best ways to search an array/collection for an element
Hi guys! I'm creating a small library of utilities for VBA programming, and right now I'm completing my ArrayManagement and CollectionManagement module. I was considering different solutions for the classical case of searchin an array or collection for a specified elements. So far I've considered using Worksheetfunction.VLOOKUP, Worksheetfunction.Match (needs some care to take into account the "element not found" case) for the arrays. I also developed an handy binary search function for sorted arrays, but it works only for 1D numeric arrays (sorted in either no...

formula for updating 1 spreadhseet from another
I have two spreadsheets one for "current accounts" and one for "past due accounts". The "current" spreadsheet list all our receivables from this year even if they are still outstanding, the "past due" spreadsheet list only those that are 30 days or more past due. What I currently have to do is if a vendor pays an invoice update the "current" spreadsheet and then if its a past due go an update the "past due spreadhseet". My question: Is there away to automaticly have the "past due" spreadhseet update itself if I enter a payme...

protecting formulas
I am trying to find a way to protect formulas on a spreadsheet whil still allowing users to put in new data. Is there a way to do this? am using excel 200 -- Message posted from http://www.ExcelForum.com Cherilyn unlock the cells where you want input and then protect the sheet with a password. Format | Cells... | Protection tab | Locked = false (not ticked) Regards Trevor "Cherilyn >" <<Cherilyn.16krpe@excelforum-nospam.com> wrote in message news:Cherilyn.16krpe@excelforum-nospam.com... > I am trying to find a way to protect formulas on a spreadsheet while ...

Excel formula problem #2
Hi everyone, What I am doing is programatically creating the HTML/XML structure o an Excel 2000 document. I am almost done, however I am having problem with some excel formulas. I have no access to the actual column names (A, B, C,...etc.) I onl have integer vales. So, basically I am trying to map these intege vales to the actual column names. So, for example, if I want to check to see if a particular text valu is there in a range (A1, A3), the following works: Code ------------------- MATCH("MYTEXT", A1:A3, 0) What I try to do is the following: MATCH("MYTEXT...

Linear Versus Moving Average
I am describing a trend in monthly Immunization totals to hospital management. Immunizations are up 17% over last year's total at this time. However, a simple linear trendline points downward because of seasonal work patterns - very high in the beginning, then lower, then gradually bending upward in the last several months. A moving average "trend" merely approximates this year's work pattern - so, why display it at all? Yet, if I were to choose to display a trendline, a moving average approximates the actual peformance of this department. Is it useful to includ...

How do I copy a cell + it's formula from one workbook to another?
For reasons i won't go into, i have to transfer hundreds of cells from one workbook to another. each one of these cells contains a formula ex: "=SUM(IF(Production!\$I\$2:\$I\$573="HM37",IF(Production!\$H\$2:\$H\$573="AP",IF(Production!\$S\$2:\$S\$573="36H",IF(Production!\$D\$2:\$D\$573="X",Production!\$Q\$2:\$Q\$573,0))),0))-SUM(IF(Production!\$I\$2:\$I\$573="HM37",IF(Production!\$H\$2:\$H\$573="AP",IF(Production!\$S\$2:\$S\$573="36H",IF(Production!\$E\$2:\$E\$573="X",Production!\$Q\$2:\$Q\$573,0))),0))" the new workbook is an identical...

IF Formulas with Ranges
Hi! I'm trying to create an IF formula where the workbook will recognize if there is data added to a column D range (Sessions) and then apply a formula to the last cell of column F range (Aimline). Hence, if Sessions (column D) which now has the numbers 1-27 in it gets 28 entered below, then the cell in the same row but column F will take the number 28 and apply the formula above it =\$F\$8+(\$A\$12*D35). Can anyone help? cabybake =IF(D27="","",\$F\$8+(\$A\$12*D27) made an assumption that D35 should relate to D28 -- HTH Bob Phillips (remove nothere from email address...

VBA Formula Problem
I am using the code below and get an error with the formula statement. I need help. TIA Sub Salary() Range("Salary").ClearContents Selection.QueryTable.Refresh BackgroundQuery:=False For Each c In Range("LName") c.Formula = Upper(Left("D2", Find(",", "D2", 1) - 1)) Next End Sub Greg the formula needs to be a string. D2 should not be in quotes either: c.Formula = "=Upper(Left(D2, Find("","", D2, 1) - 1))" Note that this will set the entire range to read from D2. If you want it instead to read from column...

excel array formula
Hi, I want to use array formula's to reduce a very large spreadsheet. VBA and macros are not to be used, due to the webpublication of this spreadsheet. Is there a way to reduce a simulation run with repetitive formula's, while at the same time the intermediate results are still available. I came this far: Run Formula_1 Formula_2 Formula_3 2 1 1 =con1 + D40*con2 2 =con1 + D41*con2 3 =con1 + D42*con2 4 =con1 + D43*con2 5 =con1 + D44*con2 6 =con1 + D45*con2 7 =con1 + D46*con2 8 =con1 + D47*con2 9 =con1 + D48*con2 10 =con1 + D49*con2 =con3*D50 =SUM(con3*D41:D50+F40:F49) Is there a way...

help with a unique formula question
Ok. I have a workbook, in this workbook there is a worksheet called: main on the main worksheet I have a import button that I press, which fetches a daily roster file from the network and it imports the files into the workbook into a sheet called: master.xls So there are two sheets: main and master.xls (with the .xls extention) Before I go on to explain what I mean by help with a formula, let me explain what the master.xls sheet has: 4 columns: A1:D1 headers, data starts at A2 and below. Daparment Name, Date, Employee Name, HR status code So for example, lets use these values, A2:D2 ...

Finding Location of Maximum Value in 2D Array
I am using MS Office ExCel 2003(11.6560.6568) SP2 and have a single sheet with a 2D array of numbers roughly 202x202 in size. Could someone tell me the quickest way to locate the maximum value in the array? I tried =WhereMax("Sheet1") but just got #NAME? as the result. Any assistance would be greatly appreciated, Peter. Hi Major: Sub findmax() Dim r As Range Set r = Selection v = r.Cells(1, 1).Value For Each r In Selection If r.Value > v Then v = r.Value s = r.Address End If Next MsgBox ("maximum value " & v & " found in cell &...

Formulas #22
how do i add a compounding number in a formula? example: 125,150,200,275,375,500 125+25=150 150+25+25 OR 150+50=200 200+25+25+25 OR 200+75=275 i think i need the formula to compound 2 things the number i start(A)with and also the number i add to (A) One way: A1: 125 A2: =A1+25*(ROW()-1) Copy A1 down as far as desired. In article <d4704b7e.0411150829.6567a986@posting.google.com>, princess_di_69145@yahoo.com (Dianna) wrote: > how do i add a compounding number in a formula? > example: 125,150,200,275,375,500 > 125+25=150 > 150+25+25 OR 150+50=200 > 200+25+25+25 ...

how can i change a value in a cell from a formula in another cell?
A formula can only update the value in the cell that holds it. davidhub wrote: -- Dave Peterson On Tue, 5 Jul 2005 05:00:01 -0700, "davidhub" <davidhub@discussions.microsoft.com> wrote: In the cell that you wish to change (e.g. A2) enter a formula that refers to the cell with the formula (e.g. A1) For example, in cell A2 enter: =A1 If having a formula in A2 is not acceptable, then you will need to use a VBA solution. Using an event macro, you can check the contents of A1 and write an appropriate number into A2. To enter the VB routine, right click on the workshee...

Need help on a formula
I need a formula that will take the following information from cell A3 and populate it in A4. Here is the information in A3 - jones123456 I need a formula in A4 that will take the info in A3 and create an email address that has the last name AND the last 3 digits of the number, plus the domain.... so that the data will read in A4 - jones456@gmail.com. I have the first part of the formula like I want it to populate the last name =IF(A3=0,"",A3). But then I don't know how to get the last 3 digits of the number AND The domain name added to the last name in A4. Here is what I ...

Query formulas (dates in formulas to be changed only once)
I have several queries that contain several hard-coded formulas. If possible, I would prefer that the formulas be referenced in a control or setup table. Does that make sense? Instead of going through my queries each month and updating hardcoded dates, I would like the dates to be changed only once and have all the formulas reference this source. Thank you!! Craig It makes sense to do this. How you do it depends on what the formulas are? For instance if you are just trying to get the data for a prior month, you can change the formula to get the prior month based on the system ...