"Average" calculation formula needed

Hello,  I have Excel 97, and am trying to learn formulas.  I need to find the "average" of certain values in column B below.  This is an example of my worksheet
A              B
1  TRUE      1.0%
2  FALSE     -.8
3  TRUE      -.5
4  FALSE     -.
5  TRUE      1.
6  TRUE      -.
7  FALSE      .

What I need: In Cell C50 I want the calculated result in the form of a "%". FIRST finds the "TRUE" ocurrances in column "A", then calculate the average % (both positive and negative) in column "B" that are next to "TRUE" found in column "A".   The above example would yield a G50 value of 1.7%
What would the Cell G50 formula look like?  I hope to achieve this calculation without manually using "autofilter".   Thanks to anyone who can help
Sa 0 4/13/2004 3:11:07 AM excel.misc  78881 articles. 5 followers. 2 Replies 381 Views Similar Articles

[PageSpeed] 23

Sam

try:

=SUMIF(A1:A7,TRUE,B1:B7)/COUNTIF(A1:A7,TRUE)

Regards

Trevor

"Sam" <anonymous@discussions.microsoft.com> wrote in message
news:E392A708-3C19-4598-ACCF-3587D8EFF5D7@microsoft.com...
> Hello,  I have Excel 97, and am trying to learn formulas.  I need to find
the "average" of certain values in column B below.  This is an example of my
worksheet:
>      A              B
> 1  TRUE      1.0%
> 2  FALSE     -.8%
> 3  TRUE      -.5%
> 4  FALSE     -.3
> 5  TRUE      1.9
> 6  TRUE      -.7
> 7  FALSE      .2
>
> What I need: In Cell C50 I want the calculated result in the form of a
"%". FIRST finds the "TRUE" ocurrances in column "A", then calculate the
average % (both positive and negative) in column "B" that are next to "TRUE"
found in column "A".   The above example would yield a G50 value of 1.7%.
> What would the Cell G50 formula look like?  I hope to achieve this
calculation without manually using "autofilter".   Thanks to anyone who can
help!
> Sam
> 0 4/13/2004 5:43:27 AM
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER)
=AVERAGE(IF(A2:A100,B2:B100))

--
Regards
Frank Kabel
Frankfurt, Germany

Sam wrote:
> Hello,  I have Excel 97, and am trying to learn formulas.  I need to
>      find the "average" of certain values in column B below.  This is
> an example of my worksheet: A              B 1  TRUE      1.0%
> 2  FALSE     -.8%
> 3  TRUE      -.5%
> 4  FALSE     -.3
> 5  TRUE      1.9
> 6  TRUE      -.7
> 7  FALSE      .2
>
> What I need: In Cell C50 I want the calculated result in the form of
> a "%". FIRST finds the "TRUE" ocurrances in column "A", then
> calculate the average % (both positive and negative) in column "B"
> that are next to "TRUE" found in column "A".   The above example
> would yield a G50 value of 1.7%. What would the Cell G50 formula look
> like?  I hope to achieve this calculation without manually using
> "autofilter".   Thanks to anyone who can help!
> Sam 0 4/13/2004 6:06:24 AM Similar Artilces:

