Comparing Multiple Thresholds
Jan 12, 2007
See the attached spreadsheet. the range D5:D20 is dependent upon the value entered in D2. the range K4:K64 contains all values I would like entered into D2. For each value entered in D2, the range E21:H21 calculates 4 statistical values. I would like these entered into the 4 columns in Table 2 (L,M,N,O). The first value has already been done. Can I automate this somehow to plug in all the threshold values I have listed in Table 2 (column K)???
View 2 Replies
ADVERTISEMENT
Dec 27, 2012
I have set of thresholds to which a value is corresponding. What I want to have is to get a corresponding value based on any input that falls within that threshold. I attached workbook which will probably be clearer.
I believe it's a SUMIF function with some &/>/< etc.
View 5 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
Jul 18, 2013
I have a single figure and as the figure increases and moves through thresholds the pay changes. e.g.
Number of sales payable = 15.66
Level 1: 1-10 pays 100 for each that falls in this threshold
Level 2:11-15 pays 150 for each that falls in this threshold
Level 3: 16-20 pays 200 for each that falls in this threshold
Level 4: 21+ pays 250 for all above this
I want to work the amount payable with a formula.
It should be $1849
Is there a formula that can calcualte this without me manually figuring out how many in each threshold.
View 3 Replies
View Related
Apr 13, 2009
I'm trying to do is develop a formula to:
- look at one column in sheet 1 and if a value in that column equals a value in another column in sheet2. then, display an X
View 6 Replies
View Related
Feb 11, 2009
I have two excel files. The data is arranged by column. I want to take 3 or more cells (from three different columns) from a particular row and match them up with three cells (in 3 different columns but in the same row). The three cells should be in the same row. However the matching row may appear in a different order in each of the two lists. When Excel finds a match, I want it to return true. How can I go about doing something like this?
I have attached a file explaining what I mean. I am trying to match the three cells in list one with three cells in one of the rows in list 2. However the number of cells in each list is different and there are some cells in list 2 which are not in list 1 and vice versa. There may also be extra columns, which I have not included for the sake of simplicity.
View 2 Replies
View Related
Dec 22, 2006
I have data in column A which is the correct data. I also have data in other columns that I would like to compare to column A. If the data is the same, then it moves beside it, if not then it leaves a blank cell.
I have looked for macros and formulas to do this for ages but i cant seem to find one. Here is an example
BEFORE
A B C D
1 1 2 1
2 4 3 2
3 5 4 4
4 7 5 6
5 - - 7
6 - - -
7 - - -
AFTER
A B C D
1 1 - 1
2 - 2 2
3 - 3 -
4 4 4 4
5 5 5 -
6 - - 6
7 7 - 7
View 9 Replies
View Related
Apr 8, 2013
I am working on a survey analysis and I'm having trouble comparing two columns.
Ex. 100 surveys
Column A being age (scale of 1-5)
Column B being willingness to pay (scale 1-5)
I am trying to correlate age with willingness to pay by saying =countif(a1:100 = "1" and (B1:B100 = "1" and "2"))
Basically checking to see if the 1st age group has a strong willingness to pay; I would repeat with B1:B100 = "4" and "5".
View 2 Replies
View Related
May 19, 2014
Is it possible to modify the sub "Continue" below so that the "If s.Name <> wsKeep" line compares multiple variables? The code is for the purpose of setting a workbook to a required state, i.e. hide all worksheets except ones stated in variables. I have further subs that would also benefit from me knowing how to do this.
View 2 Replies
View Related
Apr 20, 2009
I have a spreadsheet containing donation data for a non-profit organization with over 25,000 rows. The spreadsheet contains a lot of data, and is sorted by donor name, donation date (when the donation was made) and the feedback date (feedback on how the donation was used).
I'm looking to analyze how many transactions were made by a donor within 10 days of receiving feedback for a previous donation. The condition to check for is
(donor name is the same) AND (donation date >= feedback data for a previous donation) AND (donation date <= feedback data for a previous donation + 10 days).
How do I best perform this comparison? For instance, a donor may have made 20 donations, so I need to compare these donations amongst themselves and flag the ones where the donation date within 10 days of the feedback date for any other donation. I need to perform this exercise for each individual donor.
View 12 Replies
View Related
Feb 26, 2014
I am trying to find compare the value in one cell with an array of cells. My problem is when I try to carry the formula down to the next line - my array changes as well. For example:
Before
After
01915
[Code].....
So, if Before is Column A and After is Column G, the formula I have right now is =INDEX(A2:A3349,MATCH(G2,A2:A3349,0)). When I try carry the formula down to G3, G4 and so on, the array changes as well.
View 4 Replies
View Related
Jul 18, 2009
In one excel book I have 2 sheets.
1. One sheet (request type wise) contains request type and the phase which it belongs to. eg request type A belongs to phase 1 and request type B belongs to phase 2. (like wise there are 212 request types divided into 8 phases)
2. The second sheet contains raw data (request type) where the request types are randomly arranged.
What I am trying to do is:
Compare the raw data of sheet 2 with standard data in sheet 1 and paste the phase to which it belongs to with a help of macro/code, since the standard data (212 request types with 8 phases) is huge.
View 9 Replies
View Related
Feb 28, 2014
I'm am trying to take multiple column-groups and sort them by product ID number.
I am trying to put together a sales record for the past 26 months for around 2000 different items and sorting them manually would obviously be a nightmare.
I would like to sort them so that all the drugs with the same NDC (prod ID) have the correct data for each month displayed on the correct row for that NDC. Right now I have each set of data for that month sorted by product ID number but some months we used certain products and others we did not. Is there any sort of Macro that I could use? What I am trying to do I attached two files. The "before" and my desired "after".
starting point.xlsx
end product.xlsx
View 1 Replies
View Related
Apr 23, 2012
[1] compare (row) ranges across two columns with an unequal number of rows (column A [number] to column C [number])
[2] save each result of [1] where there was a match in column B
[3] for each row where there was a match (now stored in B), compare the value from the same row in column D (date) with the same row of column F (date), and store the result in column E
[4] count and message box the final number of matches from column E
Data Example:
A B C D E F
----- ----- ----- ----- ----- -----
12345 match 12345 11/1/2011 match 11/1/2011
77777 34345 ... ...
A and C numbers match, so check same row date in D that does match date in E, place "match" in E, then count and display.
Sub other()
Dim range1, range2, range3, range4, range5, range6 As range
Dim x As Variant, y As Variant, z As Variant, a As Variant
[Code].....
View 1 Replies
View Related
Aug 8, 2012
I'm using Excel 2010 on Windows 7.
I have 2 worksheets. One has Employees and the devices they have. Last name, First Name, Device each in their own colulmn. Many have more than 1 device so they have multiple entries on seperate rows.
Another worksheet has Employees and thier location. Last name, First Name, Location. Again, all on seperate columns.
It would look something like this
Sheet1
Lastname
Firstname
Device
Johnson
[Code]...
So I'm tasked with combining them into 1 sheet with last name, first name, device and location. The issues I'm having are:
1) A team member could have multiple devices
2) A last and/or first name can appear many times, so a simple Vlookup against lastname won't work - it has to somehow also compare against both.
View 6 Replies
View Related
Dec 3, 2008
I need of a formula that will look through a list of text strings, spread out through two columns) and compare each one to a user defined entry. When it finds a match against this entry I would then need it to compare another set of text strings on the same row as the entry that it previously matched against.
If it was to find a match on only the original text string it would need to return a true result and if it matched on the original text string and also one of the other text strings on the same rowit would need to return a false result.
I currently have a formula to compare all text strings against the user defined entry and if it is contained in the first 2 columns it returns a true result and if it is contained in both ranges of text strings it returns a false result but i need it to apply the rules per row.
Below is the formula that I am currently using if this helps as a starting point. If you need a subset of the data I can supply this.
Current formula:
=IF(SUM(IF('HBO Frequency Projections.xls'!NOT_HOMEBASED"",IF(ISNUMBER(SEARCH('HBO Frequency Projections.xls'!NOT_HOMEBASED,C5)),1)))>0,"NOT HBO",IF(SUM(IF('HBO Frequency Projections.xls'!HOMEBASED"",IF(ISNUMBER(SEARCH('HBO Frequency Projections.xls'!HOMEBASED,C5)),1)))>0,"HBO","NOT HBO"))
View 9 Replies
View Related
Oct 13, 2006
I am working on formatting a spreadsheet report where the values will change in column A. Here is what I would like to do via a Macro. Compare the cells in column A (e.g., compare A2 to A3, compare A3 to A4, and so on). If the values between the two cells in column A are different, insert three blank rows and set the active cell to the next cell following the blank lines. Example:
if cell A5 is different from A6, insert three blank rows below row 5 and new active cell is now A9 and the comparison would start again. I have been trying to code the macro for this but with no success. Here is the macro I have been working on.
Sub Macro1()
Const NumRow As Integer = 3
Dim StartCell As Range
Dim RowNR, NewCnt As Long
Dim RowCount As Long
Dim Count As Long
Dim intRow As Integer
Dim bFmtComplete As Boolean
RowCount = Application.WorksheetFunction.CountA _
(Range("A1", Range("A" & Rows.Count).End(xlUp)))
bFmtComplete = False
RowNR = 2
Range("A1:J1").Select
' Rows("1:1").Select
Selection.Copy................
View 4 Replies
View Related
Jan 22, 2014
Attached is an example.
I get data pulls of logged in time, and shift start times.
Example in column C the person's actual login time is there, and column D has their start time. If there's a variance of 'x' how could I have column E display the variance? In this example 15 minutes?
Basically, if there's a difference between the 2, I need a formula that tells me what difference is.
Attached File : Login Time.xlsx‎
View 3 Replies
View Related
Jun 10, 2008
I have one .xls file that contains an inventory list of all the products that I want.
Unfortunately this .xls file does not contain the prices for these items. I have another .xls file that contains SKU numbers and the prices related to those SKU numbers.
Is there a way to run every single SKU in the first file against the second file and, when a match is found, take the entry in the price column of the second file and place it in the first file so that my first .xls file contains all my products with prices?
I included sample data.
View 12 Replies
View Related
Nov 5, 2008
Comparing 2 values.
I need to compare the value's on 2 cells. I have a sheet setup for my sales I would like to compare each day of the week. So in other words I would like to compare this Monday with last Monday and know what the percentage of my sales ether up or down is.
Example:
Last Monday = sales $100.00
This Monday = Sales $200.00
100%
So If sales are up I would like the percentage amount font to be in green. But if sales are down I would like the percentage amount to be in red.
View 2 Replies
View Related
Apr 6, 2009
I have two spreadsheets I would like to compare against each other, last weeks inventory (Sheet 1) and this weeks (Sheet 2). All items are listed by unit number, is there a way to have all unit numbers on Sheet 1 colored red that are not on Sheet 2 and vice versa?
View 5 Replies
View Related
Nov 16, 2009
Is there a way to compare two cells and return true if they are the same, false if different? There is text and numerical data. As a side note, can anybody recommend a lightweight reference so I can sit for an evening or two and at least get an idea of what excel can do?
View 3 Replies
View Related
Mar 30, 2009
I have two string that I want too compare.
String 1 could be something like channel_0_user_defined._system
string 2 could be channel_0_user_defined
I want too compare string2 with string 1 at the left side of string 1.
I don't really know what I should use I found startswith, but vba doesn't have this particular function.
View 12 Replies
View Related
Jun 9, 2009
I have two Excel lists. One master list (list A) contains all our email addresses from our customers. The other list (list B) contains a list of people who do NOT want to recieve emails. How do I take the emails from list B (there are 1,200 of them) and compare them automatically to list A? Basically if any email address from list B appears in list A, I want it to delete in list A.
View 6 Replies
View Related
Sep 15, 2009
i have two worksheets. group and search. in group sheet it contain are groups of club & nation. in sheet2 when click the button find it will prompt player name. for example,when i put torres it will tell us that he belong to Liverpool club & Spain.
View 5 Replies
View Related
Sep 14, 2008
say i have 4 columns of 5 values each consisting of 0's and 1's.
...a b c d
v 0 1 1 1
w 1 0 0 1
x 1 1 1 1
y 0 0 1 0
z 1 1 0 0
how would i write a function that would compare each value under column 'a' to each value under column 'b' and then return a value of 1 if they are equal, and return a value of 0 if they are not equal, then add up the return values.
(comparing column 'a' with 'b' you would get a result of 3.) then have the function continue on to compare column 'a' with column 'c'..'d; comparing column 'b'..'d', etc
so in long form: IF(av=bv,1,0) = 0
IF(aw=bw,1,0) = 0
IF(ax=bx,1,0) = 1
IF(ay=by,1,0) = 1
IF(az=bz,1,0) = 1
SUM(IFs) = 3
View 13 Replies
View Related
Apr 3, 2012
I have two lists of data and am trying to write a formula that will return a "1" if the second list firstly contains one or more entries that are not in the first list and secondly the fourth cell on the same row of one or more of the entries not in the first list contains an "F".
The first list is in a sheet called "Select" cells J2:J7 and the second list is in a sheet called "Model" cells A2:A50. The second list will occasionally contain blank cells.
I have tried to use a combination of COUNTIF and VLOOKUP but am struggling to make any progress.
View 2 Replies
View Related
May 5, 2014
1) if Column M has the value "School" then corresponding value in column N should have oly "Bus","Subject","Teacher". if values in Column B other than "Bus","Subject","Teacher" then the cell should populate RED Color.
2) if Column M has the value "College" then corresponding value in column N should have oly "Box","Madam","World". if values in Column B other than "Box","Madam","World" then the cell should populate RED Color.
View 5 Replies
View Related
Mar 2, 2007
Here's one:
Column C (starting at C6) contains the team number. I want column B to find out if the team number is an AM, Mid or PM team.
I have listed the AM teams at J1:U1, the Mid teams at J2:K2 and the PM teams at J3:V3.
How can the formula in column B search those ranges and determine if the team in column C is AM, Mid or PM?
View 9 Replies
View Related
May 26, 2007
Files :- [url]
i have upload 2 worksheets (in a zip file)
i have attached my 2 files,
1 is report of Feb 2007 & other is March 2007
on 1 Branch,
i got 200 Branches reports & i have to compare the people recored and have to identify following things
1) in which Days bukket is fall in Previous report ( compare with current)
2) if the Account # doesnot match then it should be HIGHLighted!
what i have to do is to compare the Account # with Previous & add a New Column & write the previous days (in number) plus highlight or show some remards that doesnot exisits in last report,
View 11 Replies
View Related
