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!
0
anonymous (74722)
4/2/2004 4:44:47 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
337 Views

Similar Articles

[PageSpeed] 16

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
"SteveMac" <anonymous@discussions.microsoft.com> wrote in message
news:12ee501c418d1$cf460300$a001280a@phx.gbl...
> 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!


0
Don
4/2/2004 5:09:42 PM
Reply:

Similar Artilces:

Column Report (Right to Left)
Good Evening. I have a report that I have finally managed to arrange such that I can print landscape on the front page and Portrait on the back. I have one problem left.... The back page has a columnar subreport in the lower fifths of the page. The subreport functions fine except i need it to fill from right to left. The data is in the proper order but the report fills from left to right? How can I fix this? Thank You Tirelle ...

Edit a column of names
Hi I have a worksheet with about 300 names in a column The names are laid out like Adrain Mr R. or Buchanan Mr & Mrs T. etc etc Is there some way to remove the titles and just leave the Surname without have to do it a line at-a-time? Martin ���� Martin, In your worksheet, make sure you have blank columns to the right of the column that contains the names. Highlight the names. Click the "Data" menu item. Click "Text to Columns...". In the pup-up wizard, select the type of data as "Delimited". Click the "Next" button. Remove the check mark fro...

Upgrade MSPOS 1.0 to RMS Store Ops 2.0
Does anyone know of a third party utility to convert the MSPOS db to an RMS db? I have a client that wants to do this. Thanks, Don Check Retail Realm's import utility - I believe it can import items, customers AND history.... "DonInEP" wrote: > Does anyone know of a third party utility to convert the MSPOS db to an RMS > db? I have a client that wants to do this. > > Thanks, > Don Yes - Retail Realm's Import Utility has a finction especially for MS POS where all thedata from MS POS is imported to a new RMS database. Afshin Alikhani - [ afshin@r...

How do you Find Number of Columns when a column label = ""?
I have a row of column strings. To find the number of columns in row 1 I would use: Maxcol = sheets(1).Range("A1").End(xlToRight).Column However, if one of the column labels before the end is "" (empty) then the Maxcol stops there. How do you find the true number of columns if some of the column labels are "" but the last column label in the row exists? Do you have to loop through all 256 possible columns in a for loop checking for the end or is there a simpler way? Thanks for any help. Dennis One way: With Sheets(1).Cells(1, Columns.Count) ...

percentage weight
I am teacher trying to make a grading program that can give a different percentage weight for various tasks and account for missing marks from students who are absent for an assignment. I am using this formula that I found from some other excel genius: =SUMPRODUCT($D$3:$X$3,D17:X17)/SUMIF(D17:X17,"<>",$D$3:$X$3). This works fine if there isn't a formula in D17:X17 like a test result average formula e.g. D7/D2. This seems to screw up the formula. Any ideas? Here is an example of what I mean: A B C D E 1 total points out...

How to paste data to alternating cells down a column?
I am copying data from a column in one sheet where the entries run concurent down the column. When I paste to the new sheet I need to paste the information in every other cell down the column. What is the best way to do this, I have 3600 entries copied in groups of 25 to 72 different sheets, each sheet holds 50 entries? One extraction technique which should serve you well .. Assume you have source data in Sheet1, running in A2 down In another sheet, In any starting cell, say in B2: =IF(MOD(ROWS($1:1)-1,2)=1,"",OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/2),MOD(ROWS($1:1)-1,...

Percentages in Pivotal
I have a pivot table I want to do percentage of total on: Column 1 Column2 Ice Cream 32 Cake 50 Total 82 I want to add a third column that says 32 is 39% of the total 82. And 50 is 61% of the total. The system should be able to calculate this. I tried to do a search but couldn't get anything to come back. EXCEL 2007 In your, "value column" you can right click in the Column Name / Value Field Settings / Show values as tab / change the Show values as field to % of column / OK Not quite what you want. If my comme...

file names and path. How text to columns from right to left
I have an export file that is a list of file names Some times the name is 3 deep sometimes more: O:\Shared\700 Club\700 Club Information.doc O:\Shared\Accounting-IT\Invoices\Pack America\Pack America - Glen Tyler.doc I am need to get these in columns so that the file names all line up. So...I need to delimited starting from the right with the \ as the deliminator. My data is in column A. I would probably start 10 columns over pasting the first data (file name) as I do not know how deep the deepest file path is. Is there a way to do this??? Your description is not completely c...

Converting WordPerfect 5.1 file to Word
Can this be done. If so, how. I would really like to preserve and use my extensive WordPerfect documents in Word. Thanks. try the word newsgroup... >-----Original Message----- >Can this be done. If so, how. I would really like to >preserve and use my extensive WordPerfect documents in >Word. Thanks. >. > ...

XL2K: Z origin (floor) on column charts defaults to non-zero for certain datasets
I've noticed an extremely frustrating, not to mention misleading, aspect of Excel 2K. If you have a column chart with a series whose values are all considerably greater than zero, the "autoscale" feature will by default cut off the floor of the graph at some value greater than zero. Charting textbooks abound with comments as to how this is a very misleading style, because it tends to exaggerate differences between points which might actually be relatively quite small. Someone who's not looking at the chart with a sharp eye might conclude that 2 points differed signific...

How reverse the order of columns?
I have made a table with orientation from right to left. Now I want to change the direction, in other words I want to reverse the direction of table. Is there an easy way to do this job. Copy/special paste/ transpose doesn't work in this case. -- Rasoul Khoshravan Azar Kobe University, Kobe, Japan Think you could use a helper row, then select the range and do the Data > Sort > Options > Sort left to right under "Orientation". Eg Insert a new row1, fill the numbers: 1,2,3... across your cols range. Then select the cols range, click Data > Sort > Options. Che...

Counting a Column
I am trying to do a count of a column....problem is....I want to count whether the cell is shaded in green and if there is a date in the cell as well. so if a cell is shaded in green and has a date, that would count as one. Any ideas? What does the green shade mean? Why is a cell shaded green? If the cells are shaded green using conditional formatting then you can use the same logic that applies the cf to also count those cells. -- Biff Microsoft Excel MVP "dwake" <dwake@discussions.microsoft.com> wrote in message news:AF9A7986-2412-4B0D-9606-FAF55BCA...

Cannot digitally sign after Office 2003 Service Pack 1
After I installed the Office 2003 Service Pack 1, each time I want to send a digitally signed message from Outlook, the following error message appears: "An error occurred in the underlying security system", and I cannot send the message. Will appreciate any ideas as to why this happens. Have you tried removing/reinstalling the certificate? /neo ps - you can't salvage the existing certificate in the store since it sounds like it is corrupt. you need to reinstall the certificate from the original crt/pfx package you received. "Jerry" <duncan_jeremy@hotmail....

Sorting 2 columns
I have data in C5:H43 and J5:O47 and Row 4 being the header row for both league tables as Column H and O are the result fields I need to be able to sort them together so C5:H5 has 1st Place and J47:O47 last place. I hope this makes sense. Several questions: What data do you have in your columns? Is C5:H43 independent of J5:O47? Do you want to sort C5:H43 from high to low? Do you want to sort J5:O47 from low to high? "Rugby Al" <RugbyAl@discussions.microsoft.com> wrote in message news:9F8FEFEA-7339-4210-B7EA-B4B0431455F9@microsoft.com... >I have data in C5:H43 and J5:O...

combination charts
I wish to create a combination bar/line chart using five columns of data - four to be bar on the primary y axis and one to be line on the secondary y axis. The chart wizard defaults to three bar and two line. Double clicking on the line chart in question brings up the format data series dialog box and allows me to change from seconary to primary axis. However this is still a line chart and not a bar chart. Can anyone help? Hi, You can right click the line series and from the context menu pick Chart Type and select the bar chart type that you want. With combination charts it is normall...

Rank column with changing # of rows
Is there a formula that will Rank even when rows are added and deleted? Currently I am using =RANK(C8,C8:C43) but when I add or delete rows it throws everything off. Please help :-) To modify the formula, you need to tell us what you want to happen to the formula when you add or delete rows. On Mon, 5 Jul 2004 07:10:01 -0700, Amy <Amy@discussions.microsoft.com> wrote: >Is there a formula that will Rank even when rows are added and deleted? Currently I am using =RANK(C8,C8:C43) but when I add or delete rows it throws everything off. Please help :-) ...