desperately need help with average
This is what i have & works but need to do more AO6:AO66 = Names from week 1 =IF(AW6>0,AW6,'Week 1'!S6) AQ6:AQ66 = week 1 hours & adjusted hours =IF(AO6>"",E6+AZ6,"") AR6:AR66 =L means laidoff=IF(AY6>0,AY6,"") AS6:AS66 = Average hours {=IF(OR(AR6="L",AW6>""),AVERAGE(IF(AT\$6:AT\$66>=0,AT\$6:AT\$66)),"")} AT6:AT66 = total hours of this week =IF(AR6="L","",AU6) AU6:AU66 = Total hours of week 1 & this week=IF(AR6="L",AS6,IF(B6="","",AQ6+SUM(F6:Q6)...

1.3*2=? & Cost Averages?
Okay a 2 part question... (1) I would like to disable an employee from entering 1.3 * and scanning an item...sometimes they do this by accident. Is there a way to disable this? (2) When entering cost for the first time in a purchase order what happens is because it was originally \$0 it averages that with the cost...throwing the actual off. Is there a different way to enter the cost in? -- Thank You Vince :) Vince, Part 1, I'm not sure, but I don't believe you can disable this feature. Part 2, I tried recreating your error, and when there was a quantity of 1 at \$0 already the...

average on Pivot table
i have a pivot table and when i hide some of the row titles, the ro total average calculate just the visibale cells. us there any way to change that?? -- Message posted from http://www.ExcelForum.com ...

2 Variable Graph
Hi all, I have a 2 variable graph, prices on left axis, interest rates on the right axis with years on the bottom. I would like to add 2 lines without having to draw them: 1) Average interst rate line. 2) Current interest rate line. How do i do this?? I've tried adding another series with the average entered into every year but then my actul interest rate line dissappears to sit at the bottom (i think it thinks it is a price) AHHHHHH Thanks Don't use one of Excel's built-in custom chart types. Make a new chart with all your data: prices, rates, average, and current. Dou...

Average for last 30 days
-- Thanks Ruth I think that to get a useful answer you need to provide a bit more than this:- How do we establish which data relate to the last 30 days? How are your data laid out, which columns? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Ruth" wrote: > > -- > Thanks > > Ruth sorry Mike I accidently posted it before i entered in the information I have reposted the question with some detail -- Thanks ...

Not getting the correct averages.
I am averaging columns with varying numbers (1-5). There are no blanks and no zeros. When I average some columns, the correct answer comes out with five decimal places. When I try to average other rows I get a number without decimal places (such as 4 or 5), but when I do the math physcially, I get the answer 4.01818 or 4.75... instead of 4 or 5. Why am I getting the wrong averages on some columns, I am using the same function (the Average function under the Sum button on the toolbar)? Please help me as there are many many numbers to average. Thank you, Lana In all likelihood, the cell...

average of a range, but only for the cells that contain values
Hello - Anyone familiar with a way to take an average of a range of cells, but only for the cells that contain values. So for example: 9 0 9 9 The average of the above four cells should produce an output of 9. Thanks for any suggestions. One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF(A1:A4<>0,A1:A4)) Or, if using XL07/08: =AVERAGEIF(A1:A4,"<>0") Note: Of course, 0 is a value. If its possible that 0 is a *valid* value, then you'll need to figure out a different solution. In article <48fff24b-518c-40b9-b5b9-c0283477b15b@56g2000...

Calculating averages on a form
I have a series of fields on a form, and wish to calculate the average value and display this in an unbound text box. I've calculated the value using the expression builder (e.g. default value=([field1]+[field2]+[field3])/3), but I'm having a series of problems: 1. How do I control how the value is displayed? Despite setting the decimal places property to 0, the value is showing with multiple decimal places. 2. How do I enable 'live' calculation? The value doesn't appear immediately (sometimes only after saving or clicking into a different database object). ...

last 4 wk average using calculated pivotitem
Hi, I am trying to create a pivot table/chart from data that is listed b week. So my table has "week" as the row field and "total X" as th column field. I want the chart to display the total for each week as bar (the easy part) and also to graph the average of the last fou weeks as a line on top of the bar graph. I have tried to figure ou how to add a calculated pivot item but I can't seem to get it to wor right. So for each week there would be a second almost subtotal lik entry that calculates the average for the previous four weeks (if ther aren't four previo...

