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
-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 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.
I'm trying to build a formula to form a price-list. I have some basic prices from a supplier and want to build my prices with a simple rule: the higher the basic price is (column A), the lower my profit margin (in %) should be (column B). Example:
Basic value is $50, my price is $75 (50% margin) Basic value is $100, my price is $130 (30% margin) Basic value is $150, my price is $172,5 (15% margin) And so on...
I forgot most of what I've learned on Excel at my university (long time ago...), so I tried to do it by using simple thresholds, with "if" function:
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 $.
How to increase & decrease numbers evenly for a spreadsheet.
I have a number in cell F16 that can be changed and for this example say it has a value of 900. I have a number in cell F17 that is always more than cell F16, for this example lets say it is 1000.
What I'm trying to do is create 10 numbers evenly increasing away from 900 (F16) in value, & 10 numbers evenly decreasing in value away from 900 (Cell F16) but here's the catch.
What makes this tricky is that the numbers that are increasing in value away from 900 (F16) can't be bigger than the value of F17 , IE in this example the number 1000
So the formulas must evenly distribute 10 numbers BETWEEN 900 & 1000. This must have the same even distribution for the increasing numbers & decreasing numbers.
I'll attach a sheet also. So, if you say 900 is a starting point & 1000 is the ending point, what formulas would you need in each cell to evenly increase to 1000?
Also,, going the other way, decreasing, away from 900 at the same rate as the increase?
Really stuck n this 1. Spreadsheet here: [URL] ...... Screenshot here: [URL] ......
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 have been working on 2 sheets receiving and selling item and QTY, or in other words i have a Stock IN sheet and a Stock Out sheet
is there some sort of a formula which links 2 cells, columns, together in a way that, if one cell's number increased the other would decrease automatically ?
say iv set 50 in cell a1, i want to set 10 elsewhere and the 50 in cell a1 will automatically decrease from to 40
The formula I am looking for would tell me what annual growth rate % I would need to achieve to make any investment reach a set target, for instance, what % of fixed annual growth would I need to make 200K grow to 750k in say 10 yrs or any time scale. I was given the formula below but Excel tells me it's wrong, I have tried putting 10 before ^ and the 10 after but to no avail, could some kind soul please put me straight.
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.
From a chart in Excel I need to automatically calculate what the annual percentage growth rate is of a trend line. How to automate this in Excel? I've attached a sample so you can see what I'm trying to accomplish.
I'm dealing with the following problem. Say I have a set af values:
A B 1 567 2 546 3 567 4 554 5 572 6 543
I can easily determine the average of the values in column B (=AVERAGE(B1:B6)) But I also want to know if the average is increasing or decreasing without making a chart and trendline. Concrete: I want the cell with average (say B7) to turn green when the trendline has a positive growth and red when negative.
Is there a formula or maybe some VB code that can be used to decrease a cell value if another cell's value is changed. e.g. I have a userform that has a Textbox that will put a 6 digit number in cell J4 & a Combobox that enters a number in cell E4. If the value of J4 is changed from the Textbox, I would like to have cell E4's value decrease by 1. If J4 doesn't change, cell E4 remains unchanged.
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 single simple question, so let's get straight to the point: I have an A column with these values:
I:::I::::::A::::::I::::::B:::::.... I==================== .... I 1 I____40____I_________ .... I 2 I____50____I_________..... I 3 I____30____I_________.....
And I need each of these values increased/decreased by a constant number.
So far, I only found formulas where all cells are, say, added into a single result, as you can see here:.....
I need to be able to select a certain number (from, say, 1-20) from a drop down menu, which will decide a number of rows (filled with data) on the next sheet. As I prefer not to give anyone the sheet I'm actually working on I made a quick look-a-like which might explain this better.
A and B are different scenarios, with different values in the fake drop-down menu I made. Just to pass on the understanding of what I want the values to do. If putting a drop-down menu there is troublesome, then I can live without them. But entering a certain value in the field and getting that number of rows for a certain product is what I need. I tried searching for a function/macro to use for this, but I dont know what exactly to look for. Name, etc
I need a formula that can work out a percentage increase or decrease from cell J2 to L2.
J2 is the current data with L2 being the previous data - so the formula would compare J2 with L2 and tell me if there is an increase or decrease shown as a percentage.
The figures fluctuate from month to month so they could be either an increase or decrease. There are over 300 entries so I can't go through each one adjusting the formula accordingly - this is why I need the formula to do the working out if it is up or down.
I have a simple spreadsheet. A value is inserted in cell B4.
I want to increase that value or decrease it by adding a button in the next cells, C4 or D4, to increase and decrease that value instead of manually typing in the new value.
Below i have example table in which Cells B2:G4 have draw numbers. In Cells I2:X4 I have numbers in 3 groups, Group start with small number and finish with highest number.
For example in row 2 first groups is 5 to 36, second group is 10 to 30 and third group is 4 to 41 and continue same for rest rows ....
in speeding up the below code as it does not seem to matter whether I use For, If loop etc - it runs too slowly. Basically we have one sheet which is input in a different timezone that needs converting to GMT... since daylight saving I wrote some code to cover the +/- 1 hour difference (this seems to work quite well) yet when it comes to converting the actual cells it runs incredibly slowly. I appreciate any help but its only the code after "Sheets("data").Unprotect" that i think needs changing. The time stamp is in column 7 - I hope this makes enough sense - which then gets converted by either 4.5 or 5.5 hours dependent on british summer time.
Sub TimeConvert()
With Application .Calculation = xlCalculationAutomatic . ScreenUpdating = False .DisplayAlerts = False .EnableEvents = False End With
Dim FirstDate As Date Dim Position, DayIndex, lgrow, lstrow As Long, TargetMonth As Long, Stweekday, Enweekday, Tdate As Date, TargetYear As Long, cell As Range Dim bst As Boolean
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 receive a large file every month with 30K+ rows of data that need to be manipulated manually before I can send it off. Within the data are "chunks" of data that are anywhere from 1 to 90 rows each. Each chunk needs to total 100 or less, and while they do for the most part, some end up over 100 due to rounding (usually 100.02 or so). The rows above and below each chunk are blank, and I currently have a calc to add each chunk, and flag it if it's over 100. Then, I have to go through each of the flagged chunks and manually decrease one of the lines to make the total 100.
Is there any way to automate any or all of this procedure?
I have attached a sample file with two chunks of data. The actual files contains 14 more columns to the left that aren't shown.
Is it possible through a macro that the cells where numbers is in percentage format should not show any decimal points (is should not roundoff) just decimal points should not visible. I am aware that this can be done by "decrease decimal" but data will be across thousand of rows manually it will be very time consuming.
I have totals in d18 and e18 and need to find the percentage increase or decrease between the 2. E18 is last year's value and D18 is this year's value. Which formula will give me what I am looking for:
