# Percentiles Against Each Concatenated Grade And Region

Oct 28, 2008

I have a dataset (20,000rows) with Grade, Region and Salary. I need to calculate the 25, 50, 70 and 90 percentiles against each concatenated Grade and Region.

Jul 10, 2013

I am compiling a master spreadsheet to analyse progress of students. I have their potential grades in one column and their latest progress grade in another.

I would like an automatic look up of the potential to the progress grade so that if their current grade is BELOW their potential that gets highlighted one colour, if it is above, another colour.

One issue is that the grades are all letters (S, A, B, C, etc.) and that some potential grades are 'dual' (i.e. A/B, S/A) which complicates the issue somewhat.

Jun 13, 2006

my Excel teacher doesn't even know how to do this! The chapter is Working with Multiple Worksheets and Workbooks, and we went into linking workbooks, linking worksheets, using the VLOOKUP function, and saving workbooks as templates. This is Case Problem 2 and i'll attaching the file.

It says:

1. On the Grading Criteria worksheet, enter the text "Exam Average" in cell A1
and the text "Grade" in cell B1.

2. In the range A2:B6, create a lookup table for the range of grades specified by Professor Templeton. (Hint: Each letter grade should be matched up with the lowest score possible for that grade.)

0 to 49 = F
50 to 59 = D
60 to 74 = C
75 to 89 = B
90 to 100 = A

3. Insert a formula in Column E of each worksheet to calculate the overall score of each student based on the grade table you entered in the Grading Criteria worksheet.

Mar 30, 2008

I have a range of cells C11:C29,G12:G18,G20:G23,G25:G26,G28:G29,C33:C42,G33:G42,C46:C47,G46:G47,C51:C54,G51:G59,C58:C59 that contains letter grades (egs. A+,C- etc). I need the code for a macro that checks each cell from this range and if the value of the cell is A+ then the cell's value changes to 100, if the cell's value is A then change the cell's value to 98, if cell's value is B+ then change to 88 ...and so on...you get the idea (i will complete the rest of conditions but i just need the basic code).

## Calculating Percentiles

Dec 5, 2008

For my Intro to Engineering class, I collected data of two filter media in the laboratory. The data included the % of the media which passed through a sieve and the sieve openings. For use in a certain equation, I need the opening size at the 10th, 30th, 50th, 70th and 90th percentiles. I've tried searching on the Microsoft website but I don't think the PERCENTILE function will work correctly with the given data. I also tried using the slope between points to find the opening sizes at the percentiles mentioned, but doing so yielded different results from my professor's. Attached is the data I collected organized in tables and graphs of the data. I have also attached a pdf file of an example which my professor has given us.

## Creating Percentiles

Jun 14, 2005

I am going to be gathering some raw test results soon, and I need to create a percentile for this particular group of results. way to do this in Excel? Or at least point me in the right direction? :o

## Calculate Different Percentiles Based On Different Grades

Jun 3, 2009

Need to calculate different percentiles based on different grades. how do I put an 'if' loop or anything else that will work for this.

B23
B22
B35
B36
B48
B49
B510
B511
B615
B23
B37
B614

Need function for the table below
Percentile10%25%50%60%75%90%
B2
B3
B4
B5

## Calculating Percentiles Based On Known Data

Jul 15, 2009

Is there a function in Excel that would calculate the 85th & 90th percentiles based on the 50th & 75th percentiles.

## Calculating Percentiles Of A Selected Range

Apr 12, 2014

What I need is the following: I select a range of numbers and for this range I want to calculate the 1/3th and 2/3th percentile, and these should come in two cells that I can easily change in the vba code.

Some formula in which I can give two inputs: first the range of which it has to calculate the 1/3th and 2/3th percentile and a second input which is just a number and than the vba should say whether that second input is in the first range (between 0 and the 1/3th percentile value) or in the second range (between 1/3th and 2/3th percentile values) or third range (between2/3th percentile value and highest value).

## Current Region Through A Function?

Feb 19, 2014

