# Conditional Median Formula Into Conditional Quartile / Percentile?

Apr 15, 2014
Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:

(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.

However I can't figure out how to manipulate it into a percentile keeping the condition of

IF(DATA!$B$15:$DZ$15=1000)

View 2 Replies
ADVERTISEMENT
Jun 18, 2008

I have a collection of data where the lower the value, the better. When I use Quartile/Percentile, it shows higher values as having a higher rank. Any way to reverse the order, without changing the underlying data (ie. multiply all values by -1)?

Also, I have a graph of the data showing the 1st, 2nd (median) and 3rd quartile and then a particular data set (in this case, company results) over a number of years. Any way to automatically add the Percentrank as a data label for the specific company results? For example, the value 200 puts this company at the 65th percentile. I want the data label to show 65, not 200.

View 9 Replies
View Related
Feb 8, 2014

I'm looking to use the percentile formula given a condition. The percentile formula as I understand it is percentile (array,k), where K is the percentile I want.

What I want to do is select a column for the percentile, but only do it for values above zero (I don't want to get into the full explanation as to why but I cannot cheat and just sort the columns).

The context is that I was doing some averageif calculations previously and want to remove outliers (top 5 and bottom 5% of data).

View 1 Replies
View Related
Feb 21, 2009

I have a sheet of many rows a small sample attached. Column A list 3 races at times4.05,4.40 and 5.10...col b contains the names and col c a number for each name.

In col d from d2 to d11 in each cell I need the median number of col c for the race at 4.05...in d12 to d24 I need the median number of the race at 4.40 etc...My sheet has thousands of races..

View 9 Replies
View Related
May 9, 2006

for my thesis i need to create median industry multipliers. i have a list of

companies with their industry codes and multiples, but now i need to create a

list with medians per industry. is there a function similar to SUMIF for

medians?

View 11 Replies
View Related
Oct 8, 2013

Just curious to know of any other formulas similar to the "quartile" function that allow you to calculate results in thirds (plus any other if known)

View 2 Replies
View Related
Mar 20, 2013

Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:

If AI3=C3 & C3 is red, format AI3 blue

If AI3=D3 & D3 is red, format AI3 blue

Otherwise, leave AI3 unformatted.

Possible???

View 3 Replies
View Related
Feb 9, 2009

I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.

View 2 Replies
View Related
Mar 13, 2008

I have a list containing blocks of stock price values with each block representing a series of values at a given time e.g

1400 156

1400 99

1400 74

1400 86

1400 256

The number of entries may vary for each block but is never more than 60. the next block may be e.g.

14:05 15

14:05 42

etc

and down a very long list of around 65000 entries. I'm looking for a routine that will add up all the values in each block and calculate the total for that time block. I then need to fill in an adjacent column with the perecentage that each value is of the total. A completed block may look like;..............

View 5 Replies
View Related
Jul 28, 2014

I am making the monthly schedule for my colegues. I wonder if there is a conditional formula that can highlight a row with 10+ cells if in one of them I type SUNDAY or something.

I am attaching an example of the schedule. I thing you will get my point.

View 3 Replies
View Related
Mar 9, 2009

Have 2 conditional formattings going on in a database query and need to apply two more conditions but only have 1 conditional formatting left. I am trying to highlight everything in a row that contains either "HCAT" or "HCAL" in column D. So far I have this,

=$D2=OR(HCAT,HCAL)

but its not highlighting anything, any clues?

View 3 Replies
View Related
Nov 30, 2009

I am trying to create a conditional/IF formula contingent on 3 different possibilities for f2, with the formula I wish to use based on these 3 possibilities, as shown below:

If f2 <=10, f2*100

If f2 >10 but f2<20, 1000+((f2-10)*50)

If f2 >=20, 1500+((f2-20)*20)

I'm not sure how to create this formula using three different conditions, especially as it is unclear to me how Excel needs to have the parenthesis placed.

View 2 Replies
View Related
Dec 19, 2009

Hello, I cant figure out how to do this! I have a cell B2 that I enter a number in and in cell C2 I want it to add the number from B2 and add 15 to it. I can get the formula "=B2+15" and it works but I wont always have a number in B2 and I want it to say "0" and "0" would be in C2. Any ideas?

View 3 Replies
View Related
Mar 23, 2005

I would like to concatenate cells from a column based on the match in the

preceding column with a defined value.

Example:

Col. A B

Row1: T1 Jim

Row2: T3 Paul

Row3: T1 Joe

Row4: T2 Mike

Row5: T3 Caroline

The formula should return for T1 for instance: Jim;Joe;

(for T2: Mike;

for T3: Paul;Caroline

Cells in column A can repeat any number of times.

View 9 Replies
View Related
Dec 29, 2009

I want to set up formula in conditional formating so that when:

$AF6 = "CA" the color is red

or

if $AF6 = ("GA","ME","PR") then the color is blue

what I came up with is Condition 1

= ($AF6 ="ca") [which will turn the cell red]

Condition 2

="OR($AF6={""GA"",""ME"",""PR""}, "")" [which doesn't do anything]

View 2 Replies
View Related
Jul 18, 2012

I simply need to have column A be red if value is < Column B value and green if it is >. The problem is that if the value in column A is .00000004 and column B is .00000005, it turns red. Example is the first three numbers below the headers....they each are 0%, yet the value on the left turns red because it is .0000005 more than the value on the right. I just want the colors to reflect the numbers which show up as below. Is there a way to do this?

2011 % of PDD

2011 % of Total $

0%

0%

0%

0%

0%

0%

[code].....

View 1 Replies
View Related
Oct 21, 2013

I have a file with two columns

Column A has a list of equipment names, for example: EQ1, EQ2, EQ3, etc.

Column B has serial numbers of equipment from column A.

I need to create a complex conditional formatting, which will check:

If A1=EQ1, and B1"HCC*" then make it red,

OR

if A1=EQ2, and B1"ABC*" then make it red,

OR

if A1=EQ3, and B1OR("CDE*","FGE") then make it red.

And then same thing for A2, B2 cells.

I have thousands of rows in both columns.

I have to check whether selected equipment name corresponds with entered serial name pattern. I don't know how to do it for each row separately.

I thought of something like:

1st condition: =AND(A1="EQ1",LEFT(B1,3)"HCC")

2nd condition: =AND(A1="EQ2",LEFT(B1,3)"ABC")

...

But how do I apply this conditional formatting to each row, so that it will compare A2 with B2, A3 with B3, and so on, instead of comparing A1 with B1 in all of the rows?

View 2 Replies
View Related
Feb 1, 2008

1. I have a range of random numbers within a column, i.e. A1:A9

2. A specific number will appear in a cell at the end of that column, i.e. A11

3. I need to see via conditional formatting which of the above cells add up to that number.

I am aware that given the numbers in A1:A9, it is possible to have more than one combination of numbers that add up to A11. I would need to see those combinations as well identified using Conditional formatting.

View 9 Replies
View Related
Apr 1, 2009

Using this basic version of my data:

NameGradeWeekJim31Sally31Fred32Claire53Susan43Chris52Ted41Jenn53Nicole43

I am trying to figure out How many 3rd grade, 4th grade, 5th graders there will be for each week. I will need a formula to fill in for each blank cell below.

# of 3rd# of 4th# of 5thWeek 1Week 2Week 3

I think I need a formula that would "read" something like:

if "Week" is equal to "1, 2, or 3" then within the rows that contain that week, count all "Grade" that are equal to "3, 4, or 5"

I tried sumif, counta, if, and combinations of them, but I am not sure I am using them correctly.

View 9 Replies
View Related
May 29, 2009

I was wondering if anyone knows if you can set conditional formatting if the cell contains no formula but is not blank.

We use many formulas to calculate cells and we need to the cell to be colored if there is no formula entered. But the cell is not necessarily blank or zero.

View 9 Replies
View Related
Sep 3, 2006

I am preparing data for an econometric application using excel. The format of the data is as follows: (figues are made up)

Portfolio Return(Pn) Return(MarketIndex) Covariance

1 | 2.1 | 1.1 | =COVAR(B?:B?:C?:C? ...

1 | 2.2 | 1.3 | =COVAR(B?:B?:C?:C? ...

1 | 2.3 | 1.5 | =COVAR(B?:B?:C?:C? ...

1 | 2.4 | 1.7 | =COVAR(B?:B?:C?:C? ...

2 | 2.4 | 1.1 | =COVAR(B?:B?:C?:C? ...

2 | 2.3 | 1.3 | =COVAR(B?:B?:C?:C? ...

2 | 2.2 | 1.5 | =COVAR(B?:B?:C?:C? ...

... ... ...

From this I need to calculate the Covariance of a portfolio's returns with the market index. The problem is that I cant seem to specify a condition within the =COVAR(...) formula so that the COVAR Folrmula automatically selects only the applicable pairwise observations... That is, I need to write a COVAR function that will automatically search the first Column for a specific portfolio number and then include the corresponding returns data in the COVAR formula. For example, the first four formula need to return (B2:B5,C2:C5) and the next three formula need to return (B6:B8,C6:C8). I hope this has been descriptive enough of my problem. I am doing my head in trying to use Index-Match functions but know there must be some simple solution.

View 2 Replies
View Related
Sep 8, 2006

Is it possible to write a formula that will compare the text in two different cells? And if I can do this can I also have that formuls place a numeric value in another cell? An example of what I am trying to do is: If the text in C2 is equal to the text in C3 place a 1 in C4 or if the text in C2 is not equal to the text in C3 place a 0 in C4.

View 2 Replies
View Related
Mar 20, 2007

I am referencing a cell in order to change the background color of a row of cells if the number in column B is = to the number in a specific cell. The formula used in the conditional formatting is =$B73=$B$10. The reference cell $B$10 contains the following formula =INT(MAX('Assumptions & Inputs'! F8:F9))

The conditional formatting worked fine when I used that formula. I had to change the formula in cell $B$10 so that it would meet a new criteria. When I did that the conditional formatting would not change the color of the row. The new formula that I put in the reference cell $B$10 is

='Assumptions & Inputs'!$D$8+MAX('Assumptions & Inputs'!$F$8-'Assumptions & Inputs'!$D$8,'Assumptions & Inputs'!$F$9-'Assumptions & Inputs'!$D$9)

View 5 Replies
View Related
Jul 11, 2007

I am trying to understand VBA codes. Actually I wrote a formula with IF and I want to change it to VBA because it is so long and creates some problems. It is too important for me =IF(AND(0<C6;C6<9);"0";((IF(AND(8<C6;C6<14);(C6-8)*(8,4);((IF(AND(13<C6;C6<10000);(5*8,4+(C6-13)*(26,45));" ")))))))

The logic is like that, this program will calculate an amount.

btwn 0 and 8 days, it is free

btwn 9 and 13 days, 8.4 euros per day

after 13 to infinity, 26.45 euros per day

Therefore for example for 10 days, you have to pay 2*8.4 = 16.8 euros

for 14 days, 5*8.4 + 2*26.45 = 92.9 euros

View 3 Replies
View Related
Mar 6, 2014

We are trying to find the median of a large set of numbers to calculate the median income in 2010. For an example we have 8,379k people with $2500 average income, 9,783k with $7500 average income and so on. How can I calculate the median average income of such a large amount of entries?

View 8 Replies
View Related
Jun 21, 2014

I have a challenge with conditional formatting. I have products A-K with sales current year and last year and want to have year on year growth. I want to have one column where the cell formatting changes based on data validation.

Let's say product A sold 60 items CY and 50 LY meaning YoY variance is +10 items or +20%. Hence, if the relevant cell says "%" then the YoY figure should say "+20%" (formatted as a percentage) and if "VALUES" then YoY should be 10 (formatted as numbers/absolute values).

Remember also that YoY growth can be +100% (or more), 0% and -100%.

View 10 Replies
View Related
Mar 16, 2014

I have a list that, how do i create a border that data contains formula (formula not display/blank)...

i want to create dynamic border with condition if data display/showing must be border shown too...

for this, i want to line "TOTAL" in column AD expands up, based on data display/showing...

note : in cell AF2 as parameter/controller to show data by month (type 2 instead February or 3 --March etc...)

View 5 Replies
View Related
Nov 6, 2008

I am trying to use conditional formatting for a cell; It is a cell using Validation; you can choose "Yes" or "No" from a list If the value is "Yes" the cell should be green, regardless of everything else But if the value is "No" it should turn red, but only if a choice in another cell is equal to "X". Otherwise it should be left without special formatting.

View 2 Replies
View Related
Jan 10, 2009

I am having a very difficult time trying to figure out a conditional formatting formula for something that seems very simple. There are 3 conditions I would like to apply to cell B2: If the value of I2 is less than or equal to 24, but greater than or equal to 21, shade B2 red. If the value of I2 is less than or equal to 20, but greater than or equal to 12, shade B2 orange. If the value of I2 is less than or equal to 11, but greater than or equal to 8, shade B2 yellow. I am sure I am missing something quite simple here.

View 5 Replies
View Related
Mar 1, 2009

I am creating a sales per day/week worksheet for my reps to keep track of their sales. I have created the sheet that they only have to enter in what they have sold and it automatically calculates what they have sold per hour in the day, and what that makes their weekly total.

The problem is sometimes they will work 5 days, sometimes 4, sometimes 6 or 7.

im trying to fix the weekly average formula to divide by 7.5 for 1 day worked, 15 for 2, 22.5 for 3, etc all through 7...

this is the formula I have now, obviously it doesnt work

=IF(COUNT(B7:H7)=1,SUM(E42,E75,E110,E143,E175,E207,E238)/7.5,IF(COUNT(B7:H7)=2,SUM(E42,E75,E110,E143,E175,E207,E238)/15,IF(COUNT(B7:H7)=3,SUM(E42,E75,E110,E143,E175,E207,E238)/22.5,IF(COUNT(B7:H7)=4,SUM(E42,E75,E110,E143,E175,E207,E238)/30,IF(COUNT(B7:H7)=5,SUM(E42,E75,E110,E143,E175,E207,E238)/37.5,IF(COUNT(B7:H7)=6,SUM(E42,E75,E110,E143,E175,E207,E238)/45,IF(COUNT(B7:H7)=7,SUM(E42,E75,E110,E143,E175,E207,E238)/52.5)))))))

View 7 Replies
View Related