1/17 q2
How do I? How do you do what? On Mon, 17 Jan 2005 14:11:41 -0800, "Jerry de Raad" <a-jerrdr@titanium.selfhost.corp.microsoft.com> wrote: >How do I? > By asking a specific, sensible question? -- HTH Bob Phillips "Jerry de Raad" <a-jerrdr@titanium.selfhost.corp.microsoft.com> wrote in message news:ey9NJGO$EHA.2700@TK2MSFTNGP14.phx.gbl... > How do I? > > ...

Back to "A" column upon pressing "Enter"
Something happened with Excel at my work and it no longer does this. Now when I press Enter it just goes down one row instead of down one row and back over to the "A" column. Does anyone know how to get it to go down a row and all the way back to the "A" column upon pressing Enter? Hope that makes sense. Thx! If you use the Tab key to move across a row then hit the ENTER key you will go back to start column one row down. Gord Dibben MS Excel MVP On Tue, 03 Jun 2008 08:00:13 -0500, ��������� <none@none.net> wrote: >Something happened with Excel at my work...

Panasonic NN-SD697S 1300-Watt 1-1/5-Cubic-Foot Stainless-Steel Microwave Oven
List Price:$169.99 Image: http://bestdeallocator.info/image.php?id=B001MZVWA4 Best price found: http://bestdeallocator.info/index.php?id=B001MZVWA4 Panasonic 1-1/5 cu feet stainless steel microwave, One-touch sensor cook and reheat, 1300-watt of high power, inverter technology, Inverter turbo defrost, pop-out dial with flat panel for easy programming, 13 1/2-inch turntable, 4-digit readout, reflective mirror door, 10 level power settings, 9 menu sensor settings, interactive mutli-lingual screen, more/less control feature, keep warm feature, popcorn key, quick minute, delay start and tim...

