Conditional Entries
Jul 18, 2007
I'm trying to evaluate the last 8 entries of a particular column of data that I regularly update (K4:K5000). This data is filled with numbers ranging from about -50 to 50 and will occassionally have a blank entry.
I'd like to evaluate these last 8 entries using a couple of different conditions.
#1 - In column D4:D5000, I have three entry values - X, Y, or Z. It will never be blank. I would like for the Excel to automatically review the last 8 entries of columns K and D and do two things...
a. provide an output of a count of how many X, Y and Z there are for the last 8 entries (format: 4-3-1)
b. provide an output of an average of the values of K for each of X, Y, and Z... (i.e., avg of X = 4.4; avg of Y = 2.1; avg of Z = 10.7)
#2 - Next, I would like to add a third column to the mix (Column C4:C5000 with entries >0 and 0, Y>0, Z>0 there are for the last 8 entries (format: 4-3-1)
b. provide an output of an average of the values of K for each of X>0, Y>0, and Z>0 (i.e., avg of X>0 = 9.4, etc.)
View 9 Replies
ADVERTISEMENT
Feb 15, 2009
I have a table showing the quantity of products that were sold to stores within a particular week. The following data are shown in each column: Distribution centre (DC) ID, Item ID, Item description, Store ID, Day of delivery, Invoice Number, Quantity delivered - see example attached.
I want to sum up the quantities of identical items that were delivered to the same store from the same DC. In other words, the following conditions have to be met: (a) Distribution centre ID has to be identical, (b) Item ID has to be identical, (c) Store ID has to be identical. To give a specific example as per the attached file, instead of having 2 rows for 2 deliveries of 5 + 3 boxes of chocolate to store# 1, I want to have just one row showing that store# 1 had 8 boxes of chocolate delivered, a second row for that store would show that store #1 had 10 boxes of tea delivered to it. I would like the summed up quantity data point along with the Distribution centre ID, Item ID, Description, Store ID, Day of delivery, Invoice Number to be in a new tab, i.e. I want to reduce the table size and do not want the original rows to be in the new table.
Since my table goes all the way down to the last row 65536, I would need the formula/macro to be able to recognise identical DCs/items/stores and then sum up the quantities (there are about 1500 different item IDs). Can you think of a solution??
View 2 Replies
View Related
Oct 23, 2007
I have a column (D) where I put percentages complete of a task. When that reaches 100% I want to put the date in column (C).
I found many samples online but nothing I can modify to do this.. This one is pretty straight forward but I'm not sure how to have it look for the value and not just empty or not. I would be greatful for any help you can offer.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target(1)) Then Exit Sub
If IsEmpty(Target.Offset(0, -1)) Then
Target.Offset(0, -1) = Date
Target.offset(0, -1).numberformat = "yyyy-mm-dd"
End If
End Sub
View 6 Replies
View Related
Feb 26, 2008
Please find the attached sheet.
I just want to know whether is it possible to condional format multiple cells based on multiple cell values.
Eg: In the attached sheet,when a driver is standby and the vehicle no column should be empty,if accidently a data entry is done in vehicle no column it should highlight the whole area (ie,from A3 to E3).
View 8 Replies
View Related
Jan 27, 2012
apply conditional formats to a cell, based on the entries in another, different cell?
View 2 Replies
View Related
May 7, 2014
What I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.
Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).
the function I'm working with right now is
=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)
View 13 Replies
View Related
May 29, 2014
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
View 9 Replies
View Related
Mar 20, 2013
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
View 3 Replies
View Related
Apr 15, 2014
Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:
(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.
However I can't figure out how to manipulate it into a percentile keeping the condition of
IF(DATA!$B$15:$DZ$15=1000)
View 2 Replies
View Related
Feb 9, 2009
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies
View Related
Mar 13, 2008
I have a list containing blocks of stock price values with each block representing a series of values at a given time e.g
1400 156
1400 99
1400 74
1400 86
1400 256
The number of entries may vary for each block but is never more than 60. the next block may be e.g.
14:05 15
14:05 42
etc
and down a very long list of around 65000 entries. I'm looking for a routine that will add up all the values in each block and calculate the total for that time block. I then need to fill in an adjacent column with the perecentage that each value is of the total. A completed block may look like;..............
View 5 Replies
View Related
Jan 21, 2009
I have a spreadsheet that i update weekly. i need is a formula that will just sum the last four weeks. Example column A rows 1-52 will be updated each week in row 53 i just want the last four weeks to be summed.
View 5 Replies
View Related
Jul 29, 2006
I would like help with a worksheet.
I have a column of Data which is a "material description".
In an adjacent column I have the square meterage of the material.
I would like to total all the square meterage of each material and have it shown in another column.
How can I do this?
I have searched far and wide for an example however I am probably using the wrong terms in my search.
Attached is a sample workbook.
View 9 Replies
View Related
Aug 11, 2014
To give this a bit of context, I am looking at running speed of an athlete. I am trying to determine the NUMBER of sprints in a session (aka >15 km/hr for example). To do this, I need to calculate the number of times a speed vs time graph surpasses 15km/h.
For example, using this data:
TimeVelocity
0.212
0.413
0.614
0.815
116
1.217
1.416
1.615
1.814
214
2.212
2.410
2.613
2.814
315
3.216
3.417
3.618
3.817
415
4.214
4.413
4.612
4.812
511
I would calculate two sprints (one beginning at time point 0.8, one beginning at time point 3). I can't work out how to calculate these sprints independently, rather than counting the total number of entries above 15km/hr (11 in this example).
View 3 Replies
View Related
May 16, 2009
I have a row of golf scores "I3:X3" they either have a number value or "DNP".
I want to SUM only the last five number values and exclude the rest. The list will get a new entry every week. ie I want to SUM the last 5 most recent scores. There may be 15 or 16 scores or only 3, but I want the last 5 (or less) most recent.
View 6 Replies
View Related
Jun 12, 2009
If i have the following chart, what formula do i need to add all the entries EXCEPT and entry OS. i have the formula to add the entries but i need to exclude any "OS" entries. Like the following, but i want it to total 6. (minus the 2 OC entries)
=COUNTA(D6:D15)
TS
TS
VR
IS
IS
OS
VR
OS
8 (total)
View 7 Replies
View Related
Jun 10, 2009
Hello all, I was wondering if anyone would be able to help with a macro or even formula that would remove the entries in Col A that appear in Col C. Attached is an example with dummy data, Col D is how the list should be. If a number appears in A but not C then all instances of it should be kept, conversely if it is in C but not A then it does not need to be kept. Most importantly I need to be able to delete those that appear in both by the same number of times they appear in C.
Hope this all makes sense. Please feel free to ask if it is not clear.
View 14 Replies
View Related
Oct 28, 2009
I m writing the numbers 1 to 100 in rows of 10. I now need to change the code to offset this, starting at any cell i choose.
if i was to type e.g D6 into cell A1, the numbers would start at D6.
View 3 Replies
View Related
Nov 23, 2006
If I have a multiple entry of a product with the same end date against a customer number how could I flag this as something I need to look at?
Eg
Cust number Product Number Expired Date
1000018000 31/12/2006
1000018000 31/12/2006
10000180002811/2006
1000018001 31/12/2006
1001020010 31/12/2006
1021518000 31/12/2006
So in this case the first two would be flag with say “yes” and the others “no” as the customer number, product number and expire date don’t match.
View 9 Replies
View Related
Aug 26, 2008
I have been searching a bit in a lot of the already posted threads but without any luck on my problem.
Normaly in excel you always want to count how many entries for a certain criteria.
Now I just want to know how many different entries for a certain criteria.
Example:
in column A1:A10 I have the value "S" -> group
in column B1:B4 I have the value "40" -> material number
in column B5:B8 I have the value "60" -> material number
in column B9:B10 I have the value "70" -> material number
....
....
etc.
So for the group "S" it is listet with total 3 different "material number" (40,60,70)
So my result should be = 3
how should the formula be on counting for the group "S" how many different values are in a defined area (e.g. B1:B10)
I have tried with sumproduct,
View 9 Replies
View Related
Oct 20, 2008
I need to calculate average for last 12 valid entries in a column. I say valid because if there are blanks or zeros in the cell they need to be discounted.
The solution needs to work on percentages as well.
To explain further
Col A Col B
Jan 2007 1
Feb 2007 5
Mar 2007 5
Apr 2007 0
May 2007
Jun 2007 20
Jul 2007 0
Aug 2007
Sep 2007 10
Oct 2007 0
Nov 2007 10
Dec 2007 18
Jan 2008 8
Feb 2008 6
Mar 2008 0
Apr 2008
May 2008 10
Jun 2008 1
The requirement would be to calculate average from Jun 2008 backwards for 12 months where there aren't zero or blank entries.
View 9 Replies
View Related
Oct 30, 2008
Is it possible to have a formula that sums only the first 13 entries in the range f8:f1006, where the value 13 can change as it is a result of another formula linked in cell f6
View 9 Replies
View Related
Feb 25, 2009
I try to sum up values a user enters into 3 different textboxes using the following
cells(1,1) = userform.textbox1.value + userform.textbox2.value + userform.textbox3.value
Let's imagine the user enters "10" into each of the three textboxes, cell a1 should contain 30, however, what i get is 101010.
View 9 Replies
View Related
Sep 19, 2009
I have data on sheet A columns (A:DR) over 400 rows, adding more all the time.
On sheet 2 i would like to display only the last 8 entries (rows) from sheet 1 columns A,B,DO,DQ
So i will end up with 8 rows of data on sheet 2, and 4 columns.
View 9 Replies
View Related
Sep 24, 2009
I have been trying a number of different functions!
I have the following countif function that is searching a worksheet (Cases Closed) for the name John in Column O and excluding Solutions in column x. The problem I have is there are duplicates cases in Column C that are being counted two and three times.
Is there anyway to have the following function exclude duplicates records in Column C? Just count unique records in Column C?
=(COUNTIF('Cases Closed'!O:O,"John"))-(COUNTIFS('Cases Closed'!O:O, "John", 'Cases Closed'!X:X, "*Solution*"))
View 9 Replies
View Related
Nov 30, 2009
I want to put a formula in a sheet.
Suppose In issue column there are few nos but until those nos will not be posted in Return column, those will not be posted again in another day's issue column, similarly until those will not be returned, those nos will not be posted in issue column. But for the first two days it will not work ad those are first time entries.
But this will be continued working on the next sheet for the net month.
28/11/09 29/11/09 30/11/09 Issue Nos. Return Nos. Issue Nos. Return Nos. Issue Nos. Return Nos. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
View 9 Replies
View Related
Dec 16, 2009
The following code works fine if the data is grouped (not necessarily sorted) by column B (but not necessarily by column A), this code will delete the duplicates rows, but retaining the latest entry (by column A) of each name in column B.
Sub DeleteTheOldies()
Dim RowNdx As Long
For RowNdx = Range("B2").End(xlDown).Row To 2 Step -1
If Cells(RowNdx, "B").Value = Cells(RowNdx - 1, "B").Value Then
If Cells(RowNdx, "A").Value
View 9 Replies
View Related
Nov 16, 2006
I am looking for a VBA event(?) code that would erase another cell if I enter a value in another cell, because the criteria is that only one of the cells are to be allowed a value per row.
ex: cells A1, B1, C1
if I enter 1 in cell A1 and then enter 1 in cell C1, I want cell A1 to erase and cell C1 would have the value 1. I am looking to create these sets for 200 rows.
View 3 Replies
View Related
Apr 23, 2007
I have attached a sample workbook to try and explain what I'm after.
Basically I want to sum the amount of occasions a person has had checks and each occasion could have 1,2 or 3 individual checks.
View 9 Replies
View Related
Aug 6, 2007
I am trying to construct formulas that dynamically extract correct values. I need to construct two formula one in Column I and Column J. Column L and M have solutions that I should have. The formulas need to look for data in columns and extract them. For example, if an entry in E3 then for This week it will extract it and for last week it would extract entry in D3.
View 9 Replies
View Related
