I have a excel 2000 spreadsheet that has beginning and ending dates for certain services. I'm trying to tease out what our customer base has been doing over time in aggregate- i.e. how many customers subscribing to X in a given month/year.
The only way I've been able to do this in the past has been generating pivot charts of start dates (with 'start mo' and 'start year' busted out) as a positive count (+1), stop dates (with 'stop mo' and 'stop year' busted out) with a negative count (-1), then copying these tables into yet another page where I mash them together to get the 'flow' of our customer base over time and generating charts from there. Needless to say, it seems a whole lot more clunky than it should be, and I'm having a hard time searching the innarwebs for similar examples (even though I'm sure it's been done before). Example:
And I also 2 small Pictures ie picture1 and picture 2 which i insert in same excel sheet. I wanted to lookup C1 cell , if A1 is greater than B1 then Picture1 or vice versa.
I am looking for a simple loss/gain formula for my students. They are trying to find the loss/gain difference between an initial value, and the current value. They are using a monetary value, not date/time.
I run a slimming club and would like to use Excel to keep records of members weights, losses, gains etc. What I would like to do is enter their starting weight into a cell then each week when they are weighed enter a loss or gain in an adjacent cell. I would then like Excel to calculate that loss or gain as a percentage of their original weight. Is this possible?
I have a rather details and complicated procedure that in effect is designed to find a value (>=) than a text box value inputed.
There are 2 other list boxes (5 & 6) that are also designed to narrow down the search.
The values available in list box 5 are a customers name in column C. (Sample sheet highlighted value in Yellow)
The values available in list box6 are a product type. (Sample sheet highlighted in blue) When i select a value in list box5 and in listbox6, i enter a numerical value into text box111.
I then click on a command button, to populate listbox3 with all found values in column C that are greater than or equal to the value in the text box.
Now comes the part i cannot seem to be able to do.
I need to be able to click on a selected value in list box 3, and find the value that is between 2-22 rows above it in column A and place this value in text box 4. (List box 3 values are ONLY ever
I was looking for how to calculate the value from on cell F11 from this excel table but my formula didn't work so I'm looking for other solutions? ur Cell F11 indicates $104 and cell F12 $320 and F13 $658
but from my formula only works with on cell F11 I used this formula on this sheet. =PMT(C7/12,$A$11*12,,E11)*-1
Enter Retirement Parameters as IndicatedSummary of Retirement CalculationsDate plan began1/1/2008Projected retirement date1/1/2052Number of years to contribute45TRUEAccount value at retirement$542,654Annual contribution$1,300 Total contribution over life of plan$58,500Expected rate of return8%Investment Gain$484,154Type of planRoth IRAPercentage through investment89%$0.00 $1 Contribution NumberDateValue prior to contributionContributionTotal Invested Over Life of PlanInvestment GainValue at end of the year11/1/2008-$1,300 $1,300 $104$1,404$104 21/1/2009$1,300 $2,600 $320$2,920$320 $216 31/1/2010$1,300 $3,900 $536$4,436$658 41/1/2011$1,300 $5,200 $5,200$1,127 51/1/2012$1,300 $6,500 $6,50061/1/2013$1,300 $7,800 $7,80071/1/2014$1,300 $9,100 $9,100
"Run-time error '1004': "cmc4906.xls" File cannot be found. Check the spelling of the file name and verify that the file location is correct."
I am not trying to open a xls file. The path is clear and there are no file names with extensions in the path name (C:Weekly). I'm unclear of why the code thinks its looking for file cmc4906.xls and a xls file at that.
The aim is to find those combinations of variable values which generate highest total gain. I attached the spreadsheet which shows the variables (A through K) and a Gain column. I created 5 additional tabs which show all possible 2,3,4 and 5-member combinations of the variables. These tabs are like coordinates of which variable combinations should be examined. As an example I used the first combination from the second tab = A and B. If you look at these two columns on the EXAMPLE CALCULATION tab you will see 7,7 in the Number combination which is the first number pair for these two variables. The headings of the red and the yellow columns calculate the total count for this number pair and the total gain. These were recorded on a separate EXAMPLE RESULTS tab along with some other pairs which appear afterwards (these were recorded only from the first 39 rows of the AB data). I need a macro which will cycle through each variable pair (only using the combinations from the tab 2 for now, annd later from 3,4 and 5 tabs) collecting statistics for each unique number combination it encounters (printing to a separate sheet one after one), such as shown on the EXAMPLE RESULTS.
I have two columns for a Home and an Away win-loss records for a team over a few seasons. The win-loss stats in each column are in the form 24-13 (2-two digit numbers with a hyphen in between). How can I get a total at the bottom of the column for all the win-losses?
I have a trading system spreadsheet that I am at a loss as to how to calculate the profit and loss.
I was unable to attach a sample of the spreadsheet. I hope this explanation works. If not, I could email it.
Where: Market position is either 1 or -1 Entry price is the price where the position is reversed.
For example: H3=1 and I3=100.56, then H4=-1 and I4=100.88, then H5=1 and I5=100.53
I need to calculate the profit as follows: I4 - I3 (because market position of 1 expects the price to go up) and then, I4 - I5 (because market position of -1 expects the price to go down)
I want to calculate this as a running total to the bottom of the column.
This is the formula I was working with, but it doesn't work:
I have a workbook that contains several worksheets. The 2 important sheets are Imports and Sales.
In reading the MS KB, the help screens and this forum, I found a way to create a pivot table from 2 sheets using Data>Pivot Table>Multiple Consolidation Ranges. While that PT will provide a basic inventory and profit picture, it only allows 4 selects.
When these products are imported there are several SKU's. What gums up the works is there are several vendors for the same SKU's. Each vendor has a different price. The selling price is constant so the profit made on each particular item varies.
I would like to be able to keep track of inventory by vendor as well as profit by vendor.
Using the Consolidation PT does not seem to work.
I also tried making one sheet to hold all the data. i.e. data showing imports and sales. However, each entry is driven by the date. So while item A was received on 1/1/07, it was not sold until 1/2/07. The necessary columns for imports are filled in while the columns for sales remain blank until it is their row entry indicating a particular sale. At which point all the columns regarding Imports are blank. I swear, looking at the data sheet with all those blanks (actually0's) is like looking at a mouth full of broken teeth. The resulting PT is also not attractive.
I can not imagine I am the first person to have this problem. How do others handle figuring inventory and profits when you have two sheets. Are Pivot Tables the wrong solution? What would be better?
How do I track my total weight loss in excel? Here is an example of what I am trying to do.
8/1/2008 228lbs 8/2/2008 225lbs 8/3/2008 223lbs
Total 5lbs
I would like use a formula that would subtract my absolute weight loss total day by day in August. Basically, I want the total "cell" to equate to my total weight loss in August. For Example after 8/3/08 the "total cell" should equate to 5 lbs. What formula do I use to do this?
I currently copy/paste sports data from the web into MS Excel. One of the columns pasted contains "Won-Loss", which appears as '1-0' on the web. However, once the data is pasted, Excel identifies this field as a date and changes the value of '1-0' to 'Jan-'00. I would like to insert a new column and convert the field back to text (i.e. Jan-00 to 1-0). I have attempted several formulas, however no luck as of yet.
I have data from a tems current win/loss record. I want to calculate what the longest win streak is and the longest losing streak as well. What formula do I use?
I had a member make the MACRO in the attached spreadsheet some time ago, when i run it on the spreadsheet a runtime mismatch error occurs. I cant find the mismatch.
I have multiple departments I need to keep track of multiple numbers with. For example, I need to see the cost of payroll for last fiscal year, vs this fiscal year and have excel show that as a gain (bad) or shrink (good).
I need to track sales this FY vs last FY, obviously, a gain is good and shrink is bad.
Finally, I need to track d&d goods this FY vs last FY.
Where I'm getting held up is, for example last year's payroll was 12000 and this year's is 12500 but next months is 11000 this FY vs 13000. How do I get excel to figure out that payroll decreasing is good and not just do simple subtraction or negative addition thereby allowing me to just plug the numbers in and excel figure out if what I'm throwing at the cell is bad or good comparing to last year?
Also, if I could figure out the if/then to show positive numbers green and negative numbers red that would be quite awesome too.
Whenever I save and close the attached, after selecting the NO on line 23, I lose all the buttons associated with my macros. If I save with everything expanded, no rows hidden, my buttons stay put.
I am attempting to create a friction loss calculator for fire hose, I am using a known formula that calculates the loss based on volume and diameter for pipe. the difference that I have with fire hose in lieu of pipe is that the hose diameter changes with the pressure drop.
I have 3 variables that i input, pressure, beginning diameter and length. however as the water flows through the line the pressure changes in turn the diameter changes, I would like to set up my spreadsheet so that the initial variables inputed yield the correct diameter, and then reference back to the initial equation and recalculate based on the yeilded diameter, and recalculates,
I can determine the friction loss at 1 foot, in turn determine the diameter at 2 foot, but I wish the spreadsheet to work the calculation over the entire length.
I'm trying to create an excel spreadsheet that keeps track of the highest value from a dynamic series. First a bit of background:
A manufacturer produces widgets made from gold. Therefore in order to quote manufacturing prices, they typically get a live gold price using Excel's web services and if the live price is higher that the last gold price they used, then they update their base price list for gold components. If the live price is lower than the last gold price they used, the last gold price does not change.
I'm trying to produce the above logic in Excel using:
Cell A1: [Excel data connection to pull live Gold price] - no problem here
Cell A2: dynamic 'price' that only updates if cell A1 is higher than cell A2
Cell A2 is where I'm getting stuck. I can't think of how to create a formula that updates cell A2 if cell A1 is higher.
Using a macro I want to paste from the clip board. I can manually do this by opening the clipboard, selecting the data, which pastes to my specified location.
The macro copies to the clipboard okay, but loses the data that I want to paste because just before pasting I have to clear data which was previously been filtered - I have to do this at this point. The macro bogs down. If I end the macro there, I can manually open the clip board and choose the data I copied from there and paste it that way. I'd like the macro to do this for me.
I was wanting to put it on a spreadsheet. Probably a pretty easy deal, but I don't know the formula to use. this is what I want: Joes start weight 200 minus week ones weight, then the original 200 minus week 2's weight and so on till week 8. I want to keep a running total of weight lost in pounds as well as percentage.
The problem is that the table seems to always summarizes the time as well or it gives a count value of the time. If there is a better way or method to do this, I have open arms
I have a series of up to 40 or so entries for given dates. These entries are categorised by either "bid" or "offer" and I'm trying to find a way to chart them both using a pivot table. I'm stuck as it seems that a pivot can only count the number of bids and offers on any given day, but it cannot differentiate between them.
Note: that the original list the pivot depends on will constantly be updated so I can't just develop a custom chart.
I've attached a spreadsheet with an example of what I'm playing with. Pivot graph is in "PIVOT" worksheet, data is in "DATA".
I want to rank a list of teams based on their win/loss record. The list is something as follows.
Team A0-3 Team B0-2 Team C2-0 Team D1-2 Team E2-1 Team F2-1 Team G0-2 Team H3-0 Team I1-1 Team J1-1 Team K2-1
The column with the actual score contains the formula, =INDEX('2009'!$C$3:$BL$3,MATCH($A4,'2009'!$B$1:$BJ$1,0)). I would like to rank the list to something like,
Team H3-01 Team C2-02 Team E2-13 Team F2-13 Team K2-13 Team I1-16 Team J1-16 Team D1-27 Team B0-29 Team G0-210 Team A0-311
What is this error message that is popping up when I try to insert a blank row of cells?
"To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet.
If you do not have data in cells that can be shifted off the worksheet, you can reset which cells Excel considers nonblank. To do this, press CTRL + End to locate the last nonblank cell on the worksheet. Delete this cell and all cells between it and the last row and column of your data then save."
This excel document we have has LARGE amounts of data
I am trying to open an Excel Workbook which in turn will automatically run a macro on loading which is a Userform. The Userform will contain 3 boxes, just some simple text boxes.
I don't want the Excel Workbook to show in the background, so I have opted to go for a VBScript to call the Excel Application and open the spreadsheet...
I have a current .vbs script which is as follows...
Code: Option Explicit On Error Resume Next ExcelMacroExample
[Code]....
This works fine, there is no workbook showing and the userform opens as expected. The problem is, I lose the functionality to tab between the boxes. When opening the Userform directly in Excel,
