Quartile Function

I would like to use a formula to return which quartile the given data array 
falls into-  that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%, 
etc.

The quartile function only returns the value of the various quartiles, while 
I need to know within which quartile the value falls.

Any help is appreciated.
0
tika528 (2)
3/15/2005 9:01:02 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
627 Views

Similar Articles

[PageSpeed] 8

On Tue, 15 Mar 2005 01:01:02 -0800, "tika528"
<tika528@discussions.microsoft.com> wrote:

>I would like to use a formula to return which quartile the given data array 
>falls into-  that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%, 
>etc.
>
>The quartile function only returns the value of the various quartiles, while 
>I need to know within which quartile the value falls.
>
>Any help is appreciated.


Perhaps:

 =FLOOR(A1,0.25)*4

will do what you describe?


--ron
0
ronrosenfeld (3122)
3/15/2005 11:25:34 AM
For a given observation in the data set,

   =SUMPRODUCT(--(obs>QUARTILE(allData,{1,2,3,4})))+1

should do it.

Jerry

tika528 wrote:

> I would like to use a formula to return which quartile the given data array 
> falls into-  that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%, 
> etc.
> 
> The quartile function only returns the value of the various quartiles, while 
> I need to know within which quartile the value falls.
> 
> Any help is appreciated.

0
post_a_reply (1395)
3/15/2005 12:39:24 PM
Hi Jerry, I tried this, but get a #name error-  it seems to not recognize the 
"obs" funciton.  Actually, I don't get this one either. Is it a special 
function from an add-in?

Thanks for your help.

"Jerry W. Lewis" wrote:

> For a given observation in the data set,
> 
>    =SUMPRODUCT(--(obs>QUARTILE(allData,{1,2,3,4})))+1
> 
> should do it.
> 
> Jerry
> 
> tika528 wrote:
> 
> > I would like to use a formula to return which quartile the given data array 
> > falls into-  that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%, 
> > etc.
> > 
> > The quartile function only returns the value of the various quartiles, while 
> > I need to know within which quartile the value falls.
> > 
> > Any help is appreciated.
> 
> 
0
tika528 (2)
3/16/2005 7:11:07 AM
"obs" is not a function, it is a place holder for your observation, just 
as "allData" is a place holder for the endire data set.  Replace the 
place holders with appropriate cell references, range names, or constants.

Jerry

tika528 wrote:

> Hi Jerry, I tried this, but get a #name error-  it seems to not recognize the 
> "obs" funciton.  Actually, I don't get this one either. Is it a special 
> function from an add-in?
> 
> Thanks for your help.
> 
> "Jerry W. Lewis" wrote:
> 
> 
>>For a given observation in the data set,
>>
>>   =SUMPRODUCT(--(obs>QUARTILE(allData,{1,2,3,4})))+1
>>
>>should do it.
>>
>>Jerry
>>
>>tika528 wrote:
>>
>>
>>>I would like to use a formula to return which quartile the given data array 
>>>falls into-  that is I would like to return 0,1,2,3,4. 0= min, 1=25%, 2=50%, 
>>>etc.
>>>
>>>The quartile function only returns the value of the various quartiles, while 
>>>I need to know within which quartile the value falls.
>>>
>>>Any help is appreciated.

0
post_a_reply (1395)
3/16/2005 1:11:03 PM
Reply:

Similar Artilces:

Vlookup function imbedded in the table_array value of a vlookup function
Hi, I am creating a go-karting championship management system for my a-level and I am having a few problems. Basically there are 6 races and They are set out as Race1,2,3,4,5,6 and the data for the drivers is entered under that. However, the spreadsheet is organised via the dates of the races which means that the data entered as race 1 could be for race 2,3,4,5. I have already got it to calculate this and it displays the date, the original race number and the race number after organisation from the dates of the races. Now, this is all hidden from the user in a hidden sheet and on the user...