Taking the Average of a Formula
How can I average the result of the following formula? =SUMPRODUCT(--(\$G\$15:\$G\$2500=\$M\$15:\$M\$2500),--(\$O\$15:\$O\$2500="e"),--(\$Y\$15:\$Y\$2500="e"),(\$N\$15:\$N\$2500)) Try this array formula** : =AVERAGE(IF((\$G\$15:\$G\$2500=\$M\$15:\$M\$2500)*(\$O\$15:\$O\$2500="e")*(\$Y\$15:\$Y\$2500="e"),\$N\$15:\$N\$2500)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "JimS" <jimx22@msn.com> wrote in message news:nl02h4d96qcv5mf61ee5ipnhf5fq2p2g66@4ax.com... > How can I average the re...

Pivot table Average with [h]:mm format
Is there a way to get the correct average in the Total Row or Column in a Pivot table when the value field is formatted with [h]:mm? The average works find in normal number format, but gives the Total rather then the average when formatted with [h]:mm. Using Excel 2007. Thanks Found the issue :) I am using a calculated field for the values, when I change this to the actual values, the average is calculated correctly. Still weird.. "Opa Horst" wrote: > Is there a way to get the correct average in the Total Row or Column in a > Pivot table when the value fi...

average multiple columns but skip a few columns
How can I average muliple columns that are not continuous? For example, I'd like to average A2 to Z2 but ignore the numbers in C2, F2 to H2? Thanks for the help =AVERAGE(A2:B2,D2:E2,G2,I2:Z2) HTH, Bernie MS Excel MVP "cpliu" <spamfreeliu@yahoo.com> wrote in message news:327da3d2-61f7-4e64-af17-9e49f85fa419@k19g2000yqg.googlegroups.com... > How can I average muliple columns that are not continuous? For > example, I'd like to average A2 to Z2 but ignore the numbers in C2, F2 > to H2? > > Thanks for the help On Dec 5, 7:04=A0pm, "Bernie Deitri...

calculating average days
I am trying to develop a report where in the PartNum footer the average number of days between orders is calculated. I have created the following: Table: tblItemsRecvd Fields: PartNum SupplrName DateOrdrd DateRecd AmtRecd Query: qryItemsRecvd based on tblItemsRcvd PartNum Criteria: [Enter PartNum:] SupplrName DateOrdrd DateRecd AmtRecd Report: rptItemsRecvd based on qryItemsRecvd I would like to calculate the average number of days between orders for the selected part number. Do I do it in the query (how?) or at the report level (how?)? I am using Acces...

Inventory transfers out should adjust average cost
We need a way to adjust the average cost of an inventory item after a PO is committed. The reason is that there seems to be no way to back out or correct the average cost of an item once a PO that has an erroneous dollor/quantity amount is committed. If TO's adjusted average cost, we could TO at the erroneous dollar/quantity and do a new PO at the correct dollar/quantity. ---------------- 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...

Average Cost Basis
Mutual Fund within a Brokerage Account, they apply the Capital Gains and Dividends earned by this Mutual Fund back into the cost hence the Average Cost keep on changing, and hence negating the FIFO etc. methods that are embedded in the program. Is there any way to adjust the COST on a individual stock/ Mutual fund ? Have been using MSMoney 10 yrs + and it is great, this is just a new wrinkle to look at. In microsoft.public.money, Bob W <Bob W@discussions.microsoft.com> wrote: >Mutual Fund within a Brokerage Account, they apply the Capital Gains and >Dividends earned by thi...

Calculating hourly average from large data set
I have a large set of traffic data (55000+ rows and 15 columns). Each row contains the data of one vehicle passing through a survey location. The columns include the time at which the vehicle passed the survey location, it's velocity, a property called headway etc... I would like to work out the average velocity and headway of the vehicles passing through the survey location for each hour (remembering that the number of vehicles and hence rows is different for every hour). Any ideas? P.S. Forgot to mention I am using Excel 2003... NRH - You can use the database fu...

overlay average & goal lines to bar graph w/out showing in legend
I have a 4-subject bar chart and want to overlay the goal (fixed number) and the average score (variable number) as a line onto the bar graph. The problem I have is that I must include the goal and avg score data in the bar chart in order to use the trendline feature. How do I include it without having it appear in the legend? You can include series on the chart and delete their display on the legend. "Violet Maven" <Violet Maven@discussions.microsoft.com> wrote in message news:4B22E136-4CAE-4A31-817D-29188D590BE0@microsoft.com... >I have a 4-subject bar chart and w...

