Determining Given Value In Particular Range
May 13, 2014
I've got a formula which makes a word "Order" (column B) meet the closest value of 35.00 (column A)
How to modify the formula so that ''Order" meets the closest value of 35.00 in the range (>=35;<39) throughout the column?
There are about 20 approximate and precise values of 35.00 in the column A which have to be met by "Order".
I've been trying to change the value comparisons and precisity (pecentage) to set up the range from 35.00 till 39.00.
but encountered a problem that "Order'' often meets two closest values of 35 till 39, often one of them is going under 35.00 e.g. 36.50; 34.00.
Consequently how to change/substitute the formula, parameters, value comparisons etc. to meet the requirements!
See the workbook attached. 13_05_det_closest_value.xlsx‎
View 2 Replies
ADVERTISEMENT
Jun 19, 2012
I am putting together a spreadsheet and I want to loop through a series of columns (G to L let's say) and in those columns I want to look at a range of rows (4 to 17 let's say). And if that range has no values in it, I want to hide that column and then move on to the next column. I am having a bit of trouble figuring out how to determine if the range is blank and then building that into a loop.
View 5 Replies
View Related
Sep 13, 2013
I've recorded a macro that selects a bunch of cells so I can work with them. However, it's hard-coded to the bottom cell of H1551, and I need it to work no matter how large the range is.
Code:
''' Concatenate column H with B & F
Application.Goto Reference:="R2C8"
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-6],"" "",RC[-2])"
[Code]....
View 4 Replies
View Related
Aug 14, 2014
I want to determine the range in the countif function automatically and relating to a date (i.e. today).
In the attached sheet there are two employees who worked during a certain period, a day worked is 1
Then I would like to count how many days each employee has worked up until today, counting the 1's in the row of that employee until today.
View 5 Replies
View Related
Aug 16, 2014
Let's say you have a named range, Rng1, which consists of cells A1 & A2. In vba how would you report back what, if any, named range the following cells resides:
Code] .....
here are multiple named ranges so using intersect is not feasible. Essentially, through code, I will be given a range and I need to determine if that range if part of a named range.
View 5 Replies
View Related
Apr 21, 2006
Let's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.
If:
A1 contains beginning date of employment
B1 contains ending date of employment
C1 contains specified beginning date (criteria)
D1 contains specified ending date (criteria)
View 4 Replies
View Related
Apr 3, 2007
What code can i use to determine the same rows in 2 different columns and compare the data in those two cells?
View 9 Replies
View Related
Aug 22, 2014
I have a document in which I am trying to determine the length of time between a start date & time and a finish date & time. The format of the time/date cells is
mm/dd/yy hh:mm:ss
Basically, I am just subtracting the first cell from the second. This works fine as long as both dates are in the same day, such as
START FINISH ELAPSED
08/20/14 23:42:22 08/20/14 23:43:59 0:01:37
However, if I have a situation such as
START FINISH
08/18/14 23:00:15 08/19/14 0:03:22
the ELAPSED cell fills with ######## with a tooltip stating that "dates and times that are negative or too large display as ######." The actual result in this example should be 1:03:07.
How do I get consistency amongst my resulting formulas?
View 6 Replies
View Related
Nov 25, 2013
I am trying to figure out a simple formula on determining class for students according to their achievement /grades. There are 9 classes altogether ( 7A, 7B, 7C, 7D, 7E, 7F, 7G, 7H and 7J). There are three columns for subject ( MATHS, ENGLISH, SCIENCE). So lets say the student get Grade A, A , A : The student will belong to Class 7A.
As attached.. TEST PSR.xls‎
View 2 Replies
View Related
Apr 22, 2009
My question is about determining whether or not a particular letter is uppercase.
I've written code that reads from a text file one character at a time. And I know that the following determines if each character is equal to the letter "a".
View 7 Replies
View Related
Jun 11, 2009
I try to do the following I get an error 'Subscript out of range'
View 3 Replies
View Related
Dec 15, 2008
I am trying set up my spreadsheet so that when a number is put into one column. The next column is filled with a number that will correspond to the other number specified from a range of data.
Distance Sheet no.
55
68
93
135
186
So in the sheet No. column I want it to read the number in the Distance and put in the Corresponding sheet number.
So if:
Sheet 1 is : a distance of 1 though 20
Sheet 2 is : a distance of 20 though 40
Sheet 3 is : a distance of 40 though 60
Sheet 4 is : a distance of 60 though 80
Sheet 5 is : a distance of 80 though 100
Sheet 6 is : a distance of 100 though 120
Sheet 7 is : a distance of 120 though 140
Sheet 8 is : a distance of 140 though 160
Sheet 9 is : a distance of 160 though 180
Sheet 10 is : a distance of 180 though 200
How can this be automated without writing a ton of nested if statements.
This is an example but in reality I have 29 "sheets" with different ranges to separate.
View 2 Replies
View Related
Mar 31, 2009
I have about 20 files with check posting dates stretched over several years. Maybe a million checks total. The company uses 13 four week periods each year, not a calendar month/year. I need to look up each posting date and attach the correct period to it.
I could do a VLOOKUP with one row for each day of the year, but that would require 2 x 365 row's for just two years. There must be a more elegant way, maybe using an array or some IF statement.
Attached is a sample file, xlsx, with the perior/year info and sample posting date info. Can someone point me in the right direction so that I end up with the correct period/year in column 'C' for the applicable posting date?
View 4 Replies
View Related
Jun 3, 2009
There are a lot of worksheets with a lot of similar data from which I have to determine the threshold. So if possible I would like to create a macro.
I want to know the threshold value when Y=2.
The threshold can be between different Input Values (=Stimulus Intensity (µA)).
For example: the threshold of amp P2-N1 & P3-N3 lies somewhere between 200 and 252µA. The threshold of P1-N1 lies somewhere between 318 and 400µA.
Is it possible to determine the threshold value with a formula?
View 14 Replies
View Related
Dec 9, 2011
The table I'm trying to query contains the beginning and end date the data is effective. The end date seems to always be either 27 or 34 (4 or 5 weeks) after the start date.
This is the table I'm trying to query:
[URL]
I want to have it query the whole history going back 2 years, and be refreshable by other users on demand.
If I put in dates that do not match up exactly with the start/end dates they have set, I get something like this:
[URL]
I've got a plan of how to approach the data extract, but how can I determine whether or not the web query found a specific table (and should therefore try a different date offset)?
View 1 Replies
View Related
Mar 4, 2014
I have a situation where I have a column with 10 numbers in it... say A1 - A10 each contain an equation that will solve to a numerical value. If one of these does not solve and gives me a "#VALUE!" response, how do I tell a function to look for that? I'm specifically attempting to use Match to find it, in this case "=MATCH("0",A1:A10,0)".
View 4 Replies
View Related
Sep 14, 2007
In cell A1 I have a date entered as text as "Apr 2007". (That's the way my tool pulls it. Format can be changed if it helps)
I was able to pull the Quarter and year (Q2 2007) using...
A2 ="Q" & ROUNDUP(MONTH(A1)/3,0)&" "&YEAR(A1)
I need to pull the next three quarters and their year. (Q3 2007, Q4 2007, Q1 2008)
View 9 Replies
View Related
Jun 27, 2006
I am tracking attendance of individuals in an Excel spreadsheet. I would like to determine how many consecutive days each individual has attended. The spreadsheet has two pertinent columns which contain the individual's name and the date of attendance. How can I determine the number of consecutive days of attendance?
View 5 Replies
View Related
Jul 8, 2006
How can I determine if the user selected "disable" at the initial macro prompt screen.
Basically, I would like a formula/function that can say whether Macros are switched on or off by the user.
View 9 Replies
View Related
Aug 7, 2006
I have a spreadsheet with a variable number of xlButtonControl type form controls. The actual number of buttons depends on the amount of data that the spreadsheet is being asked to summarise. All the button controls use the ".OnAction" property to point execution on click to a common procedure.
Unfortunately that much is what it is and is beyond my control, so I just have to work with that.
What I would like to know is if there is a property or method I can access from within the common OnAction procedure that enable me to identify which button was pressed. If this was a CommandBar environment I would use the ActionControl property. Since it's not, and since I can't change it (not even to use ActiveX controls!), does anyone know an equivalent that I can use in this case?
View 4 Replies
View Related
Sep 14, 2008
Without VB, is there a way to write a formula which when given a certain month, will output the date of, let's say, 2nd or last friday of the month?
View 14 Replies
View Related
Dec 31, 2009
I have a column of values 2000 rows deep, and in separate column would like to determine the relative magnitude of any given value compared to the rest of the values in the column. So for example, if the highest value is 23.3, the number that would be returned is 1 (meaning this is the max value), if the lowest value is 12.4 the number that would be returend is 2000 (meaning this is the min value); if the value is the 12th highest, then the the number that would be returned would be 12 and so on.
View 2 Replies
View Related
Aug 6, 2013
I have set the default in Excel to move across to the next cell after I press Enter. This is fine for most workbooks but I have a particular workbook in which it would be preferable (save keystrokes) for the cursor to move down one cell. Is it possible to have one action as the default but another for a particular workbook?
View 7 Replies
View Related
Dec 3, 2013
I have a list of data that is the day of the month(1-31) that an event has occurred. (sample data in attachment) I want to find some way to find when the "hottest" time of the month is when the events are occuring.
how to get the end result. I think for myself a chart of some sort would be good to see visually, or some kind of statistical breakdown.
My only requirement is that the list is always being added to, so the end result needs to be updating with the complete list.
Im not an expert in excel, so the more details on how to do this the better. Even better would be creating it in the attachment, and uploading your version so I can see exactly how you did it.
I am going to be taking this idea and apply it in the same way for day of week (1-7) and time of day.
View 3 Replies
View Related
Aug 6, 2009
I have a function that I call with the name of a shape that exists on my worksheet.
I actually select that shape on the worksheet, then execute a macro and since it is a single shape selection, it is easy to get its name:
View 10 Replies
View Related
Feb 18, 2010
I'm working on this sheet and I can't figure out how to do it.
I'm trying to determine the variable "selling price" on the basis of given values, buying price, Net profit and Expenses.
View 4 Replies
View Related
Dec 17, 2013
Currently using "=IF(CC3=CF3,1,0)"
I would like to loosen this up by +/- 1.5% of the difference between the two cell values would produce a positive result.
View 3 Replies
View Related
Feb 13, 2007
In an error handling procedure, I want to be able to capture the name of the Module and Sub procedure (macro) that the error has occurred in.
Is there any way to do this, outside of storing the name of the Sub somehow in a variable?
The Err.Source method seems to only contain very broad information, such as "VBA Project" when an error is encountered.
View 9 Replies
View Related
Aug 7, 2007
I have 5 columns AS-AW. Column AS contains a letter (either A, B, C, D, E, F, or G) which is generated by a VLookup formula. Columns AT-AW contain numbers (from 1 to 6) generated by a conditional formula. I need column AX to combine column AS with the smallest of columns AT-AW. Is this possible?
View 9 Replies
View Related
Feb 10, 2009
below the following lists (Name, Week, Amount) I am trying to calculate a sum of the Amounts column (Column D) for two conditions: a particular week # (Column C), for a particular color (Column A).
Is there a simple formula to SUM numbers when it is based upon two conditions (color and week #)? For example, there are two separate RED amounts that should be received in Week 4 - how do I write a formula for the cell that correlates RED and WEEK 4 that states "sum the amounts for the Name: Red and Week: Week 4"?
NameWeekAmount
GreenWeek 1 5,000
RedWeek 4 13,000
BlueWeek 1 1,500
GreenWeek 3 3,500
GreenWeek 2 4,200
PurpleWeek 5 1,200
YellowWeek 4 500
GreenWeek 3 25,000
RedWeek 4 13,000
RedWeek 3 12,500
RedWeek 1 10,000
BlueWeek 5 9,000
BlueWeek 3 14,500
YellowWeek 5 3,300
GreenWeek 2 1,000
YellowWeek 4 30,000
Week 1Week 2Week 3Week 4Week 5
Red
Yellow
Green
Blue
Purple
TOTAL
View 9 Replies
View Related