ON 2007 resizing column widths
i can't resize table column widths as i want. some i can make narrow, others are wide and can't be made narrower. in fact, the whole table has limit on how narrow it can be. what is the secret to freeing up column width adjustments? -- jeffrey --- news://freenews.netfront.net/ - complaints: news@netfront.net --- ...

Excel column sum of negative and postive numbers.
If I have a column with negative and positive numbers, can I get separate sums of the negative numbers and the positive numbers? How do I tell Excel to add just the postive signed numbers? =SUMIF(A1:A20,">=0") for the positive numbers -- Greetings from New Zealand Bill K "Ike Milligan" <info@1accordion.net> wrote in message news:omZ%f.2421$An2.951@newsread2.news.pas.earthlink.net... > If I have a column with negative and positive numbers, can I get separate > sums of the negative numbers and the positive numbers? How do I tell Excel > to add just t...

How do I create 1 chart from 2 different sheets?
Using Excel 2003 how do I create one pie chart by using data from 2 different sheets? all of the examples i see are created by clicking and dragging data from one sheet. Jon Peltier has instructions for charting data from different sheets: http://www.peltiertech.com/Excel/ChartsHowTo/ChartDataFromDifferentSheets.html tclbeach wrote: > Using Excel 2003 how do I create one pie chart by using data from 2 different > sheets? all of the examples i see are created by clicking and dragging data > from one sheet. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.cont...

Put one Column behind another
I would like to be able to make it so that the "Total" column for each month's inspection be behind the "Pass" column so that I can show the pass/fail ammounts. This is the data I have: 3rd Q Jul-08 Jul-08 Aug-08 Aug-08 Sep-08 Sep-08 Insp. Total Pass Total Pass Total Pass Q 12 10 12 9 12 6 P 21 14 21 12 21 19 S 32 18 32 28 32 25 E 2 2 0 0 0 0 For example, Inspection Q had a total of 12 inspections performed, and 10 of those passed. If you place the Pass column in front of the total column you would be able to see the difference (2) overlapping. Is there a way ...

Conditional sum of columns
If I have 1 row with 5 columns of data, how do I conditionally sum the columns based on whether the column is greater than a value in another separate cell? For example, A B C D E 5 32 16 22 6 89 6 7 3 I only want to sum A5:E5 if the column is greater than the contents of A7. In this example, the answer would be (6+89)=95. If A7=1, then the answer would be (16+22+6+89)=133. =sumif(A1:E1,">"&A7) "randys" wrote: > If I have 1 row with 5 columns of data, how do I conditionally sum the > columns b...

Microsoft Office v. X 10.1.5 Update
Good evening MVPs Checking the Microsoft Mactopia page I just found the new Office X update 10.1.5. It has great news for all Office Users: "...This update addresses several stability issues with PowerPoint�, Excel, and Visual Basic for Applications for Office�v.�X. ..." But it also has a note that sounds more like ... You judge: "... Note If you have not installed Microsoft Office v. X 10.1.2 Update and Microsoft Exchange Update for Entourage� X (Office v. X 10.1.4 Update), you must do so before installing Microsoft Office v. X 10.1.5 Update. ..." I have all the update...