I am trying to work out the 'Upper Percentile' of some donations we have received.
To do this I thought it would be rather simple, I just used
Code:
=LARGE(A1:A100,ROW(INDIRECT("1:9")))
This worked but obviously puts the values in 9 different cells, my work would like it shown as 1,2,3,4,5,6,7,8,9 in one cell.
I have a column that updates automatically by placing a value in in the next blank cell in the column. I am trying to write two formulas to identify the highest and lowest values in the preceding 10 cells of each update in the column but seem to be stuck in MIN/MAX/OFFSET hell.
View 8 Replies View RelatedAt my work we use excel to keep a large number of labels done in one day, those labels are then added up to give our weeks total, running total and average by day.
What i am looking to do though on a seperate sheet though is find out which day we had the most, an example of what it looks like
Basically I want to get the highest value in a cell from a range of cells on another sheet within the same spreadsheet......
The problem is that the values in the range of cells I want it to look up are not just straight values but rather the summary of two other cells as follows:-
=IF(BN5<"",BO5&" - "&BP5,"") which means that the Values in the cells that the formula needs to search will read something like:- "1250 - B Builder"
Also, cell BN5 is where the archers 3rd highest score gets entered and as such only at this point in time do they 'qualify' for the Standings Table Sheet.....
Then, with luck I can look for the second highest score in the next cell down from looking at the same range and so on and so on.....
How do i take the three highest numbers from E4:E5 & E13:E14 and add them together in E26
View 3 Replies View RelatedBasically, I have a worksheet that shows test results for 6 separate tests. If an objective is met in a test then it is marked with an "X". If an objective is partially met then it is marked with a "/" and if it is not met then it is marked with a "." . I have now created a combined column where I want to collate whether an objective has been met at all across the 6 tests.
For e.g. if in the range H14:M14 there is an "X" then N14 should show "X". If there is no "X" but there is a "/" then N14 should show "/" and it there are neither values in the range then it will show "." .
I tried
[Code] ....
but it just came back with a formula error.
need to understand the easiest way of highlighting the highest and lowest number in a range of cells several times in the same column.
As I said the cell range will be in the same column. So highlight the highest number green and lowest number red in cells a3 to a9 and then repeat the same process again in cells a11 to a17 and so on.the cell references are an example.
So I have several separate sets of numbers in the same column.
I have a range of 30 cells in a row. From that row there are groups of cells that have data that might be 1-7 cells populated in a row and in between these groups are blank cells in the row. I need to see what is the highest number of cells in a continuous row that contain data, what the mode is for continuous cell counts, and what is the average value for the data from the continuous cells. The average is a nice to have but not entirely necessary as most cell data will be fairly similar and I can get a close enough average just by knowing the average number of cells that contain data in a row. I have to do this calculation for about 1000 rows of individual data so its important a formula fit in one row.
Almost need a count function to count starting with each cell and then stopping at the first no no value it finds. Then it would show those counts and I could run a formula to pick the highest count and also show the mode of counts.
I will let the experts at it!, my weak mind could not think this one through. I thought something to do with MATCH for a while but no go from what I could do.
I need a cell formula that will (a) identify the highest N values in an above specified column range, (b) color the interior of those N cells (I suspect that this is not possible), and most importantly (c) return the average value of N corresponding cells, where the corresponding cells are located on the same rows as the identified N high value cells but in a specified column to the left (not necessarily adjacent)
Does anybody know what this formula would look like?
Example:
-----------------------
...| A | B | C | D | E | F |
-------------------------
1 |....| * |.........| 7 |...
-------------------------
2 |....................| 2 |...
-------------------------
3 |....| * |.........| 6 |...
-------------------------
4 |....................| 1 |...
-------------------------
5 |....| * |.........| 5 |...
-------------------------
6 |....................| ? |...
-------------------------
? = average of B1,B3,B5 where (N = 3) and (specified column to the left = B)
I am looking for a way to find a highest (and/or) lowest value in a list of numbers. The list keeps getting longer and after a certain point I will need the 2 highest (or lowest), then the 3 highest (or lowest). I know MIN and MAX will find the high and low, but how do I find the 2nd highest, etc?
View 3 Replies View RelatedI have a column of one to three digit numbers (I5:I34), some of which contain an asterisk after them. In I35 i want a formula to find the highest value from that column. Additionally, if two numbers in the column are the same value, but one contains an asterisk after it, it should use that value in I35, instead of the one without the asterisk.
View 6 Replies View RelatedI have two columns.
A column = contains dates but does not always have 5 days in a week. Holidays are not entered.
B column = price data for each day
All I want to do is get the highest price from the previous week. So for example last week highest price was 5000 then column C will display 5000 for this entire week. I tried using WEEKNUM and WEEKDAY but i am clueless on what to do after that. I'm trying to avoid macros or VB since im not that advance with that. But if I have to I will.
I am using VLOOKUP with the not_exact_match set to True, however instead of finding the next largest value that is less than value, I want to find a way of returning the next largest value that is greater than value. I have looked at using MATCH and OFFSET to try and increment the returned value by 1.
View 9 Replies View RelatedCells L5 - L65 have percentages. B5 - B65 Have the associates names. I want to write something in another cell that looks for the highest percentage and then prints the name as the highest.
View 11 Replies View RelatedI have a spreadsheet and on sheet 1 it currently has 45791 rows filled with data and it increases each day.
Each row looks like this
A RACECOURSE
B DATE
C RACE DETAILS
D HORSES NAME
E RATING OF THE HORSE
On Sheet 2 I have 3 columns. These are the list of horses that are running on a particular day.
A DATE
B RACECOURSE
C NAME OF RACEHORSE
What I want to do is to be able to list in columns D,E,and F on sheet 2 the last three ratings the horses achieved from sheet 1.
how to acquire the latest rating of the horse by using the following formula.
=IF(ISNUMBER(v(LOOKUP(9.9999999999999E+307,1/SEARCH(C2,Sheet1!$D$3:$D$45790),Sheet1!$E$3:$E$45790))),v(),"")
This works a treat but for the life of me I can't fathom out how to get the formula to pick the latest three ratings and place them on sheet 2.
I am using excel 2003
I have a very large spreadsheet with daily sales for over 140 different items, over the past 2 years.
This is how my data looks like:
ITEM NAME 4/1/12 4/2/12 4/3/12 etc
item1 10 15 500
item2 5 0 3
item3 0 0 5
etc
I'm trying to build a formula that allows me to see which item is the highest trending over a period of time I input.
I figured out the way how to select data between specific Start and End dates, but don't know how to interpret "trending" in a formula.
I was thinking maybe highest jump from beginning to end of interval, but what if it's a new item that was launched halfway through the interval and does poorly throughout, that would still be "trending" under that rationale....
=IF(COUNTIF('Deal Calculations'!E4:E30,"Pandy Mandy"),'Deal Calculations'!B4,"£0")
Above is one of the formulaes that I am using and I tried editing, to no avail.
Anyway here it is, I need to search E4 - E30 for a particular name, lets say Pandy. Now if pandy is found, then a cell on another tab has to equal a cell which is 2 cells on the left. from which the cell which contains the name is located.
here is a psuedo version of it...
Search E4-E30 Find a cell which contains 'Pandy' If pandy is found, 2 cells to the left then a cell on another tab is equal to that.
I need code in VBA that look for empty cells at a range and return msgbox with the empty cell
View 5 Replies View RelatedI am looking to set up an excel doc with 2 sheets.
In sheet 1, I am would like to be able to enter a series of words of interest/criteria to be met (in column D).
In sheet 2, I would like to paste in the text from an individual's CV. The text would be entered en masse, meaning that some cells would be empty and others would have whole sentences/paragraphs of text.
I would like a formula to enter in column A of sheet 1 which would search all of the cells in sheet 2 for the word/words in column D of sheet 1 and return either a positive or negative value.
I had tried the following:
=(COUNTIF(Sheet2!1:1048576,Sheet1!D2)>0)
This formula, however, is not capable of extracting the desired text from a string/sentence, and so only returns a positive result if the desired text is in a cell by itself.
I have a table of information as follows:-
CLIENT BILLINGS INCOMEMARGIN
CLIENT 1 13,740,719 541,5743.9%
CLIENT 2 2,524,535 59,799 2.4%
CLIENT 3 349,125 17,085 4.9%
I need to be able to show amounts less than £1million which is easy enough in an IF statement but then I need to be able to show amounts between £1million and £5million as a seperate block.
I have value of 26 in Cell A1, for example and I want to find
whether or not that value falls with a range of cells B1:C2 (which is in another sheet) and if so return the value in D Range.
A1 B C D
26 20 30 100
How would my formula look?
So this is something that I'm not sure is possible in Excel. I would like to use an "if,then" statement to see if a number is in multiple cells. I know this is usually very simple except there's a catch here. In each cell there's a number range using a "-". So in a single cell a range would be 301-305. I am open to having the range done a different way like 301,302...etc. I just decided this would be an easy way to look at it and was hoping to find a way to solve this problem with leaving in the dash. Whatever is practical is fine with me.
Using Example A in the attached file I want to use this statement, =IF(308 is in any of the ranges in A3:A6, TRUE, FALSE).
So, for me, the alternative I want to avoid is Example B where I would have to list every single number and then check the whole range. I would like to avoid this because these examples, as you can imagine, are on a much smaller scale then what I will actually be dealing with.
My excel file was imported from a 3rd party and has many blank rows across the entire sheet.
I want to delete these rows if every cell across is blank.
How is this accomplished?
I tried the formula "=isblank(C2:C18)" however I get a "False response on those rows that are completly blank.
I have a total $$ amount in one cell. Using this $$ amount I need to populate two other cells. One that is divisble by $10,000 and the remainder would populate the other cell. In the attached excel file I made notes to this effect.
If the $ amount in cell A3 is $83,000 I would need $80,000 to populate cell C3 and the remainder $3,000 to populate D3. If the $ amount in cell A3 was less than $10,000 such as $5,000 then only D3 would be populated.
It is about finding the number of unique items in a range that contains empty cells.I can intuit the form of this:
Formula:
[Code]....
And many of the variations of it when there are no empty cells in the range.
For a range of that contains empty cells I've tried this:
Formula: [Code] .....
And variations of it. It does not work, and I don't know why...............however..........
I ran across this on another [SOLVED] thread and it does work:
Formula: [Code] .....
I would have never considered this plausible. What is the magic behind appending an empty string to the criteria in the COUNTIF function?
It strikes me that this strategy likely has application elsewhere, too.
I am using this formula to calculate a column of numbers that are both negative and positive numbers. =SUMPRODUCT(--($A$5:$A$9647<=TODAY()),--($A$5:$A$9647>TODAY()-365),$C$5:$C$9647). I need to keep this formula the same where it will calcuate on a rolling 365 day but I need the total to be only the absolute value (abs).
View 2 Replies View RelatedI have dollar amounts that I need to distribute amongst varying numbers of columns and not have have the total distributed be over or under the original amount by any number of cents. When I simply divide the dollar amount by the number of columns, the total of those columns can sometimes be more or less than the original dollar amount by a few cents.
e.g.
Dollar # of A B C All Columns
Amount Columns Total
--------------------------------------------------------------------
$25.05 2 $12.53 $12.53 $25.06
$11.47 3 $ 3.82 $ 3.82 $ 3.82 $11.46
$25.05 divided into 2 columns gives $12.53 in Column A and $12.53 in Column B. Total of Columns A and B is $25.06. Over by a penny.
$11.47 divided into 3 columns gives $3.82 in Columns A through C. Total of Columns A through C is $41.46. Under by a penny.
I know I could simply always add or take away the pennies from one column, but I would prefer the process to be random or formulated in such a way that the Column to which the extra pennies are added to or taken away from differs in order to be "fair to each column".
I have a large spreadsheet of data that contains 3 columns: Columns are Name, Revision, # of items. I want to find the # of items for the highest revision for each name. List looks like this:
Smith, 1, 30
Smith, 2, 36
Smith, 3, 18
Johnson, 1, 125
Johnson, 2, 130
Lopez, 1, 8
Lopez, 2, 12
Lopez, 3, 15
etc…
I'm only interested in the data for the latest Revision. There's over 500 names. A lookup or pivot could be possible?
i want my formula to look up the highest value from a range (the =max column), then return a name in the leftmost column. What it actually is, is a player of the month for fantasy football. Each week the player gets a score, then each month, a total of four weekly scores. i want to look up who scored the most and return the player name to me. see the attachment.
View 2 Replies View RelatedIn the attached sheet, I'm trying to get excel to return the top 5 values in an array, based on the preceding cell. This example is for a Football League, in which the QBs, RBs and WRs all have different salaries. The objective is to return the salaries of the top 5 paid people in each. Don't need to return the name, just the salary amount, so that an average can then be taken to figure future salaries.
View 5 Replies View Related