How To Siphon Value Away From Iterative Calculation
Jan 17, 2014
three people with different amounts of money. Each 'turn', they take 10% of what they have and pass the rest to the players either side of them. The amount they pass to each varies, but that isn't important.
The problem is that I need some way to record the 10% (or 20%, or 30%, or whatever) that they take off on each iteration. Currently the iteration will simply reset the value in the field. So if they had $100 in iteration one, it reads $10. But another iteration (with the amount of money in the loop decreasing), it changes to a lower value and the '$10' is lost. Is there any way you can have a formula that takes a value from the iteration each loop and isn't in turn changed each time? I basically just want to have 'value loop 1'+'value loop 2'+'value loop 3', etc. As it stands I lose the information.
View 1 Replies
ADVERTISEMENT
Jan 26, 2008
I need to perform an iterative calculation that will continue until the value is below a certain number. For example. I need to perform the following calculation.
A=M*(X-Y)
if A is less than B (some pre defined value) then the calculation is ok. If A is greater than B then I would like to reduce the value of X by 1 until A becomes less than B. B, M, X, and Y are all values entered into cells in the spread sheet. At the end of this I would like to display the value of A and the new value for X in a cells on the spread sheet.
View 5 Replies
View Related
Apr 9, 2014
I have a created a Data Chart Below. A - C are the columns and 1-7 are the rows. I have hard-coded the equation in cell B5 that I am using.
What I want to do is input an number into B3 that automatically makes Cell B5 equal to Cell C1. Is there a process in excel you can use to do this. Or do you just have to use trial and error?
A B C
1Loan Amount$10,500,000 0.0730041581143804
2Term 10 years
3Rate
4Amortization30 years
5Constant (K) =PMT(B3/12,(B4*12),-1000)*0.012
6Annual Payment$766,544
7Monthly Payment$63,879
Example is attached.
View 1 Replies
View Related
Oct 14, 2011
I am currently using MS Office Excel 2007 and my limited VBA knowledge has put me at a stop of a project that I have been working on. I am trying to create an excel template that will open every word document in a specific folder and pull data located in the title of the document.
For example, I want the spreadsheet to open every document and pull info from the title that would look similar to this:
"line of business";"policy #";"dollar amount";"name";"line of business" and etc.
The semicolons in the title would partition the data across a few cells.
Below is the coding that I currently have, This is my timestamp. Column A adds a timestamp whenever data is entered into the corresponding cell in Column B. Because of this, I need data to be pulled from Word documents and inserted into Column B.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
For Each Rng In Target
If Not Rng.Value = vbNullString Then
Select Case Rng.Column
[code]....
Below is some coding, but not very specified to my specific needs.
Sub SplitValue(Rng As Range)
Dim avarSplit As Variant
avarSplit = Split(Rng.Value, ";")
Range(Rng, Rng.Offset(, 4)).Value = avarSplit
If Left(Rng.Value, 2) = "RE" Or Left(Rng.Value, 2) = "FW" Then
[code]....
Also not sure if the word document is to be pulling information from the title, if I would need to negate ".doc" from data being imported.
View 2 Replies
View Related
Nov 19, 2008
make a calculation(addition) and use the answer to multiply against another addition calculation....
The sum of (Monday!A1:A4) multiplied by the sum of (Monday!B1:B4) plus (Tuesday!A1:A4) multiplied by the sum of (Tuesday!B1:B4) and so on.
View 2 Replies
View Related
Oct 12, 2007
I have a series of monthly revenues and want to calculate each month a commission % - but only want this commission calculated up to a defined limit from the previous months and current month and then to stop when the limit is reached.
View 13 Replies
View Related
Jan 30, 2014
A
B
C
D
F
1
Received
Overdue
Completed
Untouched
[code]....
Overdue is received in previous slot and still under dependency.
Formula used for untouched - B2+C2-D2
SLA Target Min - 98% and Max 99%
Algorithm - (Total Number of Allocations completed within 5 business days Plus Type A Exclusions) / Total Number of Allocations received minus Type B Exclusions) * 100
I need a individual SLA achieved % which includes untouched too.
View 1 Replies
View Related
Nov 10, 2008
Hoping someone might be able to kindly help me out with this one. It's for a spreadsheet of call charges (credit crunch thing).
On the sheet, I have the call charge up to an hour ($H$5). Over an hour, it's charged per minute at the rate in $H$6.
In cell E27 I can enter the number of minutes for the call.
So basically, if E27 is up to a value of 60 then the cost is just H5, if the value in E27 is 61 or more then it's H5 + (E27-60)*H6.
I'm thinking it's an 'IF' but keep making a mess of it...
View 9 Replies
View Related
Jan 15, 2009
How do I calculate YTD from 1 MTD cell? The YTD cell needs to keep a running total of the MTD cell. If the current YTD cell has the number 11 in it and I typed 2 in the MTD cell the YTD cell needs to increase by 2.
View 9 Replies
View Related
Dec 11, 2013
What I need is to add a field that gives a gross margin percentage for a project.
To begin, I simplified the following data that the Pivot Table is using. Project (10 different projects), Account Number, Account Category (Revenue, and Cost), Debit Amount, Credit Amount, and a manually inserted column named Total (CR Amount - DR Amount). See 1st image. Pivot1.jpg
I have designed the Pivot table so it lays out like this. Pivot2.png Pivot3 (2).png
I have been trying to enter a calculated field that would automatically provide the Gross Margin Percentage. In the 2nd image, the Grand Total is the gross margin, but I am unable to use that as a selection when creating a calculated field.
View 3 Replies
View Related
May 21, 2014
I am trying to figure out the calculation steps that Goal Seek is doing behind the scenes so I can create the formula rather than clicking on goal seek whenever I need to make a change. I need to maintain certain weeks of supply every week depending on the sales. Let's say i want to maintain 5 weeks of supply every week, I would like to insert a column for "Suggested To Order' column that would change if weeks of supply goes below 5. In this scenario I would do a Goal Seek, but I want to automate the calculation so I don't have to do this step every week.
View 2 Replies
View Related
Nov 5, 2008
i need to total a range of cells, however, these contain time values; hh:mm:ss. it shows me the total when all cells are highlighted. but =sum() doesn't work.
View 2 Replies
View Related
Mar 4, 2009
I have a spreadsheet with several formulas where I have to go into each one of them to activate the calculation. I use F2 and enter. Automatic calculation is on. Do any of you know how this can be done automatically. A VBA-code will fit the purpose.
View 3 Replies
View Related
Aug 26, 2009
My understanding of Excel is that the calculations are performed in Column A first and then down through the rows in Column A. After all the calculations are finished in A, the calculation moves over to Column B and down the rows in B. Is this true?
I know that Excel is a little more complicated than that especially when it creates a queue for calculations, etc. However, assuming there's no calculation list/queue, would the above be correct?
View 4 Replies
View Related
Oct 23, 2009
I'm creating a spreadsheet to calculate materials with the following columns Cost/10% of Cost/Customer Cost/Qty/Total cost.
I understand that whilst showing rounded to 2 decimal places excel stores more than this in the cell. which then throws out the Total cost by a few pence.
My research leads me to believe I need to use the ROUND function but I'm unsure which cell to use it or how.
View 2 Replies
View Related
Oct 26, 2009
I have spreadsheet that tracks the the rain total for several days. I would like to be able to break it down to the days of weeks that has rain and how much without manually imputing the data.
Days | #of times | total amount
Mon
Tue
Wed
Thu
Fri
Sat
Sun
View 5 Replies
View Related
Nov 9, 2009
I have a data in excel , sample sheet attached.
and i have another place for compile where all the data is summarized
What i want is
If the agent name is example 1 and his mistake is present in raw data and it matches the agent id , date and financial then i want excel to calculate how many " financial " error agent made on that particular date only so that i can assign to another agents too , to get exact data no matter in whichever series that data is inserted in excel.
if i use countif and if all the condition are met it shows me all financial mistakes count and if it shows false it turns to zero . if agent make " financial mistake " on 1st nov and he made another non financial mistake then as it should show only the count of that particular agent " financial mistake " on that date only from the given RANGE DATA
View 11 Replies
View Related
Feb 12, 2014
I use VBA with the macro recorder. Now I'd like to programming a code to calculate the average of three cells.
I've done a lot of measurements about 180 but it can be also more. For one test object I've always done three measurements to get then an average.
My values are beginning in cell C2:CXXX, D2:Dxxx and E2:Exxx. For example I'd like to get the average from C2:C4, D2:D4 and E2:E4. The result of C2:C4 I'd like to have in cell H2, the result of D2:D4 in cell I2 and the result of E2:E4 in cell J2. Then the next calculation is C5:C7, D5:D7, E5:E7 and the results in H3, I3 and J3. And so on.
I've attached my excel file, maybe I didn't explained it well. The VBA-Code is for sheet Export.
CalculationE.xlsx
View 5 Replies
View Related
Oct 25, 2009
Is there an in-built function within Excel that will help me ascertain what year is next year, and what year is the year before current? I am using =YEAR(TODAY()) to ascertain what year we are currently in, but cannot figure out how to go one backwards and 1 forwards?
View 2 Replies
View Related
Dec 5, 2009
I got a work sheet with 672 columns of information that im trying to cross compare against. I wanna compare each column against every other column in that row. I have 200 rows of data. That means each i need excel to do 226,128 comparion calculations each row. So that means in the entire work sheet its gotta do 91 million comparisons. Im on a dual core 1.8ghz core 2 duo cpu and 2 gigs of ram on xp pro with excel 2007. I even bumped up my virtual memory by 3 times the size it was yet still its taking forever.
Its taking over 3 hours to do this whole page of calculations. So i opened up visual c++ and quickly programmed in the same code with some generic values and within 3 seconds it computed it all. My guess is that the bottle neck is when excel has retrieve data from the cells because other than that i cant figure out why its so slow. Heres a section of my
View 14 Replies
View Related
Jan 26, 2006
I need to find out the amount of time between two dates for filling out
funeral benefits. The form asks how long the person has been alive in Years
Months and Days.
I would like to know if I put for instance 10/21/1955 in say A1 as the birth
date and 01/25/2006 in B1 as the date of death. So what is the formula, if
one, to calculate the time in years months and days that has passed between
the two dates?
View 11 Replies
View Related
Mar 22, 2006
I am writing a formula to calculate the last and next month end e.g. if I
enter 28-02-06, the expected result will be liked 31-01-06 and 31-03-06.
28-02-06 will be stored in cell A1, and my expected result will be displayed
in A2 & A3.
My formular is liked " =A1-31 , = A1+31. But because of "31"
has to change each month, therefore if doesn't work to my calcaulation.
Also, from the above example, the calculation for March is correct "31-03-06"
but the January is worng, it comes date on 28-01-06. But I need both result
at the end of the month.
View 12 Replies
View Related
Sep 19, 2006
I have a start date, generated onto cell B4 from a user form datepicker control. I also have a course type in cell C4 that course has a constant number of days. I would like to add the number of days of the course to the start date to give me an end date in another column.
View 9 Replies
View Related
Nov 6, 2008
I would like to perform what seems like a simple calculation: =IF C1>0, THEN A1-C1, IF C1=0, THEN DO NOTHING. How would this look as a formula?
View 4 Replies
View Related
Mar 1, 2009
I have been burning brain cells trying to figure this out.
I get these numbers from an online source and they come in like this:
A B C D E
1/1/0912:01AM02:40AM11:18AM07:55PM
The times do not come in as times...when I format the cell to time it doesnt change...that is my first problem.
What I would need to do to these times is: take B and C and find what time is in the middle of them and put that in a different column.
This mess will also need to be plotted on a chart with time by the minute for one day as the X axis. In my example I drew lines on the chart to show what I mean....the blue lines I dont want charted...I use those to find the time in the middle.
View 12 Replies
View Related
Oct 18, 2009
I have a sheet that requires me to press F9 each time I open it to re-calculate all cells. Why do I need to do this on this 1 sheet? A few months back it was fine and didn't require the extra attention.
View 2 Replies
View Related
Feb 12, 2010
I'm trying to implement the linest formula in a programming language for my coursework.
I've looked on excel help but it only explains on how the function selects data.
how to the values are calculated and the steps?
View 14 Replies
View Related
Jan 23, 2013
I have a list of prices but some cells contain multiple values where the value has changed. I need a formula that will use the last value in a cell.
Example below. As you can see the result column has now value as its using $3.41$3.117 in the calculation (I need it to use $3.117)
Price
Uplift
Calculation
Result
[Code]....
View 4 Replies
View Related
Feb 18, 2013
As a project in school, we are makiing and energy efficiency excel-sheet.
With different loads I calculate the motor load with a formula, and I get a number from 140 to 700 (could be like 680,93).
I have a table which gives me the efficiency value, depending on the load.
A - B
700 - 0,46
630 - 0,46
560 - 0,47
490 - 0,46
420 -0,45
350 - 0,43
280 - 0,40
210 - 0,37
140 - 0,32
how to get this? The calculated result is in cell D2.Tried VLOOKUP several times, but got N/A every time, i guess because the calculated value isn't exactly as in the table. Is there any way to get the closest one?
View 3 Replies
View Related
Oct 1, 2013
I have the table below and I am trying to automate the YTD calculation.
Jan
1
Feb
1
Mar
1
Qtr 1
3
[Code] ........
YTD
Need to automate.
View 4 Replies
View Related