Returning Descending Values
I currently use the following to find the max number is a column:
=MAX($P$4:$P$65536). If I wanted to find the second highest, then the third highest, etc.. how would this be written?
i am setting up registrations for workshops at a conference.
each room holds 75 people.
what i would like to do is put the total room capacity at the bottom of the column and then, each time i put a check mark in the column, have the column total descend to zero so i know when i have to stop accepting registrations for that particular workshop.
is there any formula that would allow me to do this?
Is there a way to use the lookup function in reverse? The "lookup vector" on mine has to be descending, that is it it is a column going from high values at the top of the page to lower values at the bottom of the page. When using lookup function in this setup, excel gives me to wrong answer for some reason. If I make the lookup vector ascending, it works, but unfortunately the vector has to be descending in my worksheet.
View 2 Replies View RelatedTwo questions:
1) How do make values on x-axis as words? E.g. apples, oranges, etc.
2) Is it possible to have the numbers on x-axis in descending order? If I can figure out 1), I can do this as well, but it would be nice to know if it is possible to have the numbers on x-axis in descending order.
I have a list of eastings and northings for specific incidents, then in a seperate sheet I have a list of eastings and northings for postcodes.
What I want to end up with, is which postcode the incidents are in!
So it means getting Excel to find the closest match of two values to two other values and return a corresponding value.
Here's what it looks like!
Incident Easting Northing
01 437302 113607
02 437340 113673
03 437366 113701
04 437366 113701
05 437474 113581
Postcode Easting Northing
SP410TQ 430449 91321
SP410PY 429158 91339
SP410QB 429258 91375
SP410PZ 429288 91407
SP410EA 428940 91444
I want to end up with
Incident Postcode
01 SP7185H
etc etc.
I have a list of eastings and northings for specific incidents, then in a seperate sheet I have a list of eastings and northings for postcodes.
What I want to end up with, is which postcode the incidents are in!
So it means getting Excel to find the closest match of two values to two other values and return a corresponding value.
Here's what it looks like!
Incident Easting Northing
01 437302 113607
02 437340 113673
03 437366 113701
04 437366 113701
05 437474 113581
Postcode Easting Northing
SP410TQ 430449 91321
SP410PY 429158 91339
SP410QB 429258 91375
SP410PZ 429288 91407
SP410EA 428940 91444
I want to end up with
Incident Postcode
01 SP7185H
etc etc.
I have used several IF and AND functions in the past in order to check to see if 2 values = true and return a third.
(for example =IF(AND(A1>10,B1<20),C1,"")
However I can't seem to figure out the senario below.
I have 2 coulmns, Column A has dates and Column B has a numeric value
I need to check to see if column A falls between 2 dates and returns the sum of column B
For example if have 100 rows and 10 of those rows have dates that fall between 06/01/09 and 07/01/09 then add up column b for those 10 rows and return the value.
i have a formula that looks up data from a pivot table....the pivot relates to a data request log and a pricing request log - both separate. the data request log and pricing request log includes data and pricing activities....some are only data, some only pricing, a few are both - each activity has its own unique request number - if both data and pricing then the request number will be the same
so the pivot looks up a request number eg F1.....it counts how many actions there are for this request number (eg request F1 may have 6 actions) and displays this number. eg F1 data only request....6 actions.....no pricing activities/request...the pivot will display 6 under the column Data but then returns the value #N/A under the column pricing if no actions/request found for pricing...i want this to return "blank" if there are no actions/request found under either pricing or data. the formula used is as follows:
under the column named data:
=VLOOKUP(J7,'DATA PIVOT'!A7:D150,3,FALSE)
under the column named pricing:
=VLOOKUP(J7,'PRICING PIVOT'!A7:D150,3,FALSE)
i think that because FALSE is used this returns the value #N/A?
it seems that excel is deciding what type of array this is by evaluating the type of the first cell in each column and applying this type to the rest of the array's column. Problem is that i want all values to be brought in, text and numerical.
Dim VArray As Variant
Sub DA201()
If FileThere(Path & "DA201.xls") Then
GetDataTowers Path & "DA201.XLS", "", "DataRange", Sheets _("Towers").Range("CStart"), False, False
Else
MsgBox ("File DA201.xls not found. Click OK to continue")
End If
End Sub
Public Sub GetDataTowers(SourceFile As Variant, SourceSheet As String, SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)
Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String
Dim n As Integer
Dim row, m As Integer
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else....................................
I have been able to get the following code to work (which simply returns the start of data on a sheet):
Sub FindDataStart( )
Dim lRow As Long
Dim lColumn As Integer
For lRow = 1 To Rows.Count
For lColumn = 1 To Columns.Count
If Trim( Cells(lRow, lColumn).Value) <> "" Then
Goto EXIST
Else
End If
Next lColumn
Next lRow
EXIST:
Exit Sub
End Sub
Now I want to build on what I have learned so far and from another Sub, I want to call the FindDataStart sub and have it return the row and column # (from where the data starts).
I started by making the following changes to the FindDataStart sub but got stuck
Changed :
Sub FindDataStart( )
To:
Sub FindDataStart( RowNumb As Long, ColNumb As Integer)
I also added the following line to the end of the FindDataStart sub
FindDataStart = (lRow, lColumn)
In the attached example I paste in a report from another system into Sheet2 (rough data layout in example).
In column B in Sheet1 I have tried using Match and Index formulas to try and return only the Local Shipment figures from on Sheet2 for each staff member.
The issue i'm having is that the data in Sheet2 can change based on how long the report is run. There is also no way I can change the way the raw data is set out in Sheet2 as it's an external system.
Basically i'm looking for a formula that isn't locked down to certain cells and can work with data moving up and down columns at different stages. The key data never changes from column A and F in sheet2. But what does change is whether each person has one or both of Sales & Local Shipment data in the reporting. This obviously changes based on what is completed each day.
So I need a formula that matches the persons name in column A in Sheet1 with Column A in Sheet2, then looks for the first mention of 'Local Shipment' below it and returns the cloumn F value which is in the same row as 'Local Shipment'.
I have a set of cells and i want it to return all of the distinct or unique values within that matrix. for example, lets say this table charts how many sales an employee makes in a week.
sun mon tues weds thurs fri sat
john doe 1 0 3 2 3 0 0
jane doe 0 8 0 1 0 7 0
james doe 3 3 2 3 2 1 1
the desired output would be: 0,1,2,3,7,8
I don't want to be copying all of the cells or doing a lot of manipulation - i know i can copy, sort, and get unique values that way - i want a formula where as i plug in values, this field is updated to include all of the distinct or unique values.
I have a table of data-
IDDescriptionDatePax Count
316COACH01/07/20135
323COACH3/07/201410
324COACH18/07/20141
325COACH20/08/201420
333COACH21/09/201445
345COACH22/09/20143
351COACH23/09/20143
362COACH21/10/20142
371COACH22/11/20142
394COACH23/12/20140
416COACH23/12/201418
422COACH23/12/201439
Example, the real data is about 38,000 entries. I want to build a query to return the value of Column D (pax count) based on the values of Columns A (ID) and C (Date)
This would feed into a second, ID specific table, like this-
ID 536 Bookings JULY 2013
1/07/20132/07/20133/07/20134/07/20135/07/20136/07/20137/07/2013
(value) (value) (value) (value) (value) (value) (value)
SUMPRODUCT, usually my mainstay, is for some reason letting me down, returning a value of 0 for everything irrespective.
How the following problem gets sorted out
ColumnAColumnB ColumnEColumnF
33rohan55
44tata55
55dev44
55rony33
As you can see from above i want to lookup all the values in column E in column A and return the corresponding value in column F.
Trying to pull data from one workbook to another using Vlookup.
The worksheet has 7 columns (date, day of week, time or arrival, package, departure time, patron count and group name) I would like to pull all the data for a specific date in a seperate workbook.
I know I require a nested formula but cannot seem to get the variables correct:
I have two tabs (In-Transit) & (AC) column A in both sheet have a "Concatenate" that i copied and pasted and values only and also switch the format to "Text" on both tabs. I do a vlookup
[Code] ......
and it returns the results i want....But then i was thinking due to the volume of data what if the value in column A is found twice on tab "AC".. i wouldn't know with a "Vlookup".
So trying to build a Index/Match formula
[Code] ....
I also returns some good results, but i also get some back as #REF. I also get #NA and both of the formulas and those are valid no matches...
I want to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.
HTML Column A Column B Column C
Date Number Who
3/23/2007 902-555-2596=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-678-0352=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
~~~~~~~~~~~ Sheet 2 ~~~~~~~~~~~~~~~~~
Column A Column B
902-555-7958Big Bird
902-555-5100Daffy Duck
902-678-0352Elmo
902-555-5752Donald Duck
902-555-7420Bert
980-123-6621Ernie
902-555-2596Grover
I'm using Excel 2003 and am trying to filter a list of values. The list has duplicate values in it. When I use Filter>Advanced Filter and select "Unique records only" the filter only eliminates some of the duplicates. If there are 4 values of 100, it may filter 2 of them or none at all. Ultimately I'm trying to end up with a list of values with no repeats.
I am looking to create a formula to add together the two highest values of a list of 10 numbers.
I know you can use something like large(a1,a10,1)+large(a1,a10,2) if numbers are in an array but my problem is numbers arent
they are in random positions along a row....
I'm trying to get a value to return based upon three criteria, sales rep, compensation plan and calculation method.
For each sales rep (there are 20 reps), there is an option for one of five comp plans and for every comp plan, there are six calculation methods (these six methods are the same for every one of the five comp plans).
I know I need to use the Indes(Match()) set-up, but I'm having the darndest time figuring out how to set up the source matrix to do this right.
I have a spreadsheet with the second tab hyperlinked to the first tab.
Tab 1
Bus
DEC '13
NOV '13
OCT '13
SEPT '13
AUG '13
[Code] ......
ERROR: Row 157, Aug '13
Here's the formula I used: =IF('End of Month'!L3>=0,('End of Month'!L3-'End of Month'!N3),IF('End of Month'!L3>0,('End of Month'!L3-'End of Month'!M3),(0)))
Should return value 339.
The formula should simply take the current month value and subtract it from the previous month value BUT if the value is zero, then it should go to the prior month and so on until it retrieves a positive value.
I've also tried to following formula without any success:
=IF('End of Month'!H3=0,('End of Month'!H3-'End of Month'!J3),
IF('End of Month'!H3=0,('End of Month'!H3-'End of Month'!K3),
IF('End of Month'!H3=0,('End of Month'!H3-'End of Month'!L3),
IF('End of Month'!H3=0,('End of Month'!H3-'End of Month'!M3),
IF('End of Month'!H3=0,('End of Month'!N3-'End of Month'!O3),
IF('End of Month'!H3>0,('End of Month'!H3-'End of Month'!I3),(0)))))))
I have attached a file with an example of what i am looking for. The data in columns A and B are my original Data. There are multiple instances of e-mail address on the left but I need to bring in each data horizontally. I would like a formula to enter into E2:J6 to pull the information in.
Book2.xlsx
I have an attendance sheet that employees scan their badge number, and returns their name and time/date stamp using a simple VLOOKUP.
I want to create a report that compares the names in the attendance sheet against the all the names in the master sheet, and only returns the names that have not attended the training.
I have tried a few test formulas using COUNTIF function, but with no luck.
I have attached an example. Use the worksheet labeled April 2014 A (2nd shift)
New Six Point Safety with Attendance Sheet Barcode Scanning.xlsx
I have been facing few problems while automation my production sheet through formula.
1-- I want to sort data in the descending order through below formula.
=INDEX($A$23:$C$29,MATCH(LARGE($C$23:$C$29,ROW(B3)),$C$23:$C$29,0),MATCH(H$22,$A$22:$C$22,0))
when two candidates have the same percentage then this formula does not work.
I have a couple Sumifs formulas that I am trying to return monthly values for but there are small discrepancies on the values that they return. When I manually select the cells that I'm targeting with the formulas, I get a different value. I have checked the formulas 100x & can't figure out what is wrong with them.
Example:..............
In cell G51 of sheet "My Overview", if two people have the same scores it pulls through one person twice in F51 and F52, how can i overcome this?
View 4 Replies View RelatedActually I plan to create an eRequisition Form for our branch sites. My focus would be the consumable items of Lexmark printers. I want to create cascading drop-down lists based on data table (worksheet : All) as per attached. From lots of examples I've seen in this or other similar forums, I notice that in order to allow this cascading to work is by creating column with header name identical with the input in the 1st List. But I'm thinking, if I need to do that, then there gonna be hundreds or maybe thousands columns to create as the number of branches are expanding, so I think tht should not be practical.
I found examples that shows how lookup 1 value can return multiple values but I totally cannot think of a way to allow those multiple values to be returned as dropdown list rather than displaying all in rows of the worksheet.
What I need is that, in 1st list, upon selecting the branch, it'll populate all items related to the branch and when selecting the item, it'll populate brand/model list for the particular item only. The input in the dropdown list should not have duplicates. Been trying using pivot to do this with macro that enables auto refresh once any changes made in the table.
create this cascading drop-down lists just based on the table as per attached? I really want to avoid having to create extra tables to allow this cascading to work.
[URL]
making a forumla that will return the second, third ewtc values using VLOOKUP or INDEX/MATCH. Attached is a dummy file almost identical to the one I'm working in. In the first sheet there's a dependent drop-down list with the values Sub1, Sub2 and Sub3. This corresponds with data in sheet two listed next to these names. The problem is when I try to populate the table below using VLOOKUP etc it just repeats the first row over and over rather than defaulting to the second and third row results
View 6 Replies View RelatedI'm trying to find a way to test is the max value in a range, columns A-F, comes after the min.
Example: Columns A-F have the values 4,1,3,5,2 respectively. I'd like a test to see if the min, in this case Column B, comes before the max, column D, and have an output that says yes or no. So in this example yes but if the numbers were instead 4,5,1,2,3 it would return a no.
I have created a worksheet with 7 Tabs. 6 of the tabs are tables of existing parts and their catalog information. The Front Sheet I want to be a place where people can select data via a drop down or typed in, and in turn have that headsheet display the results of the search.
I have the 6 tabs broken down by part type, and figured those can easily enough be the first choice on my worksheet. So by picking that I can direct excel to which tab we want to run the search on, but ater that I'm drawing a blank.
For an example, say I'm looking for a Elbow that has a 6 size on one end. I would like to use the drop down to choose "Elbows" and then like to select/type 6 in the End 1 size box and have Excel show me all the reslts from the Elbow Tab that match that criteria.
