Ignore Zero's - Percentile

Hi

I have used the following to get the average value from a set of data whilst 
ignoring zero values within the data set.

=SUM(K4:AP4)/COUNTIF(K4:AP4,">0")

I would like to use the percentile command on the same set of data. Is there 
a way that I can use Percentile and also ignore zero values ?
Many Thanks
Chris
0
Utf
2/18/2010 3:15:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
4700 Views

Similar Articles

[PageSpeed] 7

Hi,

with an ARRAY formula

=PERCENTILE(IF(K4:AP4>0,K4:AP4),0.1)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Chris26" wrote:

> Hi
> 
> I have used the following to get the average value from a set of data whilst 
> ignoring zero values within the data set.
> 
> =SUM(K4:AP4)/COUNTIF(K4:AP4,">0")
> 
> I would like to use the percentile command on the same set of data. Is there 
> a way that I can use Percentile and also ignore zero values ?
> Many Thanks
> Chris
0
Utf
2/18/2010 3:28:09 PM
Reply:

Similar Artilces:

query export wont allow a zero value?
I runa query and forcing a specific value with a leading zero. i.e. 011210 When I run the export the first zero is eliminated. 11210 Any ideas why this happens? When ever I change the zero to a 1 or 2 works perfectly. The query always shows the correct value but the export text file is always off. Any ideas why? Thank you Is the field a number or text? Sometimes, if it is text it will drop leading zeros. -- Milton Purdy ACCESS State of Arkansas "Vadimbar" wrote: > I runa query and forcing a specific value with a leading zero. > i.e. 011210 &...

Leading Zeros Lost in Concatenation
I'm working on creating a database that pulls data from three fields and concatenates the results into a fourth. http://s22.quicksharing.com/v/5121074/ScreenShot002.png.html I'm almost there, I suspect, but the first field from which I pull has a leading zero that's not carrying over into the concatenated field. http://s15.quicksharing.com/v/8550436/ScreenShot001.png.html. What would you advise to fix this? Thanks, Jim I'm guessing that the Figure Identifier field is a numeric field, and it has some custom formatting to give it the leading zeros. If that is the case, ...

Show zero values in specific area
I know it is possible to show zero values in a sheet by ticking "zero values" in options, but I have a situation where zero values should only be shown in certain areas of the sheet. I've tried setting formatting to text and this displays zero values entered, but it plays havoc with the data validation (Decimal greater than or equal to 0). Is there any way I can retain operation of the data validation, but still display zero values in a specific range? -- Ian -- The only way I can think of is to use the option "show zero values"; then in th...

