I need to locate the highest number in column b then in column c minus the users score from the highest so they can see how much they are behind the highest score. eg.
A B C D
fred 150 highest
dawn 125 25 points needed
mark 100 50 points needed
kevin 80 70 points needed
I WANT it to search all of column A for the highest number in the range (which will change daily to a new high number above the highest currently), and return the value in column 4 (column D) that corresponds to that value from column A. This is what I have, but it is not working, it is giving me the number (1) in Cell A800 (which is the lowest valued number in my range. The numbers range in value from 1 to 762 with none repeating (they are sequential). The formula I have is :
=+VLOOKUP(MAX(A76:A2000),A76:I2000,4)
I am looking to have the items in column K 1 through 16 listed from highest to lowest and at the same time post the corresponding number from column J 1 through 16.
So that it reads as follows:
04 - 18 - 06
02 - 17 - 06
08 - 10 - 03
Item 04 has 18 points and is listed in 6 columns etc.
How can I get Excel to do this?
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 worksheet (A) similar to the following:
Jan '12, Feb '12, Mar '12 --> Header Row
-5, 10, 2
6, -2, 3
5, -2, -1
I have another worksheet (B) with the same header Row (Jan '12, Feb '12, Mar '12). I need to create formula in cell A2 on worksheet B (right under Jan '12) that dynamically locates the Jan '12 column in worksheet A and then sums the numbers in that column only if they are < 0.
So in cell A2 on worksheet B (under Jan '12) I'd have -5 (only -5 is < 0), in cell B2 on worksheet B (under Feb '12) I'd have -4 (-2 + -2) and in cell C2 on worksheet B (under Mar '12) I'd have -1.
I've tried several variations of sumproduct, sumif, index, etc. with no luck.
I am looking for a formula that will find the lowest 5 number group in column A with a total value from column B under $100 in the below table.
1 $28.75
2 $28.00
3 $27.75
4 $24.75
5 $25.50
6 $25.25
7 $25.25
8 $16.50
9 $24.75
10 $26.50
11 $24.50
12 $27.00
13 $26.50
14 $23.50
15 $23.00
16 $19.25
17 $20.75
18 $25.00
19 $20.50
20 $20.50
21 $17.75
22 $22.50
23 $20.50
24 $19.75
25 $20.00
26 $10.75
27 $11.25
28 $5.25
29 $13.00
30 $9.00
We have an excel worksheet with textfields, that are compiled (when a btn is clicked) and the results are assigned to a static range. (Meaning, the results always appears in the same cells)
Question is: How can i have the results appear in a different row each time, starting on row 14 ?
I've got a formula that takes a value from the last numerical entry in a column. Is there a way to code this so that I don't have to enter the cell location of that number manually every time I append the sheet?
View 7 Replies View RelatedIs there a way to reference a column depending on its header? Is there a way of doing this without the need of surveying one-by-one all the column headers with a for loop? I need this cuz if I have a very dinamic program which moves columns arround and depending on the state of the program the position of a column can change.
View 2 Replies View RelatedIs there a formula I can use to locate the last active cell in a column. I did use the count function, however this isn't always reliable if there are blank cells within the column
View 5 Replies View RelatedI want to look at a whole colum and to tell me if the 6 digit number is located within it.
is "787897" located in column D? if so tell me by either Y / N or 1 / 0.
Here is the sheet where I entry new data. From left to right: Productnumber/article/supplier/price per item/starting nr of articles/order threshold/ date 1st order/date of delivery. When I press the button "verwerk" the data will be transported to the sheet "Voorraad verloop" shown underneath. As you can see the first problem consists of the placement of the first table. I can't get it to the first column. The second problem is that when I enter a new product like in the first image, it overwrites the current data in the sheet "voorraadverloop". See the image below. I used the following macro:
Private Sub CommandButtonVerwerknieuw_Click()
Dim iLC As Integer
Dim FindString As String
Dim rng As Range
Dim Lr As Long
If Application.WorksheetFunction. CountA(Range("A4:F4")) < 6 Then
MsgBox ("Niet volledig ingevuld"): Exit Sub
End If
FindString = Range("A4")
If Trim(FindString) <> "" Then
With Sheets("Voorraadscherm").Range("A29:A65536")
Set rng = . Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _ .................
I am trying to write a formula that shows the best grade for each each student and the subject in which
that grade was achieved.
In a previous post i was shown how to identify the highest grade
MAX(IF(A$3:A$34=A7,C$3:C$34))
I hit CTRL, SHIFT & ENTER to activate the formula - Result in column E
However, I also want to identify the subject in which they scored their highest grade in column F
I have two problems
1. I don't know how to write a formula that brings in the subject based on the grade for each student
2. I don't know how to write a formula in case there is a tie (see DAVID L)
I need a formula (or formulas) to give me the "coordinates" (Column & Row location) of a certain value in a large rectangular range. Intuitively this should be doable with a smart combination of VLOOKUP, HLOOKUP, INDEX, MATCH, ADDRESS and/or CELL functions (maybe in the form of "array formulas") but I can't seem to find the way...
Here is an example of what I need:
I have a large rectangular range (A10:F500), full of numerical values. Then in cell A1 I have one of the (many) values contained in the range, let's say, 3.14159. I need two formulas that give me the following:
a) A formula in cell B1 that tells me in which COLUMN of the range the value 3.14159 is located
a) A formula in cell C1 that tells me in which ROW of the range the value 3.14159 is located
If the range consisted of a single column (or a single row), this would be an easy INDEX and MATCH combination.... but I'm dealing with a *rectangular* range here...
I am new to VBA and have tried to develop the code for finding a specific stock symbol (Column A) from over 4200 symbols from a downloaded csv file, copy the data in in its (specific symbol's) row (in the next 5 columns B to F) to the 3rd blank row in the master workbook (data arranged from Bottom to Top). In the Master workbook each stock symbol has a worksheet with its symbol as the tab e.g. BHP.AX is the tab or sheetname for BHP stock in the Australian Stock Exchange. The downloaded file comes from a subscribed site EODData which provides daily OHLCV (Open/High/Low/Close/Volume) data against each symbol.
My attempt is shown below and it is very primitive. It does not work! I wish to run it from the Master workbook (name:-0PortfolioASXMultipleIB.xlsm) and not from Csv Data file.
VB:
Sub Macro0CopyFromCSV()
'Insert a blank row and format it in Master workbook
Rows("3:3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[Code] ......
This is a re-submission of a question previously submitted because the title for the first submission was so poorly worded.
I have a column that has numerical values in random order. I want to locate the first occurance of a value in that column.
I have unsuccessfully tried an Index-Match function - apparently unsuccessful because the values must be in ascending or descending order?
I need a VB Script or Excel tip to Locate the latest value in the column.
Example1
Value
1
2
3
4
5
6
Latest Value
6
Example2
Value
1
2
3
4
5
Latest Value
5
Note: I have 1 Row and 'N' Column. Column may have null value, which need to be ignore as mentioned in the example.
Also., if in future new value has been entered in the column then the Latest value has to be updated automatically with the new value.
In the attached spreadsheet I would like to locate the peak values of the numbers in column "F". I don't know if this is best done with a series of functions or by using a macro.
Column "F" translates to the light blue line on the attached chart. Column "F" (MACD) is the difference between a 12 week exponential moving average (EMA) and a 26 week EMA.
I would like the peak values from column "F" re-stated into column J,K,L or M. Ideally I would like to find the highest peak that occurred in a rolling 12 weak period. So not all peaks would need to be posted. By doing this I would only be identifying longer trends .
I'm using Excel 2007. 25 years experience using Excel and functions. Limited experience with Macros though long ago I used to write C code.
I am using the below code to locate the last column in various worksheets before pasting information. The problem is the column selected is either: Correct, Is a blank column with many blank columns in between it and the last visible text. The code also highlights all the cells containing text in some sheets. The results are the same for each sheet the code is run in i.e it is not varying.
View 4 Replies View RelatedI have a row of numbers (per example below) and I would like to return the column header for the max value and the second highest value, even when the second highest value is the same as the max value.
For example
A B C D E Highest Second Highest
1 2 3 2 3 C E
3 1 3 2 2 A C
The return values are under the highest and second highest column headers?
is there a way to highlight the highest value in a column.
=MAX(A1:A50) will write the highest value in whatever field the formula is written in, but is there a way for the number itself to be highlighted in its given location within the colum.For example, if A 21 contains the highest value in column A, can that cell be highlighted?
I am tying to find a way that I can get the highest value in a column of data depending on a set criteria, I have used the following =MAX(IF($C2:$C$109=$C2,IF(CJ$1=K$1,IF($D2:$D$109=$D2,K$2:K$109,0)))) and it works for the first row, I can drag it across ok, but if I drag it down it just populates the same value as the cell the formula started in even though the IF logical test has changed??
Can you please Help fix or offer alternative.
Also is it possible (say the above worked) to add to the formula so that it returns a value from another cell in the same row that it found the highest value? e.g. =MAX(IF($C2:$C$109=$C2,IF(CJ$1=K$1,IF($D2:$D$109=$D2,K$2:K$109,0)))) say this returned the value and it was found in K52 it would return me the value in B52
I have a spreadsheet where columns I through V are date fields (stored as text as some have letters after the date). Is there a way that I could write a formula so that Excel returns the column letter of the last cell without a letter at the end of it? So it kind of looks like this:
I J K L M
05/01/2014 05/15/2014 05/30/2014(p) 06/20/2014(p) (null)
What I want in the column with the formula then would be just the letter J. Is this even do-able?
Starting at C6 and running down the C column there is a numerical list of values which have been generated from a PivotTable. I'd like some code that will scan down the C column and copy the highest 10 values in that column and the contents of the cell in the corresponding A column starting at F5 and finishing at F15 (a top ten list).
A sample workbook with desired output is included.
How can i color the highest value in each column separate. (I mean not manual, for example i have 50 columns).
View 9 Replies View RelatedI have two columns each having 8500 rows.
A= Zip code
B= Sales
Duplication of zips appear in column A - and I only need to return the highest sales in B. So based on the example below..I only need to return $500 in column C.
A
A2=50201
A3=50201
B
b2=$345
b3 $500
make and loop that loops true all values in column A and B. For example, if the cell A3 is higher the cell B3 then I want it to write the higher value in new sheet.
View 2 Replies View RelatedI am trying to determine for each account number in column A, the most popular method for accessing our services based on visits in column B. And then having those numbers summarized as a total for each method.
See below for an example. As you can see Mobile was the most popular for 2 clients Java was for 1 and Desktop was not for anyone. Not all accounts will use all methods.
Account
Visits
Method
1
10
Mobile
[Code] .......
I have a worksheet where columns C, F, I, L, O record scores within a league. Each row records a persons score in that league and there are two rows per person recording their score and their handicap score. So Person A would be on rows 3, 4, Person B on 5, 6 etc. The persons name is recorded in Column B.
What I would like to do is to have a cell(s) elsewhere in the worksheet which show the highest score in that league and display that score and the name of the person who achieved it. This ideally would need to be done for the highest score and the highest handicap score.
For the life of me I can't even begin to work out how to do that or even know if it is possible in Excel.
So to clarify, lets say the highest score is in cell L7 and the highest handicap score was in M3. The cell(s) containing this formula should then show the name in B7 and the score in L7 and below it the name in B3 and the score in M3.
Im looking to find the max or highest time in column G based on the criteria (date selected and name) So if the date is the 10/02/2014 in G10 it would look up that date and the name and bring back the time in column H. I have put an example answer in H12.
View 9 Replies View Related