I need formula for dynamic ytd calculation for the scenario mentioned below.
YTD YTD YTD
Month Apr-13 Apr-13 Apr-13 May-13 May-13 May-13 June-13 June-13 June-13 May-13 May-13 May-13
Product type x y z x y z x y z x y z
sales executive
a 5 10 15 20 25 30 35 40 45 ? ? ?
b 4 8 12 5 7 11 12 69 79 ? ? ?
if a2 = "multiplications and divisons of number 100", then .......
a2=200
multiplication of 100 =200,300,400,500,.... divisions of 100 = 50,25,12.5,........ ***************************************************** how can i say if all cells from a2:a8 = same number then ....
I am comfortable with getting a macro to copy & paste where I need it to, however how to get the macro to recognise when a certain value has stopped.
For example in Column B on "DataDrop" are all the divisions that exist in my company i.e East Anglia, Essex, London etc. There is an empty row between each division.
When setting up a Direct Debit There are 3 options, the 7th, 17th and 27th. I've put these into a drop down list. I need this list to be slightly dynamic. I must allow 10 working days from the date of the arrangment before I can apply the DD.
To explain further. If someone were to want to set up a DD and spoke to me today. If they then requested the DD be taken on the 7th, I wouldn't be able to set it up until the 7th of December as 10 working days are required. I'd like the list to reflect this. I can do it in OO with a simple if / switch but excel is a strange creature and I'm not sure how to approach the problem.
From that I think I also need to calculate the number of weeks remaining in the current financial year. I think I should be able to do this by using the Today() function and some maths.
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e. First dynamic range: called "Milestones" at A11 Second dynamic range: called "Activities" at A25 & make a Validation list that will list content of both
I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.
I have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-
1) Offset and match, cavet being the data must be sorted a-z 2) Have lists for each potential selection
Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?
I have a dynamic table which is linked to a couple of charts. The table must remain dynamic.
I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".
I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.
Is there a way to make dynamic drop downs within a dynamic table?
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.
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.
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.
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.
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.
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.
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.
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?
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.
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
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
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.
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?
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
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?
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.
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.