I am trying to get the address of Current Region through a Function call. The function only returns the address of the cell, not current region. Isn't it possible to use Current Region within a function? It works for a Subroutine. I have attached the examples in a file.

## Using Current.region Less 1 Column

Mar 30, 2009

How can I use this code but tell it to select all but the right-most column? I have this piece of code...

## Plot By Region Chart

Jan 1, 2008

I am trying to figure out the best way to plot this data -- For each region, I want to plot December 2006 snowfall against December 2007 snowfall. So starting on row 12 to row 41 (in the attached file). First challenge: the dates are not aligned because it is using the retail calendar where 12/3/2006 is compared against 12/2/2007. How can I plot the snow depth for the Northeast for example? I can't just use a dual axis chart because there are also dual dates...

## Different Method For Finding Variable Region

Jan 2, 2009

I have the following

## Assigning Current Region To A Variable

Dec 7, 2009

I’m attempting designate the cell which will then determine the start of the current region, to be copied and pasted to another sheet. I’m receiving an “ERROR 1004” , Method Range of object_ Worksheet Failed.

## Trying To Select Current Region Of Name I Choose

Dec 4, 2006

I get an error 1004 with this
Sub SelectCurrentRegion()
Cells.Find(What:=InputBox("Enter the sheet name to delete"), After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
Range("ActiveCell.Value").CurrentRegion.Select
End Sub
I can put Range("G2").CurrentRegion.Select and it works fine, but I need it to enter the value from the earlier find.

## How To Count The Region By Date Wise

Feb 8, 2010

how to count the region by date wise,

For Example: Below 2 columns there are four dates available,
i want to know the count for date 1/1/2010, how many UK?, IND?, US?.
As per the below format....

Date UK IND US
1/1/2010---
1/2/2010---
1/3/2010---
1/4/2010---

Date Region
1/1/2010UK
1/1/2010IND
1/1/2010UK
1/1/2010UK
1/1/2010IND
1/1/2010UK
1/1/2010UK
1/2/2010IND
1/2/2010UK
1/2/2010US
1/2/2010US

## Macro: Name Current Region Or CurrentRegion

Oct 28, 2006

Probably a really simple solution to this one, I'm trying to create a macro that will jump to a location, select the current region and then create a range name for that region. However, the number of rows in the region will change each time the macro is run as the region is a result of an advanced filter from a huge database. This is what I have managed so far but it to no avail

Selection.CurrentRegion.Select

Ultimately the data selected will become a list for a Word mail merge

## Sum Of Postal Code By Region(canada)

Jun 30, 2007

I am trying to Write a function that would add all the postal codes that contains a certain value. Overall I want the function to return the number of postsal codes that contains lets say L3T. the problem is that my postal codes are formated like L3T 6X5 - L4V 4X9 , etc. i need excel to only consider the 3 first digits/letters and return me the sum of all the L3T, L4V etc

## Limit Row Height For Rows In Current-region?

Oct 15, 2007

I have an area in my spreadsheet (the current region surrounding cell G6) in which the height for each row should not exceed a specified limit (say, 150). It's okay if the row height is less than the limit - these rows should be left alone. I only want to resize those that exceed the defined row height limit.

Is there a simple way to reset the row height for rows whose height exceeds a specified maximum row height limit?

## Move All Data From Worksheet To Other Based On Geographic Region

Oct 23, 2013

I have a excel file with two tabs, one with raw data, with data ordered by geographic region.

I have another blank tab, where I've created a dropdown list of the geographic regions.

How do I move all the data from worksheet to the other based on that geographic region?

## Select Current Region Ignoring Cells In Dropdown List?

Feb 17, 2014

I have a sheet with a data in range A1:J9. I have a drop down list in each cell from A2:A100.

I'm trying to store all values in A1:J9 in an array using the property CurrentRegion.value like below

Code:
Arr=Range("A1").CurrentRegion.Value
The issue is that is taking as current region the range A1:J100, even if I don't have selected any value in the drop down list from A10:A100.

Is there a way to force CurrentRegion to select only values from A1:J9 ignoring the blanks dropdown list values or a similar way to load an array with values in a contiguous range?

## Retrieving Names Of Places From Google Maps For Selected Region In Excel Sheet

Nov 2, 2011

Retrieving the names of all places in excel sheet as per Google maps or visible in google maps for a selected District / State / Country.

## Doing IF Compare Against Concatenated Value In VBA

Jan 31, 2013

struggling with the syntax of this one. Lets say we have a variable called MYWEEK with a value of WEEK 9 and cells A1 and A2 have values of 9 and 10 respectively. I need to be able to compare the variable against cells A1 or A2 but with a prefix of WEEK .

I've tried the following:

if MYWEEK = "WEEK " & A1 then ...
if MYWEEK = "WEEK " + A1 then ...
if MYWEEK = ("WEEK " & A1) then ...

I know the answer is staring me in the face but I cant see it. Any use of the ampersand presumably invalidates the IF statement by introducing multiple conditions rather than a concatenated value. How do you compare a value against a concatenated set of criteria in VBA?

## Concatenated TextBox

Oct 30, 2007

I have a TextBox that I want to show the concatenated value of two cells. Is that possible? Or do I need to concatenate it on the sheet and then have the TextBox = that cell?

## Vlookup Concatenated Value As A Variable

May 13, 2009

Effectively I need to concatenate two columns in Sheet1, and then vlookup each concatenated value from Sheet1 in another concatenated column in Sheet2 and return the value from the next column

Now that is quite straight forward, but I am new to coding, and I was wondering if there was a way of doing it that doesn't involve having the concatenated values written to a column as a formula. Ideally what I would like it to do is put the concatenated value of Sheet1 together as a variable (I think thats what you call them) and then look up that variable in Sheet2 without Sheet2 creating a concatenated column too.

I'm of the understanding that this might be slower than writing the columns, but there is a reason for my madness.

Let me know what you think, or if i'm barking. Any suggested reading would also be handy, I don't like to bug you guys for the full code straight out.

## Countif With Concatenated Range

May 2, 2012

How can I countif on a row/range

I tried this but this wont work. All i want to countif there are duplicate rows. This formula will be copied down

=countif(\$A\$1:\$A\$11&\$B\$1:\$B\$11&\$C\$1:\$C\$11,A1&B1&C1)

## Summing Concatenated Cells

Dec 7, 2006

I have concatenated the following cells:
A1, B1, C1

The data in each cell is:
1, +, 2

I can concatenate them to show "1+2".

My question is, can I then perform the calculation that the concatenation produces?

I would like to be able to enter different operators into B1 in order to carry out different calculations.

## Concatenated Cell Value For If Statement

Jul 6, 2006

I have a cell that containes a concatenate statement for two named formula. The value taht the cell returns is a multiple of 10 (i. e10, 100, 1000, 10000 etc etc.) then in the adjacent cell, i have a nested if statement giviing differing text dependent upon the other cells value, i.e if less than 1000, return text string of "good" , however the formula does not seem to accept the value given in the concatenate cell.

## Concatenated All Results Of Lookup

Aug 29, 2007

I'm trying to use this function which was posted as an answer (looking up a single value and returning multiple results, concatenating those results in one cell):

Function getfiles(DRng As Range, LURng As Range)
For Each ce In LURng
If ce.Value = DRng Then
holder = holder & ce.Offset(0, 1).Value & ", "
End If
Next ce
getfiles = Left(holder, Len(holder) - 2)
End Function

What is "ce" here? Auto Merged Post;additional info:

the original question was posted by jwhite68, Feb 27th 2007, "Return Multiple Values From Lookup To Single Cell"

## Formula For Segregation Of Various Grade?

Feb 20, 2013

Here in "Grade" Column wants put a formula so that it will show First,Second,third and Fail. If the Mark is more than or equal 40 but less than 50 will reflect Third,if the mark is more than or equals to 50 and less than 60 will reflect Second, if the mark is more than or equals to 60 will reflect First and remaining less than 40 will reflect fail.