Traffic Lights - Increasing / Decreasing Trends?

Aug 7, 2014

I need to conditionally format a cell to:

-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%

View 1 Replies


ADVERTISEMENT

Conditional Formatting Traffic Lights

Jun 11, 2008

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

View 9 Replies View Related

Increasing/decreasing; Rate Of Decrease - Spreadsheet

Aug 24, 2006

I’m trying to make this:

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

View 2 Replies View Related

Create Drop-Down List Using Increasing/Decreasing Range

Oct 3, 2006

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.

View 6 Replies View Related

Increase One Cell By Decreasing Another

Jun 12, 2014

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.

****A* B *C
1 *2 *4 *6
2 *4 *2 *6
3 *8 *6 *14
4 *0 *6 *6

View 3 Replies View Related

Traffic Light Function?

Jan 10, 2010

I want to include a simple dashboard-light, which turns green, yellow or red, dependent on a value, eg in A1. [like the one appearing after my name]

A1<100 => light turns green

A1 100-500 => light turns yellow

A1 >500 => light turns red

Does something like this exist? Preferably in VBA code?

View 8 Replies View Related

Traffic Light In Cell

Nov 22, 2006

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?

View 9 Replies View Related

Formula To Predict Trends / Outcomes?

May 2, 2014

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?

View 2 Replies View Related

Finding Trends In Multiple Rows

Nov 21, 2008

I have revenue sheet that spans 12 months kind of looks like this:

0 | 240 | 261 | 0 |317 | 0 |0 | 0 | 0 | 0 | 0 | 155

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.

View 3 Replies View Related

Using Line Graphs To Show Trends

Oct 19, 2009

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?

View 3 Replies View Related

Downloading CSV File From Google Trends

Jan 17, 2014

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)'.

View 2 Replies View Related

Macro For Picking Out Trends In Data

Dec 21, 2011

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.

row column11 column12
1 0 1
2 0 1
3 1 1
4 1 1
5 1 1
6 1 1
7 0 1
8 0 1

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)

For RowNum = 3 To LastRow Step 1

[Code] ........

View 4 Replies View Related

How To Analyze Trends From Google Spreadsheet Inputs

Jan 15, 2013

I am using google spreadsheet to gather survey data:

The spreadsheet looks like this:

[Code] ....

Is there any way to compile the information and create a line graph to display the trend of A and B over time?

View 4 Replies View Related

Show Trends To Forecast Annual Inventory

Nov 29, 2007

I have 200 items that have 22 months of usage information each and need to:

1. In one column, show trend (up or down) based on the 22 months' activity for each item.

2. In another column, show/ forecast how much inventory will be needed for the next 12 months.

View 9 Replies View Related

Traffic Light Conditional Formatting Based On Text?

Nov 4, 2013

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.

View 2 Replies View Related

Conditional Formatting To Achieve Traffic Light Results For 3 Different Values?

Jan 7, 2014

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

View 5 Replies View Related

Correlation Trends: Calculate The Trend Of Correlations Over A 4 Year Period

May 4, 2007

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.

View 3 Replies View Related

Traffic Light Icon Based On Dates Historic - Present And Future

Aug 15, 2012

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.

View 1 Replies View Related

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)

[Code].....

View 1 Replies View Related

"If" Statement: Work Out On A Spreadsheet How Many Failures I Have Had According To A Traffic Light System

Jan 7, 2009

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?

View 3 Replies View Related

Increasing Rate Of Value?

Mar 27, 2014

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 $.

View 5 Replies View Related

Increasing A Cell Value By +1 Every Day

Oct 22, 2007

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.

View 2 Replies View Related

Increasing Cells By 1/3 With Autofill

Jun 11, 2014

I'm trying to find a formula that increase a cell by 1/3 that will work with auto fill.

For Example if auto-filling a column:
3
3
3
4
4
4
5
5
5

Or if auto-filling a row:
3 3 3 4 4 4 5 5 5

View 11 Replies View Related

Increasing Numbers In Each New Cell

Nov 26, 2008

in a1 I have a number 1, a2 is a number 2. I want to keep on counting down to number 7691. Just do not know how to do this.

View 5 Replies View Related

Increasing Value Of Cell On Save

Sep 21, 2009

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.

View 2 Replies View Related

Row Index Number Increasing Automatically

Mar 29, 2007

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.

Hlookup.xls

View 8 Replies View Related

Increasing Value In Spreadsheet By Set Amount Monthly?

Jul 31, 2014

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.

I will attach a screen shot when able.

View 2 Replies View Related

How To Get 3D Sum Against Increasing Number Of Sheet Tabs

Mar 9, 2014

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...

View 2 Replies View Related

Increasing Value Of One Cell Based On Another Incremental Value

Feb 20, 2014

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.

View 3 Replies View Related

Increasing Row Number In Regular Pattern

Jun 10, 2014

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

[Code] ......

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved