Weeknumbers - Not Work At Year End When Comparing Values
Jun 16, 2014
I am using a formula for weeknumbers and formatting cells based on their values but have just realised it will not work at year end.
The formula in D2 is as follows:
=IF($C2<$O2,"green","blank")
C2 is the weeknumber of an incident based on the actual date in B2 (e.g. 01/06/2014) and uses the formula: =WEEKNUM(B2). O2 is the weeknumber of the current day and uses the formula: =WEEKNUM(NOW()).
So cell D2 states "green" if the incident happened over a week ago, and states "blank" if the incident happened in the current week.
If I used this at year end when the incident weeknumber may be 52 and the current weeknumber may be 1 - the formula would not work as it would state "blank" rather than "green", even though the incident happened over a week ago.
How can I get the year encorporated within the weeknumber?
View 4 Replies
ADVERTISEMENT
Jan 24, 2014
Work has given me a hourly sales data for the last 2 weeks, and Last years hourly data for the equivalent 2 weeks.
The question is compare the average of the last 2 weeks against the average of the equivalent 2 weeks last year and evaluate if the trading performance is majorly different.
What formulas to use? Should I use percentages or leave it?
View 6 Replies
View Related
May 31, 2007
What I am looking for is a macro/formula that will look at a unit number, then the type of work done, then the completion date and decide if the same type of work was done on the same unit within a 7 day span of time.
Example
Unit is 11111, work is plumbing completion date is 10/4/07
Unit is 11111, work is plumbing completion date is 10/7/07
This means the work has been done 2 times (obviously showing that it did not get fixed the first time)
Is there any way to mark an X in the square with the formula, or any type of identifier what-so-ever
I have attached a spreadsheet and hope it helps
Again I know this is complex so it is OK if no one feels like messing with it but I wanted to check here first.
View 3 Replies
View Related
Dec 25, 2013
Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.
View 10 Replies
View Related
Mar 2, 2014
I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".
The sample sheet has been attached for reference.
View 3 Replies
View Related
Sep 28, 2012
Got a road block on one of the trackers which I am trying to create which compares some of our agreements. Basically we need to compare current production with our contracts and then bring the tier level they are at in order to then calculate how much in overrides we receive. Belwo is an example:
Input Value = 140000
Out Should be = 1.5%
Set of Values 1
T1
T2
T3
T4
[Code]...
In a nut shell it will look for 14000 in set of values 1 and pick T3 to then go in set of values 2 and display corresponding T3 which will be 1.5% in above example as output.
View 2 Replies
View Related
Sep 14, 2008
on a sheet i have 2 column with values.
For e.g
DevID DevNum
123 s123
123 s234
987 sabc
987 sabc
Is there a way where i can compare the values in DevNum with the same DevID
to see if theres any duplicated values using macro?
View 10 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
May 28, 2014
I have a football score system and wish to compare the values of various cells - 2 if possible, but more if not possible.
Essentially I have a list of predictions for the score of a game (e.g. 2-1, 1-0, 0-0, 2-2 etc) in a spreadsheet, and I have a cell where I enter the correct scoreline (e.g. 2-1). The way the points system works is that if the prediction is correct (i.e. 2-1) then that player gets 3 points. If the result/outcome is correct but the scoreline is incorrect (i.e. 1-0) then the player gets 1 point. If they result/outcome and scoreline are incorrect, then that player gets 0 points.
Ideally I would link to compare 2 cells - predicted score and actual score, where the format of the scores is entered as 2-1.
However, I feel this may be easier having to columns for the score - Column A = 2 Column B = 1. In this way the scores only need to be compared cell by cell, however they would need to be compared by value for the correct outcomes.
View 5 Replies
View Related
Jul 13, 2013
There are two columns A and B and values there in
A B
1 1
2 2
3 3
4 4
9 5
10 6
11 13
15 15
As you can see there are certain numbers that are missing between column B and A. I want to know which of the numbers that are present in column B are missing in column A ...any handy calculation ?
View 3 Replies
View Related
Jun 25, 2014
How would I write a IF statement comparing 3 values. For example the field that it would be referencing would have values cell E51(1,2,3,4,5,6,7,...99). The pull back would be:
1-10 = $250,000
11-15 = $550,000
16+ = $750,000
View 2 Replies
View Related
Dec 13, 2005
I work for a financial firm. Every morning I compare today's cash balance file (sent from our custodian) with yesterday's to see if there is anything different in each portfolio cash balance column. Right now I'm doing it manually checking row by row. It'll become tedious if more and more portfolios are added. I'm looking for an efficient way I can use the portfolio codes column (as a key) to compare with previous day's file’s portfolio codes column , and list only those portfolios(in another workbook) if the corresponding cash balance column doesn't match. This is what I started doing:
Private Sub compare_cash_bttn_Click()
Dim b1 As Workbook, b2 As Workbook, b3 As Workbook
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet
Dim i As Integer
i = 2
Set b1 = Workbooks("CashA")
Set b2 = Workbooks("CashB")
'Set b3 = ActiveWorkbook
Set w1 = b1.Sheets(qry4rCash).........................
View 9 Replies
View Related
Jul 31, 2013
I am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
August PM Schedule Demo.xlsx
View 2 Replies
View Related
May 31, 2007
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
View 12 Replies
View Related
Apr 27, 2009
I have two columns A and B. I need a count of how many times a value in B is greater than the same row in A. I don't want to insert a new column (e.g. for if statements to add up).
Can I insert a countif at the top to do that? I tried various countif variations but could not find the right one. Additionally any other option that does not involve adding new columns/rows.
View 5 Replies
View Related
Nov 4, 2009
I have a cell (F2) that is auto updated with total sales units for today (Its a simple counter of individual sales). I want to detect when there is a "batch" of sales >= 10. ie. F2 may start at zero for the day and increment by 1,2,5 or 10 units at a time. If the increment is 10 units or more I would like to detect this and store the result in cell G4.
Typical values in C2: 0, 1, 6, 7, 9, 19, 21, 22 etc as new orders arrive. The larger order between 9-19 (viz 10 or more), I need to detect and store in G4. I know that I need to use both the current value of F2 (say 19) and the previous value of F2 (say 9) to perform this, but the solution eludes me.
View 2 Replies
View Related
Feb 19, 2010
I’m attempting to find values that are within a band of .001 of the values of cells in columns L through O and searching columns B through I for values that meet the .001 criteria and copying the values to columns Q, R, S, and T respectively.
I mentioned the values in L, M, N and O are where the comparisons will be made and these values are tagged to a specific date in Column K. I need to find the dates in Column A that are in between the values of K and the cell beneath it. For example, in K4 I have a date of 10/3/99 and the next date I have in K5 is 10/5/99. I would then query Column A for the dates 10/06/99 (A4), 10/07/99 (A5), and 10/08/99 (A6). The values I need to find within the .001 band are in Columns B through I (B4:I6). Lastly, when reach the last value in Column K, query all values in Column A greater than last value in K and copy the appropriate values in B through I to Q through T.
Rules for copying data to Column Q, R, S, and T:
For Column Q-
Find values in B, C, or D that’s within plus or minus .001 of M and copy the value of B, C, or D into Column Q of the same row.
For Column R-.................
View 5 Replies
View Related
Nov 13, 2009
I'll try my best to explain what I'm trying to do here using Excel macro (i've very limited knoledge in VBA ). I am working on a project where I'm using an actuator to measure micro-switches. The values are then extracted using HyperTerminal via RS232 and transferred to Excel to filter the values and display only the ones that are important.
Here is what I'd like to do via a Macro:
Compare cells in column A (e.g A1 and A2, A2 and A3, A3 and A4 etc.)
Example:
A
0
300
550
1200
1700
1900
2200
2000
1850
[Code]....
So first, if the values increase, do nothing and keep comparing other values. If we find the values decrease, display the larger value between the two, in this case it's the value 2200 that we want to display.
Next, the values will be decreasing progressively. We'll do the comparison between 2 values but this time if the values decrease, do nothing until we find a value increases. Display then the smaller value between the 2, here it's 1500.!
View 9 Replies
View Related
Dec 30, 2006
The following formula (thanks to Barrie) determines if the difference between 2 values equals a third value, within a fourth ‘margin of error’ value, and returns either True or False.
=AND(C1-D1
View 9 Replies
View Related
Nov 13, 2009
I'm trying to do here using Excel macro (i've very limited knoledge in VBA ). I am working on a project where I'm using an actuator to measure micro-switches. The values are then extracted using HyperTerminal via RS232 and transferred to Excel to filter the values and display only the ones that are important.
Here is what I'd like to do via a Macro:
Compare cells in column A (e.g A1 and A2, A2 and A3, A3 and A4 etc.)
Example:
A
0
300
550
1200
1700
1900
2200
2000
1850
1750
1600
1500
1950
2500
4000
So first, if the values increase, do nothing and keep comparing other values. If we find the values decrease, display the larger value between the two, in this case it's the value 2200 that we want to display.
Next, the values will be decreasing progressively. We'll do the comparison between 2 values but this time if the values decrease, do nothing until we find a value increases. Display then the smaller value between the 2, here it's 1500.
View 9 Replies
View Related
May 11, 2007
I am aware this question must have been asked to death on this forum and I already searched and found some answers but for some reason I can´t get it to work. I have two worksheets (sheet1 and sheet2). In Column E of Sheet1 I have product descriptions that I want to compare cell by cell to the product descriptions in Column F of Sheet 2. If they are not the same, color in the cell in Sheet 2. There are 1810 rows in both columns. I tried this method but it gives me an error (in spanish) that says that I cannot compare ranges of data.
I have also seen some code to do something similar (http://www.ozgrid.com/forum/showpost...85&postcount=3 ):
Sub find_cell()
Range("A:E"). Find(What:=Cells(2, 6).Value, LookAt:=xlPart).Activate
ar = ActiveCell.Row
ac = ActiveCell.Column
' the two lines above store the row and column values of the cell found
' in your example ar is going to be equal to 229 and ac is 3
' now this following lines are to change the colors to red
With Cells(ar, ac).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End Sub
I don´t quite get the "Range("A:E").Find(What:=Cells(2, 6).Value, LookAt:=xlPart).Activate" part though.
View 7 Replies
View Related
Mar 25, 2013
I need to hide the rows in an excel whose value is equal to the array list that has been already hardcoded...How to do this...
View 1 Replies
View Related
Apr 22, 2014
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‎
View 3 Replies
View Related
May 29, 2014
I am facing a problem in evaluating a problem. I am referencing from the screenshot of excel file. I want to replace the values of Column-C for which column-A matches with column -G. And, the remaining will remain same.
Capture.jpg
I am also attaching the excel file.
View 5 Replies
View Related
Feb 24, 2014
Am looking for a technique to print unique values by comparing 2 columns
Below is example
Col A
Col B
Col C (Output expected)
1
4
1
[Code]...
View 2 Replies
View Related
Jul 2, 2014
I'm trying to compare the values of columns B and G and if any of the cells are G < B, I would like to move the whole row into the second worksheet. And only if possible, it would be awesome if the macro could check column A for duplicates before the value comparison and sum up the column B if there are duplicates. In my example row 6 and 7 on column A have the same suppliers code and the amount totals to 2.
In this case for example, the value of the third row of G is lower than B, and therefore I would like the whole row 3 to be cut out from worksheet 1 and moved to worksheet 2.
Original view of sheet 1:
SUPPLIERSCODE
ORDERAMOUNT
PRODUCTNAME
BARCODE
SUPPLIERSCODE
WAREHOUSE STOCK
And then cut out the rows in which G
View 9 Replies
View Related
Jun 12, 2014
I am trying to compare to values that a user inputs in a user form. It's ok if they are not the same I just want the user to know it. I am only including the part where the if statement is failing:
VB:
If Me.VSLongQuant.Value <> Me.VSShortQuant.Value Then
MsgBox VerUnBal
If VerUnBal = vbNo Then
Exit Sub
End If
End If
I have VerUnBal as Long and defined as:
VB:
VerUnBal = MsgBox("The Long and Short option Qunatities are not equal." & Chr(13) & _
"Is this intended to be an unbalanced vertical spread?", vbYesNo, _
"Unbalanced Spread Alert")
I can provide more info as necessary. There are two problems 1) the message box is appearing whether the numbers are equal or not, and 2) clicking No in the message box does not cause the Sub to exit.
View 6 Replies
View Related
Jan 23, 2014
I have a list of over 2000 names and need to establish which names from column A appear in Column B, as well as which do not appear in column B.
I attach an example : NameCheck.xlsx‎
View 3 Replies
View Related
Nov 26, 2012
I'm new to macros and VBA so I am wondering if it is possible for a macro to compare values in column B of sheet2 and column B of sheet3 and for the values that DON'T match i.e. unique values to column B of sheet2, copy row from sheet2 to sheet4? This will aid my processing time drastically if I can copy all the rows from sheet 2 to sheet 4 where the model number in column B of sheet 2 doesn't appear in column B of sheet 3!!
View 3 Replies
View Related
Sep 18, 2013
If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).
Example: D7=Bob and D8=25
D11=John and D12= 40
D14= 45
D16 (answer cell) =John
OR:
D7=Bob and D8=46
D11=John and D12=48
D14=45
D16 (answer cell) =Bob
View 2 Replies
View Related