dropping leading zeros on import
I have a csv file that has leading zeros for zip codes. I want to be able to open the file without manually switching from General format to Text format every time it is opened. Ay suggestions? change the extension to .txt, then turn on the macro recorder and open it manually. Make the appropriate setting for the column (treat it as text) in the wizard and complete importing it. Now you can use this code to bring in the file. You can rename it in the code, use openText with the recorded settings. If you leave it named with .csv extension, your settings will be ignored (at least through x...

Getting a average value without including zeros
Hello, This is kind of complicated so I will try to explain this the best I can. Lets say I have a bunch of numbers with zero's in columns c2,d2,e2 and this has 407 rows. (Maybe this is the correct way of saying it =sheet2!c$2:$e$407) From this I would like that it calculates the average for me but I don't want the zeros to be included in the average just all the other numbers. (See example below) test test1 test2 test3 test4 Avg. 2/12/04 0 2/12/04 5 2/12/04 100 2/12/04 ...

please ignore last msg
have worked out how to do it so not to worry!! ...

Junk filter ignores rules
I am a member of some mailing lists and I'm finding some of those messages end up in the "Junk mail" folder. I have rules that sort out the mailing lists into their own folders, but these still end up in the junk mail folder, it appears that the junk filter runs before the rules. Is that true? If so, is there a way to change it. I am running Outlook 2003 connecting to exchange server 2003 - both are completely updated. ...

Ignore deductions linked to Direct Deposit to calc Net Pay
When an employee has a deduction linked to direct deposit and another deduction that is a percentage of net pay (ie. garnishment of wages), the amount of the second deduction is less than it should be because the direct deposit deduction is reducing 'net pay.' Since the direct deposit deduction really IS net pay, other deductions should be based on the net pay before deductions linked to Direct deposit. ---------------- 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&qu...

ignore
test Unfortunately, programs counting unanswered posts don't make a distinction that something should be ignored. We see the post whether we want to or not. Hope this is leading to something good. none wrote: > test > tt2 "@(none)" wrote > test And what's wrong with microsoft.public.test? Jordon ...

SmartList report showing zero records
My custom made smartlist report is not showing any records, if I login as "sa" user it is fine but if I login through other users I am not able to view any records. I have tried all the basic things which are needed to show the smartlist report like granting access rights. I have followed Marianos steps too. http://dynamicsgpblogster.blogspot.com/2008/12/smartlist-builder-security-for-sql.html Anything else I am missing out in the configuration? Thanks Leo You need Smartlist Builder to be installed for the user PC you're trying to access. -- Thanks Janakiram M.P. MCP...

Leading Zeros #2
I need a way to leave leading zeros in a column in a worksheet that is being saved as a .csv file. I save the file as a csv, then when I open it back up instead of having say 002 it just have 2. The cells are formatted as text. Eric, It probably is writing the zeroes. If you're reopening the csv file in Excel, that's probably when the zeroes are getting dropped. Try opening it with NotePad, or any text editor. It will show you exactly what's there, character for character. If you determine that it isn't writing it correctly, then instead of saving as csv, try the ...

Getting Rid of Zeros
s20 u20 v20 1 2 3 0 0 4 5 9 0 0 The following formula sums s20+u20 in v20: =IF(S20="","",SUM(S20+U20)) However, in s21,u21, s22, u22, s23, u23, s25, u25, s26, u26, etc. the value calculated is "false". When those rows are sumed it returns a zero in the V column. I've used conditional formatting to get rid of the falses. I want to g...

Avoiding empty cells showing as zero in line chart without na()
I'm using a line graph to show trends in a multi-question questionnaire. Like many else (I've done a lot of searching but haven't found a suitable solution) my line graph jumps to zero for the empty cells (currently defined as ""). I understand I could just change this to na() instead but then my average() calculation stops working. I have a lot of data so making two separate data-series is not feasible. Can anyone help me with a suggestion here? Thanks! It's not feasible to have parallel data ranges? It's more feasible to spend lots of time trying to workarou...

pfmigrate.wsh script detects zero folders Windows 2000 to Windows 2003 migration
Hi, Please excuse the cross posting. I'm hoping someone will answer this question. I've seen a few others ask it, but no answers to be found. We're planning a migration from Exchange 2000 server (in active/passive 2 node cluster) to a single Exchange 2003 cluster. I've successfully setup an Exchange 2003 server withing the organization. I'm planning on using the move mailbox/public folder method as opposed to rolling upgrade etc within a cluster. Besides, we're moving away from clustering. I'm attempting to replicate public folders now using the latest pfmigrate...

ignore dup key warning
Is it possible to suppress the warning that is generated when you have an index with IGNORE_DUP_KEY and you try to insert duplicate records? The warning is causing our jobs, such as DTS, to fail because it's not a success code. Thanks, Andre If you are using SQL Server 2000 then I do not think there is a way to suppress the warning. On SQL Server 2005/2008 you can use TRY/CATCH to trap it. However, the real solution is to avoid using IGNORE_DUP_KEY and to properly code your SQL code to avoid duplicates (for example, using a predicate with NOT EXISTS to insert only ke...

Outlook 2002 SP3 -> Tasks -> Custom Filter -> Advanced
I just have realized, that in Outlook 2002 SP3 (10.5709.6714) we have issues with Custom Task views/filters and conditions/values. If we create the condition/value "between"/"last year and next friday", after applying the view, the value will appear in a date/time format rather then a string format ("last year and next friday"). Besides, the value will not be adapted dynamically by the time. Here's an example, this weeks thursday it should appear as ("http://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/81040040" >= '...

sequential numbering of zero leading values
I have a value with leading zeros that is 18 digits, for example 001001001001001001. How do I create a list of sequential numbers for this value for example: 001001001001001001 001001001001001002 001001001001001003 etc.... etc.... If your first number is in cell A1 (which you have to enter as text as excel does not recognise numbers more than 15 characters) Then copy the following formula in A2, and drag down: =LEFT(A1,LEN(A1)-4)&INT(RIGHT(A1,4))+1 Mangesh "nagorb" <nagorb@discussions.microsoft.com> wrote in message news:A63F3446-AB19-4A8E-88C5-FEC2BD4FDA0C@microsoft...

A Percentile IF function
Hi i was running if there was a percentile IF function or a way to run something similar to an average if function. Lets say I have a data set like below a 6 b 1 b 2 b 3 a 4 b 5 a 10 c 12 What I want to achieve is to find the percentile of all data points that are taged "a". The average if function for this data looks like =AVERAGEIF(D1:D8,"a",E1:E8) So ideally I want to be able to have a =PERCENTILEIF(D1:D8,"a",E1:E8), sorting column d is no appropriare as different filters are constantly applied to this data set. Potentially I would want to ...

Why does CRM resource planning ignore Outlook items?
When i plan a new serive activity or an appointment, CRM ignores items that are already in Outlook/Exchange. Can CRM be configured so that it does not schedule double appointments? I figured it out. The good answer is: CRM will not ignore items in Outlook/Exchange, if the users has tracked their own appointments with the "Track in CRM"-button. "Hans Nietsze" wrote: > When i plan a new serive activity or an appointment, CRM ignores items that > are already in Outlook/Exchange. Can CRM be configured so that it does not > schedule double appointments? ...

Ignore Blank cells
I have a data series of fixed cells for product categories (eg. a,b,c,d,e,f) which will not always have values relating to them, but are required to retrieve data from different customers. Is it possible to get a chart to ignore the data series if the value cells are blank. This will hopefully allow for data retrieval from multiple customers, but charts will be created with only relevant data. Thanks in advance Derek You might try sorting the chart data before creating a chart, sortin first on the cell(s) that may be empty, then by your primary key -- crispb -----------------------...

Zero problem with blank cell after automated data transfer
Hi, (Excel 2003) I have a workbook where sheet 1 is called 'Raw data'. Sheets 2 to 13 are then called 'January' through to 'December'. I use the following formulas to auto-transfer rows of data from sheet 1 'Raw data' into sheets 2-13 'January' - 'December' depending on a date cell within ‘Raw data’: In sheet 1 ‘Raw data’: =IF($B3="","",IF(TEXT($B3,"mmmm")=EK$2,ROW(),"")) In sheets 2-13 ‘January’ to ‘December’: =IF(ROWS($2:2)>COUNT(OFFSET('Raw data'!$EJ:$EJ,,MATCH(WSN,'Raw data'!$EK$...

Office 2003 and 2007 Word ignores certain spelling errors
I checked both versions (2003 and 2007) of Office Word to see if they recognized specific spelling errors. Spell checker seems to ignore any word that has certain characters embedded, like any number (0-9), asterisk, parenthesis, right brace, backslash, or left-arrow (shift apostrophe). When I'm banging along and I unintentionally hit a numeric key within a word, spell checker doesn't recognize the error. Why? Is there a fix for this? There's an option (in Word 2003, Tools > Options > Spelling & Grammar; in Word 2007, Office button > Word Options >...

ignore negitive numbers
I am making up a mortgage sheet. I have all the formulas inserted and it all works great except when i want to make up a payment shedule. I made up the first line of the shedule and then draged the fill down till the last balance reached zero. Now my problem is when I want to redo the sheet to use different numbers then i could end up with negitive numbers on the sheet. I was thinking one way of dealing with this is to have excell hide and ignore negitive numbers. Is this possible? I am using the sum formula to sum up a column for total payments and the same thing to total interest...

How to suppress zeros
Hi, I have data that includes zero values. How do I stop these zero values being plotted on a chart? Thanks Jonathan Jonathan - You can replace the zeros by blanks, if they are not the result of a formula in the cell. This means clear the cell, select it and press Delete. "" won't do it, because it isn't a blank, it's a rather short text string. If the 0 is from a formula, replace it by NA(), which gives you the #N/A error in the worksheet. the formula will have to look like this: =IF(<something>=0,NA(),<something>) If the error values in the cells ...

Force a blank cell to zero
How can I force a blank cell to equal 0 in a column -- Ro ----------------------------------------------------------------------- Roz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2974 View this thread: http://www.excelforum.com/showthread.php?threadid=49529 Roz Blank cells always equal 0. To prove it type =A1 in a cell, if A1 is blank it will return 0. Regards JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261 View this th...