Return State Value Based On Match Of Name And Number Between High And Low
Aug 27, 2013
I have a set of data (Data tab) that lists name, a high number, a low number, and the state that is associated with the name and range between the high and low number (see attachment). I am trying to figure out a formula that will return the state value based on a match of the name and the number between the high and low. I am trying to use Index/Match but i cant figure out the "between the high and low" portion.
tester.xlsx
View 2 Replies
ADVERTISEMENT
Jun 24, 2009
I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.
View 4 Replies
View Related
Aug 14, 2008
I want to find the high value from daily data for each week and copy that value.
I have a list of daily price data, being the date, opening price, high price, low price and closing price. For each week I am finding the data necessary to create a list of the weekly opening price, high price, low price and closing price ending on each Friday.
To find the opening price I am using this formulae -
=INDIRECT(ADDRESS(MATCH(J2,$A$1:$A$1400,0)+1,COLUMN(B1)))
To find the closing price the formulae is -
=INDIRECT(ADDRESS(MATCH($J$3,$A$1:$A$1400,0),COLUMN(E2)))
I need to find the high value and the low value within the five days data, from two separate columns (C and D) of daily data.
Can I use a formula similar to those above, but instead of copying a relevant cell, for example, finding the high from cells B3 to B7. Then I need to do this for each subsequent week.
An example spreadsheet is attached. You will see that currently I am currently copying the Friday data in columns L and M as I don't know how to adapt the formula to do the job.
View 9 Replies
View Related
Aug 14, 2008
I have an interesting problem (I hope you find it interesting too). I'm trying to find the historical "high" day of the week for a particular stock I follow. I searched for several hours but was honestly unable to make any progress. Normally I do my own formulas, so other than pasting a rare macro, that's pretty much the extent of my (lack of) experience, ie please assume I am a novice....
View 9 Replies
View Related
Apr 2, 2009
I have time start and time end and the difference between the two times. In a third column I have profit and want to calculate the profit per hour. When I divide the profit with the time spent I get a very very high number. If I for instance type in 1 hour of work and profit 100, then I get profit per hour 2400. (profit/hours spent)
On a side note I am trying to get the numbers as 24 hours and not use am or pm and in the spread sheet I get these numbers but in the box at the top where you see the formulas etc. it shows as am and pm. I have uploaded an example.
View 2 Replies
View Related
Jul 20, 2007
I would be loading data into a spreadsheet, and when there is a phone number I'd like to have it populate the state field. (ex. (212) 123-1234 in the phone field, would populate "NY" in the state field, is this possible? If so is there any brief way to describe how to do so without taking too much of your time?
View 9 Replies
View Related
Jun 22, 2009
in one column I am looking up the value Yes to return the row number.
=MATCH("Yes",C:C,0) in this case it returns a 2
I want to use this row number in a sum...
i.e. =sum(b2:b&x) where x is the row number from the formula above, but it just errors out.
View 9 Replies
View Related
Jun 3, 2012
I'm trying to create an array formula that returns all the cities in a given state by imputing the state's abb in cell K2. The returned cities go into column J. California has the most cities(430) and the cities are 5000 rows deep.
View 7 Replies
View Related
Aug 25, 2006
I have two columns of numbers (I'm going to call them Column A & Column B). There are 23 rows with these numbers. There is another column (Column C) which is also 23 rows of numbers. I am trying to find a way for a designated cell that falls between the two numbers in Column A and Column B to display the number in Column C. The numbers would work their way across the row. For example, if cell A1 had a value of 1 and B2 had a value of 10 and cell c3 had a value of .034, if the designated cell has a value of 7, I would want the cell below the designated cell to show the value of .034. I can't use the if/then function because of the 7 limit. I also can't use the vlookup because there is a range of numbers instead of an equal to.
View 4 Replies
View Related
Feb 26, 2014
I am trying to create a macro, that ultimately does the reverse of one written a while back.
I have created a macro that exports "Roster_Data" to "envision_Roster" in .csv style formatting. What I want to achieve is reverse engineer the macro to return the data back to a similar state.
I am trying to transpose column D into rows that correspond with dates in column c in a sheet called "OutputView" this in essence is similar if not exactly the same as the original worksheet "Roster_Data"
I have split the macro I am working on into three separate modules.
Module 1 - Initial Export of "Roster_Data" to "envision_Roster"
Module 2 - Format and output worksheet to find MAX date and MIN date and produce top rows of data
Module 3 - Analyse, Undertake Logical Tests of data, and transpose to suit (Replicate the initial "Roster_Data" view)
I'm having trouble visioning this altogether so I've been starting with basic code to output basic stuff, but I still can't relate this back to my data.
[Code]....
View 2 Replies
View Related
Mar 6, 2008
I have a worksheet with various data autofiltered. I know when I filter on one of the fields, the drop down arrow becomes blue. If I filter on one or more fields, finding which fields I have autofiltered can become hard to find.
This is my question -- Can I put a button or some type of one touch command were I can take those autofilters off and return the worksheet backs to its original state before I autofiltered?
View 9 Replies
View Related
Jul 16, 2012
I'm trying to figure out how to write a VBA Match function that can look for multiple criteria and return the row number of a successful match.
I have about 255,000 rows of data on the worksheet "Filtered". Column B contains my Item Number and Column D contains the supply source. I want to find the row where ItemNumber and SupplySource match my variables and then return the value from Column C.
I can do a match for one criteria, but where I'm having problems is getting it so the two matches are on the same row.
View 4 Replies
View Related
Apr 18, 2012
In the frmula below, Is there a way for the operator to input a number between the range that is shown size of inducer wheel. Example: 2.277" could be inputed as 2.267 to 2.287" anything else would be rejected.
Air OutletGeometry# of Bolt HolesSize of Wheel Inducer
"0.010"
Noise SuppressorBackplate flange styleA/R ratioCastingPart #
StraightFixed62.277"YesFlat0.58AStraightFixed62.277"YesFlat0.58AVA-11-763 - 449633-0039
VA-11-763 - 449633-0039==IFERROR(VLOOKUP(J5,Cat,10,0),"******")
View 3 Replies
View Related
Aug 5, 2014
I am trying to use some vba match function code to return the column number of the matching date. The date will be stored in a date variable.
Every time I run this code I normally get a match error even though the date is in the worksheet and the variable matches that date.
See below:
[Code] .....
View 5 Replies
View Related
Feb 14, 2014
I have a spreadsheet that contains data for a fiscal year broken out by month and quarter. I want the formula to return the data from that month and use whatever the latest quarter is. For example in the data below:
If it has only pulled data only thru Q1 it would return Q1 for Jan-Mar, however once I have Q2 data I would want the formula to return Q2 for the months of Jan-Mar.
2013Thru Q1STOLISTOLIJan-2013STOLI BASE
2013Thru Q1STOLISTOLIFeb-2013STOLI BASE
2013Thru Q1STOLISTOLIMar-2013STOLI BASE
2013Thru Q2STOLISTOLIApr-2013STOLI BASE
2013Thru Q2STOLISTOLIMay-2013STOLI BASE
2013Thru Q2STOLISTOLIJun-2013STOLI BASE
2013Thru Q3STOLISTOLIJul-2013STOLI BASE
2013Thru Q3STOLISTOLIAug-2013STOLI BASE
2013Thru Q3STOLISTOLISep-2013STOLI BASE
2013Thru Q4STOLISTOLIOct-2013STOLI BASE
2013Thru Q4STOLISTOLINov-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2014Thru Q1STOLISTOLIJan-2014STOLI BASE
View 3 Replies
View Related
Apr 24, 2012
I have a rather large table that I need to return the column heading from.
Within the table I have a list of Names in the left most Column (A) and a list of Percentiles in the header row (row 2) (incrementing by 5%). By inputting the name and a cell value I am trying to return the column header.
Using an example;
5% 10% 15%
Bob 3.5 4.1 4.15
John 3.6 4.2 4.30
Chris 3.3 4.9 4.95
Sammy 3.7 4.6 4.7
Like I said, knowing the name and the cell value, I want to return the percentage value. So for example, Bob would be one input value, and another input value would be 4.1. I would want excel to return 10%.
I have found different suggestions using a combination of index/match but even using these functions I cannot get it to return the COLUMN value instead of a cell value.
View 8 Replies
View Related
Jan 15, 2014
The logic of the formula like this....
TAB X, Column A contains a data set, each row is a unique value. In column B of TAB X, I want to input a formula that performs the following logic: Search TAB Z, Column C, for an exact match of data contained in TAB X, Column A....and if found, look in TAB Z, Column D...if a blank cell is contained there, then in TAB X, Column A, say "Unsettled" ; however, if there does exist any character (not-blank) in TAB Z Column D, then in TAB X, COlumn A, say "Settled".....but, if the data ID listed in TAB X, column A is not found whatsoever in TAB Z, Column C, then say "NOT FOUND". So for example, within TAB X, Column A there is a unique ID in one of the rows "ABC123", then searches "ABC123" in TAB Z, Column C. If found, it looks in TAB Z, Column D and that cell is blank, so TAB X, Column B, same row that contains ABC123, the result is "Unsettled".
View 1 Replies
View Related
Jan 19, 2010
I m trying to do is this. For every person in company there is a table 30 days (cells) long, and 3 shifts (cells) wide. Every shift leader fills in the data in the "calendar" so every day, for every person, he will fill in number of hours.
I have to make excel process the whole calendar 30 x 3 = 90 cells, into final results for our payroll department. So the end product of the excel table should be Person name, total work days, total sick leave days, holiday days, and so on.
specific problem i have is how to calculate number of State Holiday days. You can see my best attempt in the attached file.
if there is only one number / mark entered each day (in only one of the 3 shifts) thansumproduct works ok.
View 8 Replies
View Related
Jan 18, 2008
I want to write code for a Checkbox that when checked it fills a cell with a color, but if it is subsequently unchecked, the cell color disappears.
View 9 Replies
View Related
Jan 23, 2008
I have a Worksheet_Change event like the one below. When Checkbox1 is ticked the it subtracts and when it's not, it adds:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intValue As Integer
If Not Intersect(Target, Range("W12:X24")) Is Nothing Then
intValue = CInt(Target.Value)
If CheckBox1.Value Then intValue = intValue * -1
Select Case Target
' Apples
Case Is = Range("W12")
Logbook.Show
Range("I10").Value = CInt(Range("I10").Value) + intValue
Case Is = Range("X12")........................
View 7 Replies
View Related
Mar 12, 2008
I have a checkbox and a command button on my sheet. I want that, depending if the checkbox is marked or not, I can call two different macros. I don't know if this is possible at all or maybe can be done easier otheriwise, but this would more or less be my idea of what should happen:
Private Sub CommandButton1_Click ()
' If CheckBox1 = marked
Call MacroA
'Else (CheckBox1 = unmarked)
Call MacroB
' End If
End Sub
View 4 Replies
View Related
Mar 9, 2009
I am adding rows of data to a worksheet based on criteria found in another worksheet. I've done this with two other worksheets in the same workbook without a problem.
In this case, I'm adding rows if the Utilization in the Master worksheet is >=150% and the logic I am using (which does not work) is:
View 2 Replies
View Related
Oct 22, 2007
I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.
There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.
NAME, PHONE, STATE
Bleh, 555-555-5555, =ChkState(B2)
I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field
Function ChkState(pVal As String) As Long
Dim AreaCode As String
Dim StateAbrv As String
AreaCode = Left(pVal, 3)
If AreaCode = "201" Then
StateAbrv = "Test201"
ElseIf AreaCode = "203" Then
StateAbrv = "Test203"
ElseIf AreaCode = "555" Then
StateAbrv = "Test555"
Else
StateAbrv = "0"
End If
MsgBox StateAbrv
End Function
I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.
View 9 Replies
View Related
Jan 18, 2008
In my spreadsheet the user has the option to put in a numerical value into different cells, which will update (add to) another. So if we have an initail value of 20 in "I10" and the user puts in 20 in "W12", then I10 will display 30.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("W12:X24")) Is Nothing Then
Select Case Target
Case Is = Range("W12")
Logbook.Show
Range("I10").Value = CInt(Range("I10").Value) + CInt(Target.Value)
Case Is = Range("X12")
Logbook.Show
Range("J10").Value = CInt(Range("J10").Value) + CInt(Target.Value)
Case Is = Range("W13")
Logbook.Show.................
View 6 Replies
View Related
Jul 11, 2012
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies
View Related
Feb 10, 2009
Formula: ......
I am looking for the value in column G, a formula that will search B-F and return the Group with the highest percentage for each zip.
View 3 Replies
View Related
Sep 8, 2007
Spreedsheet on on sales and taxes
Need formula for cells.
3 different cells involved... B1 will have $ amounts, B10 tax code, B25 will have total of tax applied due to code.
Exp; cell B10 will contain the tax code... if you enter..... "1" in the cell the calculation will be 7% in cell B25, enter "2" in cell B10 and the calculation will be 6% in cell B25 , and if you enter "3" in cell B10 the calculation will be 13% in cell B25.
View 6 Replies
View Related
Dec 8, 2011
I have a range of cells (D10:D20) with numbers in them. I need cell A10 to look at D10 and return a specific number based on a set of criteria.
The same thing would go for A11:A20 & D11:D20.
If the number in D10 is 1-2 then A10 needs to display 0
If the number in D10 is 3-8 then A10 needs to display 0.1
If the number in D10 is 9-14 then A10 needs to display 0.2
If the number in D10 is 15-20 then A10 needs to display 0.3
Once I have that formula I know that i will just highlight A10 and drag the formula down through A20.
View 9 Replies
View Related
Nov 17, 2006
I have a questioner in a excel spreadsheet. Column C have yes or no answers. If answer yes is implied then I want the number 2 to appear in Column F of no is implied then the number 3 will appear. I am having trouble writing the language for this.
View 2 Replies
View Related
Aug 27, 2007
I have a range of some 2,000 plus cells that are various numbers in them only. What I want to be able to apply is a macro that will look at the number in a cell and if the result is between a set range, produce a text, eg.
If the number is between 0 - 999 then insert text [url] this is where you go'.
If the number is between 1000 - 1999 then insert text [url] this is where you go 2'
If the number is between 2000 - 2999 then insert text [url] this is where you go 2'
etc.
I have up to 11 ranges of numbers to insert (up to 10000 - 19999).
So the text is the result of the formula.
View 9 Replies
View Related
