-return red/yellow/green when the value is below/above or within range of targets but dependent on an increasing or decreasing trend.
If the trend is decreasing and the value is above the target is should return red.
If the trend is increasing and the value is above the target is should return green.
For all there is a Threshold.
For example:
Value Target Trend TrafficLight
(A) 79% 90% Increasing RED
(B) 69% 70%-90% Withing Range Yellow
(C) 85% 90% Decreasing Green
Thresholds:
for example (A) - Red: <80%, Yellow: 80%-90%, Green: >90%
for example (B) - Red: 60-90%, Yellow: 50-60%;90-95%, Green: <50%;>90%
for example (C) - Red: >80%, Yellow: 80-90%, Green: <90%
I want to turn a certain cell(sheet1!K2) either green, orange or red depending on certain conditions.There are 3 conditions.If 3 of these conditions are met I want the cell to go red.If two of these are met I want it to also go red.If one of these conditions are met I want it to go orange and if 0 are met I want it to go green.
The conditions are : 1) if a number > 0 is entered into B11:H11 2) if a number > 0 is entered into D11:H11 3) if Ex is entered into cells sheet2!D2:D16
Amount of money: 1000 (changeable) Beginning # of units: 0,1 (changeable) Delta: 200 (changeable) Rate of decrease: 50% (changeable)
0,1 unit should be added for every 200$ increase (1000+200=1200; 1200+200=1400; 1400+500=1900; 1900+256=2156 etc) 0,1 unit should be taken away after decreasing by100$ (because the rate of decrease = 50% (changeable))
After increasing of 1000 + 200, quantity of units should also increase for 0,1, so it should be 0,2 (1200), 0,3(1400), 0,4(1600)….. Amount of money can increase not only by 200, but for any sum. If that sum is (for example) 500, we should increase by 0,2 etc.
But when this some decreasing we are using rate of decrease (for example 50%, (changeable): 0,4 units – 1600 0,3 units – 1500 0,2 units – 1400 0,1 unit – 1300
I have a list of jobs being displayed using the following code. All sheet names that start with AJ, CJ and PJ within the workbook are how the list is created.
Sub ListSheets() Dim sht As Worksheet Dim lRow As Long Dim rCell As Range
With Sheet1
Set rCell = .Cells(2, 12) End With
For Each sht In ActiveWorkbook.Worksheets Select Case UCase(Left(sht.Name, 2)) Case Is = "AJ", "CJ", "PJ" lRow = lRow + 1 rCell(lRow, 1) = sht.Name
Case Else End Select Next sht
End Sub
what i want to do is create a drop list within each job sheet within the workbook that will display the names of the jobs above. Now the thing is I cannot choose the range like normal from data - validation - list as I will not know how many job names will be displayed so I dont know how many cells to include in the range.
Is it possible to have a cell increased by 1 and another decreasing by 1 in a simple cell formula.
E.g.: I have an inventory stock sheet that is very simple. I have a maximum stock column C, a stock on hand (available) column A and a stock sold column B.
Every time I sell an item and punch it into column B it should automatically decrease the amount in column A by the number that I punch into B.
I am not concerned about the maximum stock column but would like an easy formula just to work out the amount that I have available so I know when to order more stock.
So when I punch in a 5 on B2 it should decrease the value of A2 to 1. And when I purchase stock again and I punch in say a 5 on A2 it should decrease B2 to a 1.
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 used three different dates. In which the outcomes were available. The first three sheets.
Is there a possiblity to use excel to calculate a future outcome of the next month(Sheet 4). The figures are in a way linear, because the previous months are in it.
So it's possible for the future outcome to go up or stay the same. It can't be less then previous months.
See attachments.
It's about a holiday (combinations) of arrangements. So it'll be influenced by the date (because of holliday plannings), finance etc etc. But the last factors/variables we will use and test in SPSS and other programs.
The question is more: Is there a program/formula in excel which will predict the outcome in the last sheet with maybe a factor of outliners (this coincendence of a better salary in some parts/or the date that people will book a holiday). And how the formula would be formulated in the last sheet?
So that is 1 row, my problem is I have about 8 thousand of these, and what I'm looking for is trends in lapsed accounts (accounts with no spend, the "0's"). As you can see I have 6 zero's in a row, which means this customer was lapsed for 6 months.
I want to run something that will find the average of zero's in a row for all 8,000 rows. For example one customer has 6 zero's in a row the other one has 3 zero's in a row so the average would be 4.5. I only care about zero's in a row.
I am trying to show some trends, from data taken on a monthly basis. The attached spreadsheet is an example of what I have:
The first two columns show data for Month1. The next two columns show data for the last 2 months (Month1&2). I was thinking of using a line graph, and showing Month1 and Month1&2 on the X axis. At time Month1, I'd have 4 different data points, for hw, sw, fpga and chip. At time Month1&2, I'd show the cumulative data for those same 4 categories. Over a period of time, we could see whether one area or another (hw, sw) keeps getting worse. How can I do this via pivot table?
I am writing a VBA for downloading a csv file from google trend.
In the first worksheet ,enter the search keyword in the excel file and the click a button to download the csv file and place it in another worksheet in the same excel file.
I have had some experience with downloading the stock stat. csv file from yahoo but then I realize this time is really a different story.In the google trend case, I cannot find a real URL of the csv file.
I strongly believe the solution is on [URL] .....
I have tested the code in #1 .It runs but cannot return a proper csv file just like Lloyd said. I do not understand what Kyle said. What do title and id refer to ?
Besides,I do not understand and cannot run the code in #6.
What is the input and output of this code?
When I run this, I face a Run- time error '-2147024891(80070005)'.
I am trying to write a macro that will automate the process of selecting rows from a data sheet. I want the rows to be selected based on the values in two columns. I want to find and select the row at the beginning and end of positions in a column where the values are equal. For a quick example, if my data looks like this below, I'd want to select the entire row for rows 2,3, and 6... I also need the row just before the value in both columns are equal.
I have written this macro and it is not working... are there any obvious problems with this? This is new to me and I'm not exactly sure that I've used the correct commands. It also seems like LastRow is referencing 0 and not 12965 or whatever the last row of filled data is for the sheet.
Sub Deltadata() Dim RowNum As Long, LastRow As Long LastRow = Range("A" & Rows.Count).End(xlUp).Row 'Finds last filled row (should be 12965)
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.
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
Column A is the COB Date Column B through N are various stock data. I would like to calculate the trend of correlations over a 4 year period. I can't seem to figure out how to manipulate the data analysis tool pak to do this.
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'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 have to pay 190000 $ in 180 months. I need a formula to establish an incresing rate for every month. I have a simulation on a paperwork, but I can't figure out the formula. On the paperwork the first rate in the first month is 875,21 $, the second one is 876.99 $, ..., and the last one in the last month is 1258,97 $.
I'm just fiddling around with excel at the moment and have a created a cell with a value of 16. It represents the number of days an event has been running for.
I'm not sure how to make it increase by +1 every day without me having to open excel and change the value in the cell.
I have a worksheet that I need a piece of code for, Cell E1 is an amendment number that increases every time a new one is put out, so they can be tracked.
Every time the sheet is saved the value in cell E1 needs to increase by 1.
I am doing a hlookup on a range (possibly over 100 rows). My formula is working but my problem is that it takes too much time to go and manually edit the formula to change the row index number and increase it by one. For example the row index number has to change in order to pull in the right data....eg 5,6,7,8 etc up to over 100. Is there a quick way to do this or do I have to manually enter the row index numbers in over 100 rows?
I am attaching a SAMPLE of data. The actual worksheet is much more larger.
I need a series of cells to increase by varying amounts after each month, however this needs to happen automatically so if I send the spreadsheet to someone else, it will update for them automatically. I'm hoping that I can set up a function that will ONLY increase the cells after 4 weeks after they select a date from ANOTHER drop down menu (i.e they select the starting date from cell A1 - then input their starting numbers into other cells which then increase on that same date, 4 weeks later).
The cells that need to increase already have a formula in them as well; as this number is their predicted 1RM based off formulas in other cells.
My wife and I run an apartment complex with 2 employees, a manager and a maintenance man.
We use excel to produce a paystub for these employees twice a month.
There are a number of variables including incentives for renting apartments, overtime, variable hours in a pay period etc.
We have a separate spreadsheet for each employee and a separate sheet tab for each pay period named for the pay date... e.g. the first paystub had one sheet named "Jan 15"... the next pay period the paystub had 2 sheets: "Jan 31" and "Jan 15". The third paystub had 3 sheets and so on.
The paystubs have a column with the numbers for the pay period and then a column with the year to date sum (for straight pay, over time, federal withholding, social security, medicare, state withholding, etc.)
Right now I edit the formulas for all these sums for each payperiod. e.g. =sum('Feb 15':'Jan 15'!B35) for the third pay period is editted to =sum('Feb 28':'Jan 15'!B35) for the fourth pay period.
I would like to be able to just copy the prior spreadsheet and not have to find and replace values in 11 spreadsheet cells for each pay period.
I have figured out how to get the name of the current spreadsheet to appear in a cell on that sheet using VBA... but not how to get that cell content to be part of the arguments for =sum(. on the formula bar...
I should know this and I'm sure its something very simple that just wont come to me. I have two cells, one has "total billable hours" in a month, the second needs to calculate how many days based on that number. So it needs to increase by 1 for every multiplier of 24 in the first cell.
I want the first 60 rows of column C to be constant meaning C1, C2, C3, C4..C59 and after 60 rows it should start again with C1, C2, C3.....C59 rather than C60, C61, C62. In other words i+1 but after 60 rows i should be reset to 1 and then again increase by 1. how can i implement these changes