Getting a cell address from Vlookup function in a macro?
Does anyone know how to get the cells address from a vlookup funtion in a macro, Im relatively new to excel and learning fast, but keep comin up against brick walls! What I have done, is a lookup sheet where i type in a number and if its found in a table in another sheet, it returns values from other columns, ie name/address, but i want to be able to select the name from my lookup sheet and mark it by painting the cell, the painting bit i can do, i just cant figure out how to get the cells address from the vlookup function, any ideas? ie, Sheets("Lookup").Select Range("G5&qu...

Member functions
In C++, member functions of a class (like other code) only take up space once, regardless of the number of objects of the class you create. Do this indicate that if having several objects of the same class created in separate threads, each member function of the class needs to be thread-safe? No matter it's static or not? Thank! garyolsen, > In C++, member functions of a class (like other code) only take up space > once, regardless of the number of objects of the class you create. > > Do this indicate that if having several objects of the same class created in > separa...

IF function #13
Hi all, Is it possible to have an IF function that deletes a row=20 if a condition is returned? I have a list of stock, with sub-totals throughout. I=20 want to delete all rows with sub-totals, so that i am only=20 left with the actual items. So, in the example below, i=20 would want to delete rows 3 and 5. A B=20 1 item1a =A35 2 item1b =A34 3 sub-total =A39 4 item2 =A32 5 item3a =A34 6 item3b =A36 7 sub-total =A310 the rows i want to delete, ll have sub-total written in=20 them. thanks for any help, Luc 6 Not possible with a fun...

What is the bar and function name for refresh all?
Does anyone have any suggestions on what the bar and function name is for refresh all from external links? I would like to code macro to refresh all from external links, Does anyone have any suggestions? Thanks in advance for any suggestions Eric Try something like: ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources -- Gary''s Student - gsnu201001 "Eric" wrote: > Does anyone have any suggestions on what the bar and function name is for > refresh all from external links? > I would like to code macro to refresh all from external li...

Data Validation in XL2007 suddenly stops functioning
I created a workbook in XL2003 that included some named ranges on Sheet2 that fed some data validation lists on Sheet1. Everything worked as expected. My colleague opened up the workbook in XL2007 and used it several times, and everything worked as expected. Then she called me, saying that it was broken. Of the 6 named ranges and related data validation columns on the other sheet, none of them were working anymore. I went over to her PC, and checked everything I could think of- I could set up working data validation test cells on Sheet2, but could not get the ones on Sheet1 to...

create pivot table formula without the GETPIVOTDATA function
Previous to Excel 2003 I could use a pivot table result in a formula and copy that formula as a relative reference. In Excel 2003 a GETPIVOTDATA fromula is created and uses an absolute reference. Is there a way in Excel 2003 to either make it arelative reference (without editing the formula) or revert to the method used in all prior releases? There are instructions here for adding the Generate GetPivotData button to a toolbar, and toggling the feature on and off: http://www.contextures.com/xlPivot06.html ebergkes wrote: > Previous to Excel 2003 I could use a pivot table resu...

DOES THE DAYS360 FUNCTION WORK?
UPGRADEED TO EXCEL 2003 Nomatter what I've tryed, the Days360 function gives me a ridiculus answer. for example, DAYS360(1/30/2003,1/30/2004) returns a zero, (i.e. 0), and no error message or other indication of any problem. Crazy, I say. After two hours of study, I think that Excel 2003 either can't handle this function, or provides incorrect instructions. (PS, I used to use this function with older versions of Excel. Any help will be appreciated. Thanks in advance, James Try =DAYS360("1/30/2003","1/30/2004") -- Regards, Peo Sjoblom (No private emails...

MATCH function #3
Hi All I am using the following formula in each row of column B =MATCH(A1, $C$1:$C$685,0) Col A Col B Col C 84048 #N/A 86316 86948 #N/A 86317 86960 #N/A 86319 86964 #N/A 86960 86970 #N/A 84048 87288 #N/A 86620 87291 #N/A 86622 You'll notice that item 1 and 3 in column A have matches in column C but the formula is not picking them out. I have used MATCH many times before without problem but this has got me stumped. Can anyone shed any light on problem? Many thanks Chris Barlow --- Message posted from http://www.ExcelForum.com/ Hi Chris! Could be unseen character...

Cut, copy & paste functions in Excel 2007 are extremely slow.
I just got a new Dell with Office 2007. Computer is a Vostro 430 with 4GB of RAM and a 2.8GHz processor. Cut, copy and paste operate extremely slow in Excel 2007, even if I only have one cell to cut, copy, or paste, with nothing else in the spreadsheet. The action is always completed, but may take a minute or more. It was this way when I first tried it, right out of the box. I suspect there is something set wrong. Notably, I'm having similar problems in Word 2007. Everything else on the computer that I've tried works great, including Publishing, other facets of Ex...

want to apply function to numerous cells
HI, Basically I want to divide a number of cells by a number in another cell. So say, A1 through A10 by C3. However, I do not want to make the change permanent, and I want to be able to see what the values would be if C3 changes. So apart from manually going thru and adding the = and /C3 is there a way to map that = xC3 to the original cells. (I know its a circular reference of sorts, but can i write a macro to perform that task for me.. or is there some other way?) Thanks try =a1/$c$3 and copy down to a10 -- Don Guillett SalesAid Software donaldb@281.com "ExcelQuestion" ...

convert the functions and formulas to values
Hi How to convert all the functions and formulas in the whole workbook to values without the method (copy, paste special as value) Thanks Why? Look at Value in the help for VBA Range("A1").Value = Range("A1").Value -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "Mireille Abi Nader" <mireillea@newhorizons.com.lb> wrote in message news:7D82E49E-8709-4930-B8D6-7ED311A1F8C9@microsoft.com... > Hi, > How to convert all the functions and formulas in the whole workbook to values without the method (copy, paste ...

Calendar date function
I would like to insert a calendar icon into a cell so that when I click on it, it will bring up a calendar from which I can then select the date that I want to appear in the cell. Hi John See http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin http://www.rondebruin.nl "John" <John@discussions.microsoft.com> wrote in message news:968D56F9-D1FA-4CB5-841E-94B51D717ED2@microsoft.com... >I would like to insert a calendar icon into a cell so that when I click on > it, it will bring up a calendar from which I can then select the date that > I > want to...

Is MSCRM3 provide some backup function to us?
Dear All, Now Because for some issues of my ActiveDirectory . Maybe the ActiveDirectory needs to re-setup But I don't know how to backup and recover the MSCRM. Because well knows that the MSCRM3 is combined with ActiveDirectory. And the user is from ActiveDirectory. So. How I can do it? Only backup the database? Seems not enough. Is any other software can be solve it? How about Veritas BackExec ? Any suggestion are appreciated for us. Thanks, Benny Ng You not only need to backup SQL you also need AD backups. Your sysadmin should be aware of how to do this. -- ...

How to add function to ie to parse html of current web page??
He there, i am looking for a way to add a function to ie7 or ie8 which parses the html code of the current web page and then copies parts of it to the clipboard so the data can be used via copy & paste. Any hints on how to add this kind of functionality would be highly appreciated. thx. Leon wrote: > He there, > > i am looking for a way to add a function to ie7 or ie8 which parses the > html code of the current web page and then copies parts of it to the > clipboard so the data can be used via copy & paste. > > Any hints on how to add ...

If Function Limitation
How can one have more than 7 If Functions nested, I have the need to create more than 7 If Functions, how can I go around this problem, I consulted the Help Section and it states: "Up to seven IF functions can be nested as value_if_true and value_if_false arguments" Your help is most appreciated! In practically every instance I run across where you need more than 7 IF statements, the problem can be solved by constructing a Lookup table of values to be matched and their corresponding return value. Example: Col_A Col_B Apple 1 Berry 100 Cat 11 Date 21 Eel 35 Fruit 106 Grate ...

Offset function to set ranges for charts
I used the OFFSET Function in Excel to create a dynamic range i.e. the range size changes. I checked the dynamic nature of the range being created by the OFFSET function and it works well. The problem is I am trying to use the cretaed range in a chart for plotting the specified data points and I am not being successful. If I enter the OFFSET function in the "Source Data" of the Chart, it is not accepted. I even went as far as to name the range refered to by the OFFSET function, but then I didn't know how to use a named range in a Chart's "Source Data". Ca...

BUG? internet explorer 8 with touchpad scrolling function
my laptop touchpad scrolling function does not work in IE8. is this some kind of BUG? i use samsung x10 laptop. synaptics driver 7.2.9 03Jan03 ---------------- 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 you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/communities/newsgroups/list/en-us/default.a...

Problem with MS Visual C++ 7.0 getline function
I get this error each time I use the getline function. I am using MS Visual C++ 7.0. error C2780 : 'std::basic_istream<_elem,_Traits>&std::getline(std::basic_istream<_Elem,_T= raits>&,std::basic_string>_Elem,_Traits,_Alloc>&,const _Elem)':3 arguments attendus - 2 fournis error C2780 : 'std::basic_istream<_elem,_Traits>&std::getline(std::basic_istream<_Elem,_T= raits>&,std::basic_string>_Elem,_Traits,_Alloc>&,const _Elem)':impossible de d=E9duire l'argument de mod=E8le de 'std::basic_istream<_Elem,_Traits>...

Index/Match Function
Hello All, Using Excel XP. I have two worksheets, one named "TIMES" and the other "HI&LO". On sheet "TIMES" I have a month & year in column A and a time in column B, e.g: A B ---------------------------------- 1 March 04 15:45 2 April 04 13:22 3 May 04 18:01 4 June 04 15:38 On Sheet "HI&LO" I have in column A the two Highest Times from sheet "TIMES", e.g: A B ----------------------------------------...

function pointer help!
I have a callback function void __stdcall CallBackFunc(int, int,double); And I need to have a pointer to it. void (*CallBack)(int,int,double); but the assignment doesn't work... CallBack = CallBackFunc; What am I doing wrong? Thanks as usual! Ron H. ----------------- www.Newsgroup-Binaries.com - *Completion*Retention*Speed* Access your favorite newsgroups from home or on the road ----------------- Try this instead void (_stdcall *CallBack)(int,int,double); AliR. "Ron H." <rnh@mmm.com> wrote in message news:4739f338$0$7517$8d2e0cab@news.newsgroup-binaries.co...

Auto Correct Function Question
My company is using an Access based program. We use an Auto Correct file (*.acl) to post entries: e.g. TT = TELEPHONE TO, CW = CONFERENCE WITH. The problem is that we want these codes to function only when using Access. People are upset when using Outlook or Word and they type in letter combinations and get posting code entries. No one wants to have to keep turning the "replace text as you type" function on and off as we change MS programs. Can I shut off the AutoCorrect function "replace text as you type" in all but Access? Alternatively, is there a way...

Function help needed urgently!!! #2
Hi anyone who can help... I have two sets of figures 1 is sales and 1 is target. Eg: Sales Target Incentive 5 5 In the Incentive column I want a formula that will calculate the Incentive payment for employees who exceed there target. The easy part is the if statement for example 10% of their sales if they exceed target: =if(sales>target,Sales*10%,0) The complicated part comes in to play when only when they hit multiples of 10 are they entitled to an incentive: If the Sales are 5 and the Target is 5 - no incentive. If the Sales are 10 and the Target is 5 - no incenti...

Converting MSXML2 to System.Xml: transformNodeToObject functionality?
Hello. I'm attempting to convert an XML wrapper class from VB6 (using MSXML2) to .NET (using System.Xml). For my TransformDocument method, the MSXML2 code uses the transformNodeToObject method for XSL transformation, using DOMDocument30 objects for the target and both parameters. In System.Xml, it appears that I must use the Xsl.XslCompiledTransform.Transform method. This is much more complex for internal object references, and it appears that in using the new method to get back an XmlDocument object, I must through an intermediate text file or stream, which seems quite inefficient to ...

embed function parameters/doc
The Embed function is incredibly undocumented. I'm trying to find out how it works. I understand the first param is the clsid. That's all I know. Any good documentation out there? Thank you ...