Average IF #2
I want to average the amount of time someone spends on a specific date with customers. I have it average the person by user for the entire time (1 week), but I want to average it per day. I am looking to create a formula that looks at 2 columns (Column D has dates, Column I has Usernames). It should then calculate the average time spent if the date and username match. D E I R 3 07/18/07 09:30 beckerm 4 07/18/07 09:39 beckerm :09 5 07/18/07 09:46 beckerm :07 S21 T20 T21 (Average) 07/18/07 beckerm :08 Is there a...

Calculating Averages but excluding zero's
Hi, i want to calculate the average of a column but exclude anyzeros in that column. For example: If column A1 contained 2, 2, 0, 2, 2, 0 then the average would be 2. (8/4) NOT (8/6) Thanks. -- fodman ------------------------------------------------------------------------ fodman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31941 View this thread: http://www.excelforum.com/showthread.php?threadid=555103 Try this: =AVERAGE(IF(A1:A6>0,A1:A6)) Enter this as an array formula. Use CTRL-SHIFT-ENTER instead of just Enter. HTH, Elkar "fodman"...

plotted Average
i have a series of data as a line graph, i want my average to be plotted as well. the average is a single value based on the series not a moving average. i have put the average 'series' in and told the chart to plot empty cells but nothing has appeared except my single value average and it is not formatted the same as the actual series, i.e. the series is 0.895whatever and my average is 0.774589etc. but the series is showing up as a whole number but the average is not, i.e. series is 8.95whatever and average is still 0.774589 thanks anyone Can you give an example of the data as...

Bay chart error bars automation
Hi, Over the past month I developed an Excel add-in (which I can't share in its entirely for legal reasons, unfortunately). The most relevant code is pasted below my signature. What the relevant part of what it does: - Sorts the primary (input) worksheet by a column which represents the ID of survey panelists (there are multiple, varying numbers of observations/rows per respondent) - Uses the subtotal function to average values for that panelist on a number of "attributes", which are found in adjancent cells on each panelist's row(s) - It then creates a bar chart on...

Averages
Is there a formula by which when I have say e.g. four entries in a column on lines 31,32,33 & 34 I can obtain a average of these entries in line 35? If there is how do I arrange matters so that if I add entries above line 35 this line will automatically adjust itself to show a new average? try =SUM(A31:A99)/COUNTA(A31:A99) This formula is using data in column A and allows you to fill in values between rows 31 and 99 without needing to change theformula hope this helps -- tony h ------------------------------------------------------------------------ tony h's Profile: http...

Sort, select and average macro help
Please help! I have a report that is pulled regularly. It contains thirty day of data each time. Header row on line 11. Data starts on line 12. Column C & D are the identifiers of a particular sector. Each sector has 30 lines of data, one each for 30 days. Then the next sector starts. For each sector, I need to sort column E from greatest to least, then average the second thru the seventh value. In the data below, 1201A would be the first sector, 1201B is the second...I need to take each sector, sort the Indices greatest to least for that sector, ignore the first line of data ...

average of percentages between 1% and 100% in a column
Hello, I am trying to get the average of percentages within a column, but would like to exclude 0% and 100% from the formula. This is the formula I thought would work: {=AVERAGE(IF(AND(L1:L988>0,L1:988<100)))} (I read in an earlier post to hit ctrl+shift+enter to apply this type of formula) Thanks! Here is a way using sumproduct that does NOT need array entering =SUMPRODUCT((E1:E100>0)*(E1:E100<1)*E1:E100)/SUMPRODUCT((E1:E100>0)*(E1:E100 <1)) or array enter this =AVERAGE(IF((E1:E100>0)*(E1:E100<1),E1:E100)) -- Don Guillett SalesAid Software donaldb@281.com &qu...

How to convert Null values to zero when create an average query
This is my crosstab query Items Worked April 2010 March 2010 Average Reports 2 4 3 Tables 5 5 Files 2 2 2 As you can see, for "Tables" the query is giving me an average of "5" instead "2.5" because is not counting the null value date. How can I fix that? the SQL query so far is this: TRANSFORM Avg([Integrate Query].T...