Compress Data For Annual Return - VBA
Mar 4, 2014
I have the data as below. And to submit this to the third party is needs to be compressed (a total for each Full time ANNUAL salary).
RULES
All total monthly earnings must start from 0 when a "Full time ANNUAL Salary" is added or endedIf the Annual amount misses a month I need to assume the salary has ended
I hope this makes sense.
Below is test data for one person. And the result i am hoping to get with some comments.
Employee
Full time ANNUAL salary
Monthly earnings
Pay date from
Pay Date to
[Code]....
The result of the above would be:
Employee
Full time ANNUAL salary
Monthly earnings
Pay date from
Pay Date to
Comments
[Code]...
15k started 01/06/2013 so the total for the 10k starts again, alongside total for 15k
Chris
£15,000.00
£450.00
01/06/2013
31/08/2013
[Code]...
15k started 01/12/2013 so the total for the 10k starts again, alongside total for 15k
Chris
£15,000.00
£150.00
01/12/2013
31/12/2013
[Code]...
10k ended, 20k started 01/01/2014 so total for 15k starts again, alongside total for 20k
Chris
£20,000.00
£400.00
01/01/2014
28/02/2014
[Code]...
20k ended so total for 15k starts again
View 2 Replies
ADVERTISEMENT
May 5, 2012
What formula to use to show the Annual ROI for a real estate investment property (single family). I believe I have all my figures to calculate WITH (acquisition amount, annual net income, etc.) -- but I can't figure out how to determine the ROI.
Or - is determining that % even possible/logical?
View 3 Replies
View Related
May 17, 2009
I have been trying to compile some monthly and annual data from our main sheet to a FY09 sheet (for the fiscal year 2009). The 2 tabs in the uploaded example are the Distribution tab which we use to track the status of every item and the FY09 tab where I need the totals to be calculated for each month as well as the entire year. I have tried several formulas I found while searching the forum but I can not seem to get any of them to work, (I am sure it is because I don't understand them very well).
Since any formulas used will need to be copied 200 or so times, I would really like a VB solution which should also reduce the physical size of the file. I also tried a pivot table but I do not think it will show everything I need.
Basically, I need all the items separated by month on the FY09 tab. Then column 'F' on the Distribution tab needs to be summed up for each item in column 'D' of the FY09 tab for the respective month and multiplied by the respective item price in column'C' with the total value going in column 'E'. Column 'D' and 'E' need to be summed up for each month and cumulative for the entire year. There needs to be an average items and value for each month as well as for the year.
View 8 Replies
View Related
Nov 13, 2008
how can in compress the following formula
=IF(B3="Monthly",IF(B25="Windows 2003 Std",15,IF(B25="Windows 2003 Ent",30,IF(B25="Windows 2008 Data Center ",40,IF(B25="RHEL 5","30",IF(B25="Windows 2008 Web","15",0))))))+(IF(B3="Quarterly",IF(B25="Windows 2003 Std",40,IF(B25="Windows 2003 Ent",75,IF(B25="RHEL 5","89",IF(B25="Windows 2008 Data Center ",120,IF(B25="Windows 2008 Web","45",0))))))+(IF(B3="Semi-Annually",IF(B25="Windows 2003 Std",75,IF(B25="Windows 2003 Ent",140,IF(B25="Windows 2008 Data Center ",200,IF(B25="RHEL 5","149",IF(B25="Windows 2008 Web","90",0)))))))+(IF(B3="Annually",IF(B25="Windows 2003 Std",150,IF(B25="Windows 2003 Ent",300,IF(B25="RHEL 5","299",IF(B25="Windows 2008 Data Center ",400,IF(B25="Windows 2008 Web","180",0))))))))
View 5 Replies
View Related
Feb 12, 2009
I need a macro where I can compress the lines in the report below. By other words will I end up with totals on lines with the same text information. This report can have different number of lines, so it is an advantage if the macro is dynamic.
BD 000012EXPI2CAC2009SBU48595,98
BD 000013EXPI2CAC2009SBU338903,14
BD 000013EXPI2CAC2009SBU1887610,1
BD 000013EXPI2CAC2009SBU154462,84
BD 000013EXPI2GAC2009SBU739214,99
BD 000014EXPI2CAC2009SBU111295,82
View 6 Replies
View Related
Mar 6, 2007
i'm using the following code to compress a worksheet, but i get an error message that i would like to surpress...
'zip
On Error Resume Next
Dim FileNameZip, FolderName
Dim strDate As String, DefPath As String
Dim oApp As Object
DefPath = "c: empsalesmanMDBed"
If Right(DefPath, 1) <> "" Then
DefPath = DefPath & ""
End If
FolderName = "c: empsalesmanMDBed" '<< Change..................
View 9 Replies
View Related
Jul 25, 2007
i'm still have troubles compressing an external xls file from within another xls file...?
(sorry...i did a search...)
???
(have to post the current code)...
View 5 Replies
View Related
Mar 19, 2007
I'm looking for is ComplyXL and that costs $300 to buy. So, as a poor grad student, I'm looking for an alternative solution - either a freeware program or a hassle-free VBA code.
So here's what I need the code or program to do: When I ask excel to open a pre-existing file, I'd like it to automatically rename (& compress?) the last used version of the file & save that to a filename-specific archive somewhere (hidden?), before opening the file as requested. Alternatively, I suppose that a mirror copy of each 'just used' file could be created & saved to an archive folder whenever the used file is saved & closed.
--Note: to avoid making copies of EVERY workbook I ever use, I suppose there should also be a button in the toolbar or a file-associated setting that lets me identify which files I want this task to apply to...
Basically I'm looking to have excel automatically save versions of certain key files (like Word's 'Versioning' feature) without having to remember to 'Save As', without the versions taking up much space, and without having to store the older file versions in the same place as the most recent one.
And yes, I know that a similar problem has already been posted & solved here, but they didn't include a way to choose which files used versioning nor (if it's possible) a way to compress the archived versions to save space (maybe by adding it into a WinRAR archive somewhere?).
View 11 Replies
View Related
Jan 27, 2012
I am trying to make a spreadsheet for a customer and would like to include a feature where the list of parts can be expanded or compressed with buttons.
Each SO# has a set of PO#'s associated with it, and it would be nice if the viewer can choose to see the PO#'s or not see them at will (this will make a lot more sense if you see the spreadsheet example in my link.)
Ideally, there would be a button next to each "SO#" which would expand/compress accordingly. I don't want just a universal button that would expand/compress all of the SO# at once (although having this option on the side would be nice).
here's link to my spreadsheet: [URL]....
View 2 Replies
View Related
Oct 14, 2013
Here is something I have noticed with using Compress Images in 2010.
When I run Compress Images from the save as dialog box in 2007, the file size is slightly larger than if I run it in 2010. However, if I then send the resulting excel file out to people, if they are viewing it on an iPad, then they cannot see the images at all from the 2010 version but can see them fine if the excel file is from the 2007 version (with exactly the same data and images in there).
I cannot figure out why this may be. The options on both are set as "Email (96ppi)" and with "Delete Cropped areas of pictures" ticked.
View 3 Replies
View Related
Jun 5, 2008
Have two tables of data the first is shown below (the first line is headers)
DogCatReptileMonkey
SmallBigNosyFluffy
SmellyNosy
The second is (again first line is headings)
SmallSmellyBigNosyFluffy
AAGFF
BGHGE
CTUBQ
Need to create code which reads the first table, finds the categories of the first table in the second table and puts these with the original headings into a new table so in this example the end table would look like -
DogCatReptileMonkey
AGFF
BHGE
CUBQ
AF
GG
TB
My original has about 150 headings in table A and B hence why I have given an example
View 5 Replies
View Related
May 26, 2014
A1 is a date
I need B1 to show 1 year from A1, unless it has already passed, in which case I will need additional years added until the date being displayed is either today or in the future.
For example, if A1 is 1/1/2010, B1 should be 1/1/2015
View 12 Replies
View Related
Apr 4, 2009
I have daily streamflow data for a large number of years. One column is the date and the second column is the streamflow value. I want to compute the maximum value and the 3-day maximum (largest average value for any consecutive 3-day period)for each water year (starts on October 1 and ends on Sept 30). Of course the number of days each year varies by one during leap years.
View 5 Replies
View Related
Oct 27, 2006
There are two tabs in my workbook right now. The first is where users will input information and annual budget number for consulting fees. The second tab spreads the annual budget by the 12 months. I don’t want the users to see or touch this tab containing the spread. Once the annual budget number is entered, I would like them to use a combobox to choice how they would like to spread the budget monthly. One choice is evenly which is the default and the other would be manually. If they choice manually I would like a userform or something where they than input each month’s amount that will total the annual budget. See attachment, this is all I got so far.
View 2 Replies
View Related
Jun 28, 2014
I'll try to be as concise yet informative as possible. In the included attachment you will find a list of names in column A. These names have each have an event that is due at the end of his/her month, once annually. The due month for each person is listed in column B. Once that person completes the event, it is recorded in column C. The event will then not be due until the following year by the end of their due month.
I need column D to reflect the last day of their due month.
This date will change once the event is completed and recorded in column C. There are of course a few additional requirements that puts this out of my ability. They are listed below:
The person may complete the event at any time within three months of their due month. So if Tom's due month is September, he may complete the event anytime in July, August, or September. This would then need to reset the "Due by" date (column D).
If the person does not complete the event, the original "due by" date should not change until the event is completed.
Last note: this attachment is from Excel for Mac, The actual sheet this will be used on will be windows (so no worries about the formula changes for Mac).
Recurring due date help.xlsx
View 4 Replies
View Related
Dec 27, 2008
I have list with 20000 rows, I need to count total annual issued qty. find attached sample for more details. I have solved. I used pivot table.
View 5 Replies
View Related
Aug 8, 2006
I am trying to calculate the compound annual growth and my starting point is a negative number. The example is that I have (273,000) for the YE 2003 and have Growth to the amount of +767,000 at YE 2006. The formula I have been using for other calculations where the starting point is positive is =POWER(J23/C23,1/O$1)-1. Where J23 = a positive amount and C23 = a positive number. This formula works fine, but when C23 = a negative number the formula does not work and the % does not make sense.
View 6 Replies
View Related
Apr 8, 2012
I have 7 teams (82 staff in total) staff who work for several production line. we currently record all leave on the wall calender. I want start recording these on a spreadsheet and I wonder if any of you have already designed a annual leave planner that I could have a copy?
Staff can request for 1/2 annual leave as we all full day. Each Team is listed on a seperate sheet and if a team has more than 2 person on leave, it will go red.
View 3 Replies
View Related
Jan 12, 2014
I'm doing an exercise for school and I'm totally confused here. I have to:
With the original assumptions, goal seek to determine what the Annual Rate Increase would be for the total expenses to be $175,000 (answer = 20.77%). Here's what I have: through the process?
View 1 Replies
View Related
Aug 18, 2009
If I invested $350 per hectare into a project and at the end of 12 years that investment yielded me $150,000, what would my rate of return be over the life of the project?
View 9 Replies
View Related
Feb 13, 2010
I am trying to calculate APR (Annual Percentage Rate) for a mortgage loan that has a balloon feature. I have tried to the the RATE function but it only gives me the APR for a loan that is ammortized over 30 yrs and paid in 30 years. I need the APR for a loan that is ammortized for 30 years with a baloon in 5 years.
View 9 Replies
View Related
Nov 15, 2009
What would be the best way to put together a cancel curve on an annual membership model?
View 5 Replies
View Related
Feb 28, 2013
I've put together an annual leave tracker for the department and am trying to make it as automated as possible. It currently consists of two sheets, 'Leave Applications' and 'Leave Tracker 13'.
If you look at the 'Leave Applications' sheet, I wish to be able to take this data to fill the calendar on 'Leave Tracker 13' with either AL or HL to demonstrate whether the day is annual leave or half day leave. This only wants to happen if the leave has been accepted, represented by an 'A' on the 'Leave Applications' sheet. The calendar obviously doesn't have any dates in the cells but I've got around this when it comes to shading in weekends by concatenating the date above and to the left of the cells and assume I must be able to do the same for populating it.
It's also complicated by the 'Leave Applications' consisting of multiple members of staff, though this may be ordered by date.
View 11 Replies
View Related
Jun 27, 2012
I am trying to create simple leave data base using Excel. Is it easy or complicated?
View 2 Replies
View Related
Jul 25, 2012
I need to design a spreadsheet in Excel that keeps track of annual expenses for the company car, but have no numerical data.
The only information given is the labels needed to be used, such as main heading, date, mileage, petrol, maintenance, registration, insurance, subtotals, averages and main totals. I've designed the spreadsheet with date - subtotal at the top of each column, with total and averages on the left hand side, under all the months (so the answers to both the total and averages would be in each column)
I also need to write a formula to work out the averages of everything and the formula I thought would be right is giving me this error message: #DIV/0!
View 2 Replies
View Related
Mar 24, 2008
How can I get the median of a column that contains both hourly and annual rates? I would like to either multiply the hourly rate * 2080 or divide the annual rate by 2080 then get the median.
View 6 Replies
View Related
Nov 17, 2007
I am trying to figure a formula that will give me a monthly increase with a annual target. Example:
2006 sales were $100,000 and December 2006 sales were $9,000.
For 2007 I would like a 10% increase in sales which means I should end up with $110,000 at the end of 2007.
The formula should figure a gradual sales increase each month until December 2007. I am assuming the business sales trend is upward with no seasonality so that January 2007 should be the annualized increase over December 2006.
.
I have used the formula =100000*((1+.1)^(1/12)-1) which works for 2007, but it gives me a large jump between December 2006 and January 2007 and then a slight increases each month.
View 3 Replies
View Related
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
Jan 30, 2014
I am trying to create a data entry sheet to enter quotes on. When a quote is received, I click on my "Add quote" button and a userform appears. Data is entered into the userform (frmEntryForm) and returned back to the next available row.
I also need to be able to:
Edit a row by double-clicking it. When a row is double-clicked, data from that row is passed back to the userform, edited and returned back to the same row (to prevent duplicates).Validate that all fields are complete within the userform where relevant (i.e. if the work is not complete or in progress then the "Invoice Number" and "Actual Cost" fields are disabled and blanked to prevent entry (I think this is almost sorted judging by my tests)
I have attached my sheet : 2014 Gatwick Quote Log (Macro Enabled).xlsm‎
View 14 Replies
View Related
Jun 6, 2014
What I need is a worksheet which will generate fields from a set of variables (similar to a loan amortization schedule).
I don't know how to write the field code to include the annual percentage rate increase based on that additional input variable.
Input variables would be:
starting rate: (ex: 8¢/kilowatt hour)
starting date: (ex: July 2013)
average kw hours/month: (ex: 1062.5 kw hours)
annual rate increase: (ex: 3% *default 0%)
years to display: (ex: 25 years, *shown in months in fields below)
service months per year: (ex: 12 *default 12)
[code].....
So, data fields below the variables would be blank until these are entered.
Assume I would protect the sheet to avoid messing it up.
this would generate a chart showing rates over time from the fields below.
Also it would generate fields in a summary area to show the rate per year for future years.
Secondary, if there were a way to input actual usage and rates from specific months in the past, that would be useful but not part of this forecast model.
The goal of this model is to forecast electrical costs for residential power users compared to independent solar power generation to 25 years forward.
I want to make this because I am skeptical of the advertised savings that solar installers use to convince clients that their return on investment is so high. I believe their data to be greatly inflated projections.
Using this in comparison with other data such as inflation and wages will allow me to get a very good economic picture of the future of independent solar energy generation and its impact on the average home owner.
View 2 Replies
View Related
