Running Tally Traffic Light System - Find Previous Values Upwards In A Column
Jul 21, 2014
I'm trying to work out to most efficient way of making a quality monitoring system which uses traffic lights to show problem products.
The traffic light system need to obey the following "rules".
Each product is tested once daily When a product fails once it moves to Amber, When a product Fails twice in a row it moves to RedOne pass when a product is in Amber Status, will reset it to green.If a Product is in Red status, it much have 3 consecutive passes to reset to Green.
I have tried to use array formulae to look up a column for previous values in the past for this sort of thing, but with 100000+ rows it runs far too slowly.
Banana Problem?
Product
Result
Traffic Light (0=green, 1=amber, 2=red)
I have a scenario where I am trying to work out on a spreadsheet how many failures I have had according to a traffic light system. If I have 5 or less failures it is considered green, between 6-10 amber and 11 or more red. How can I create a formula under column A that captures which status it is on?
I am wanting to have conditional formats so the cell fill in a range on sheet 1 will change to:
- red if the value of a cell on a seperate sheet (date) is greater than 18 months old - yellow if the value of a cell on a seperate sheet is 'In progress' - green if the value of a cell on a seperate sheet (date) is less than 18 months old
I am seeking to insert an image into an Excel cell by a formula or a macro.
I am currently trying to have a green traffic light appear in a cell if another particular cell has the numerical value of "1". for example, something along the lines of: =if(A1=1,"image1.jpg","").
Now, i know the above formula doesn't work but is something similar possible?
I'm trying to create a dashboard with traffic lights displaying the status of a task. My manager wants a to be able to pick a color (Red, Yellow, Green) from a drop down menu and then have that be displayed as a red, yellow or green traffic light. Using the conditional formatting option, I'm only able to use numerical values or a formula. I'm hoping there's a way to either create a drop down list with the 3 different colored traffic lights or a way to use VBA to have the conditional formatting search by text instead of numbers.
What I need to do it create a traffic light icon, just the ones in Excel based on the dates within the field. If there is a date present which is past todays date then I would like it to display the green traffic light icon, if the date present is scheduled for a future date I would like the amber traffic light to show, and finally if there is no date present at all I would like it to display a red traffic light icon.
I have a list of varying IP addresses in a column. I need to create a formula that will tell me how many times an IP address appears in the column so that I can ultimately determine the most common IP address listed.
i am currently trying to figure out excel i have to fill this worksheet out daily and then at the end of the month i have to count the total number of items. I was wondering if there was a way to auto talley on a different sheet so that everytime i entered a product name and quantity if on the other sheet it would auto add it in example:
I ate at mcdonalds (Main sheet)
On 11/22 I had 3 big macs and 4 quarter pounders, On 11/24 I had 5 Big Macs and 2 quarter pounders On 11/26 I had 12 Bigmacs and 5 quater pounders (New Sheet) i want it to look like this:
Product quantity Big macs (20) <-- this number i want to auto add from the notes made from main sheet)
So on another sheet i want to make a formula where for everytime i type in "big mac" on main sheet that on the other sheet it would auto add or keep a running tally so after i made those 3 entries on the main sheet it would show that i had a total of 20 big macs on the new sheet.
I want to search upwards in a column to find the last value not equal to a value (0,"", etc) so that I can perform a calculation using that value. Here is an example of the sheet.
A B 1 2121 2 1738 3 2159 4 2180 5 0 6 484 7 484 8 0 9 484
working my way down the column, what I want to do is take a value in column B and add it to the value in column A if the value in A is not 0. The first value in B will always equal the value in A if that value is not 0. If the search upwards shows only null fields or it hits the first row in the column without finding a value, that value in the current B cell should show the value in column A. I want the field to show null if A is 0. I want a formula that will look upwards in the column to find the last non-null value to add my value in column A. example : formula in B5 should show a null (""), the formula in B6 should show 84 (B4+A6), the formula in B7 should show 88 (B6+A7), formula in B8 should show null (""), etc. Column A will change values from time to time and I would like the formula to automatically be able to handle the change in values.
I have a spread sheet with bunch of rows and columns. Columns goes all the way from A ... BB and rows from 1 to 40,000
The main focus is column Y and AC
If there is a value of FALSE found in column Y then macro should go to previous row and copy the value that contains in column AC and then move down to the next row, , where the value FALSE was found and that's in column Y and paste that value in column AC the NUMERIC VALUE
I am running Excel 2007 on my Windows Vista laptop. A workbook I've developed in Excel 2007 (compatibility mode) on my Windows Vista laptop has a macro that runs as expected on my laptop but will not run on my desktop which is running Windows 2000, Excel 2000.
I've addressed this question previously under the thread "Macro Bombs Excel 2000". I've not received a response as to why it won't run under Windows 2000, Excel 2000. I don't know if it's an Operating System issue or an Excel 2000 issue.
I'm developing this workbook for my children for their personal financial management. I'd like to determine if this is an Excel 2000 issue or an Operating System issue. Most of my children run Windows XP, Office 2000. I have a dual system on my laptop (Vista and XP).
I have a legal software license available for Office 2000. My question is, can I load Excel 2000 on my Vista, Office 2007 laptop. I've looked at http://support.microsoft.com/kb/218861/ and they talk about installing oldest versions first. I have Excel 2007 already installed. I don't particularly want to uninstall it. Can I install Excel 2000 without uninstalling Excel 2007?
1. Can it be done? 2. Can it be done without MAJOR operational issues. 3. What is the process? 4. Can I load ONLY Excel 2000?
see attached file. Need to find latest non blank value - in attached file it is highlighted in yellow. From there, want to summarise 26 weeks back so, in the attached file:
Row 2 would be finding 750 and summarised back 26 weeks from 30 sep 2012 Row 3 would be finding 2250 and summarised back 26 weeks from 2 dec 2012 Row 4 would be finding 5000 and summarised back 26 weeks from 4 nov 2012
for group A: min qty: 250 min increment: 250........................
1) I need to match the product ID in workbook1 with workbook2 - see which group it belongs and put into workbook1 under column group
2) I need to count the no.of quantity and see if it meet the citeria and place Yes/No in workbook1 column under Comments.
- for example: product ID 11111 in workbook1 can be found in workbook2 group A (put group A into workbook1 under column group)
do a check: prodct ID 11111 has 500 in quantity, since its under group A it meets the min qty of 250. and followed by it meets the min increment too - 500/250 (thus put Yes into workbook1 column under Comments)
formula to take a value from one sheet and add to it in Excel 2010? I'm naming the sheets but not necessarily numerically. I have done it by manually entering the sheet name but would like it to figure out the sheet name automatically based on where the sheet is located in the workbook. That way I can copy the current sheet, rename it and still have it update properly with a running total. Here is what I have now: =D7+'011514'!E7
I have to keep a record of the running totals of school house points for each week. The problem is that teachers are very lazy and don't record data every week so I have many blank cells which my current formula can't cope with. I've tried using N/A but it doesn’t seem to work? (Have thought about threats of violence but would probably lose my job) I’m if there is no data (blank or 0) then I need it to keep the same total in the cell as the previous week and so on until new data is entered and updates the total. I have attached a simplified copy: Teachers enter points in the HP sheet, the Running Totals sheet (TAB) contains the formula.
I have a formula that I use to tally data and return the number of times a particular value is present in column C.
IF(COUNTIF($C$2:C2,C2)=1,SUMIF(C:C,C2,E:E),"")
I copy this formula down the sheet until arriving at the last input in column "C". Can someone tell me how to write this in code. All I would like visible in Column "F" is the output not the formula.
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
I have between 1 & 10 values on column A, which are distributed throughout the column. They are inconveniently at the bottom of the set of data which they are related to.
Solution I'm hoping for: -Starting from last row that contains a value- -Copy that cell upwards until it runs into a cell that contains a value- -Then stop copying that cell, pick up the new value, and then copy the new cell value upwards- -Stop at A2.
I have a workbook with 20 sheets in English Language, but I must to translate in Russian. Translating has done by human translator, so translation is good. I must change phrases and words in english to russian. I can use find/replace function but doing by hand it's very tie consuming. I need VBA or macro who takes values in column A, finds it in entire worksheet and changes it to matching value in column B. Is it possible and how I can do it?
s/s is 325501 rows deep. Column C contains names. Column J contains scores. I need column N to give me the highest score a name has previously achieved. (please see small attachment for illustration). If i can get a formula then I can fill this down.
I have a row of dates with a variable number of nonblank cells between them. e.g.:
A1 1/9/12 B1 6/9/12 C1 D1 8/9/12 E1 F1 G1 12/9/12
I want to calculate the NETWORKDAYS between dates, but where there is a blank cell, I want to be able to use the date in the previous nonblank cell. For example, NETWORKDAYS(B1,D1), if cell C1 is blank or NETWORKDAYS(D1,G1), if cell F1 is blank.
how to get the value of the previous nonblank cell and nest it inside the NETWORKDAYS formula?
This code (partial) was written to interrogate a database. Cell "A2" of another tab contains a Part Number to be sought.
Target = Range("A2") Sheets("Kanban Data").Select Dim Nrow As Range Dim N As Integer On Error Resume Next With Sheets("Kanban Data") Set Nrow = .Columns(2).Find(What:=Target, After:=.Cells(3, 2), LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False)
If the Find expression (as pulled down from the Edit menu) is empty, then inserting a Part Number in A2 and executing the macro finds the correct record and returns the its correct row number (N, as defined afterwards in the code). Then, changing the Part Number in cell A2 and executing a new search returns the SAME row as the previous search, despite Target being equal to the new sought after value.
I'm trying to use the find() function (or another if it exists) to find a previous non-empty cell.
Basically, the active cell will be ain a column with mostly empty cells, and I need it to move up the column looking for the previous cell that is not blank. I could do it with a loop, but I'd rather not if possible.
I've got two question on how to 'find' some values in a specific data file, namely:
- the '52-Week High' of the share price (highest value of the share in that specific year) - the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..)
Simplified, my data looks as follows:
-In column 'A' I've a created a line of unique edentifiers (company CUSIP + year -> combined column D and E), in my set this column consists of 405 companies over 6 years of daily data -> aprox 460000 rows -In column 'B' the date is shown -> its daily trading data -In column 'C' the daily share price is shown -In column 'D' the company's CUSIP (edentifier for specific company) -In column 'E' the year
Sheet 1: 1.JPG
My final data set has to look something like this: -In column 'A' the unique edentifiers (company CUSIP + year) -In column 'B' the specific year -In column 'C' the '52-Week High' of the share price (highest value of the share in that specific year) -> red numbers from sheet 1 -In column 'D' the value of the share price of the last trading day of that specific year (this isn't always 12/31/20..) -> green numbers from sheet 1 -> off course all the empty cells in column C and D normally contain the missing values
Sheet 2: 2.JPG
The big question is how do I manage to identify the red and green colored values (and link them to 'sheet 2')?
Regarding to the '52-Week High' (red colored in the sheets) I've already tried with the following functions: - VLOOKUP: When I use this one I only get the most upper value, for example in the case of '8668102001' this would become '12' - Combined INDEX/MATCH: The same problem as with VLOOKUP - IF-function:In this case I get the max value of the entire 'C'-column, in the example of sheet 1: this would be '115'
Regarding the value of the share price of the last trading day of a specific year, I couldn't manage to come up with one.
I'm trying to do is scan down Column A (1:100 would be more than enough) and looking for 3 values, "INCOMING:", "OUTGOING:" and "WEBALERT". These will always be under Column A but the lines of text between them are different everytime. I'd like to capture the Row values of each of these values and store it in a variable which I will use in a range later. I think the way is to use a loop, but for the life of me I can't figure out how to get it to scan.
