I am working on a budget spreadsheet and want to find a formula that will automatically tell me what my take home pay is depending on the yearly salary and see if covers my expenses (linked to another spreadsheet). It will be easier to budget my expenses depending on my salary. My attempts to create IF and = haven't worked.
View 2 RepliesI have created an monthly budget spreadsheet on excel with a calendar. Formula to show the recurring expenses in the calendar? (Example: Rent/Mortgage on the 1st of every month).
This is what I have : Monthly Budget.xls‎
Does anyone know the equivalent of CTRL (Home) to move the cursor to the a1 position in VBA? I have tried Range(cells(1,1),cells(1,1)).select but with no success
View 3 Replies View RelatedI have several spread sheets I sort daily, now suddenly today when I hit Ctrl / home it wont go home and I can not sort the sheet.
View 7 Replies View RelatedI am trying to capture billable and non-billable expenses in a time sheet by date. I thought it was pretty simple, but after a few days of battling with the syntax (I am pretty new to macros)
I have attached the file I am referring to and highlighted in yellow the significant cells.
1. Trying to get data from the all expense sheets to the "Time Sheet" Tab by date and category.
2. Need to show the billable expenses expanded out to each category (meals, hotel, etc.) by date.
3. All nonbillable is summed up in one column by date.
June expense report has:
Date of ReceiptExpense Type "Billable? Total USD
01-Jun-08 Airfare Yes - Recoverable$2,000.00
01-Jun-08 Airfare Yes - Recoverable$2,000.00
01-Jun-08 Ground Transport No - Training$20.00
05-Jun-08 Meals & Entertain Yes - Recoverable$15.00
05-Jun-08 Meals & Entertain Yes - Recoverable $50.00
1) There are TWO June 1, 2008 items that are "Airfare" & "Billable", therefore in the "Time Sheet Tab, I need it to show that under "Billable Airfare" for June 1, 2008 that it is $4,000.
2) Same as June 5, 2008 for Meals & Entertain.
3) All Non-billable (No - Training; No - XXXX; anything with NO) are summed up in one column by date in the "Time Sheet Tab"
4) There are multiple Expense reports and I need the macro to run through all of them dynamically as they input the data in to show on the "Time Sheet" tab.
This would make my job a lot easier if I could get this running. I dont' think it is too complex, but obviously too complex for me. I started on some of the vba below. I do not have all the parts yet, weird thing is, it did spit out a number, but now it is not. I'm at a loss.
...................................
In the attached spreadsheet i have a budget amount, billed to date, %complete, %remaining and forecast figure. What i am trying to do is estimate the forecast spend vs the budget or billed to date and percent remaining. I am struggling with how to do this based on in some cases the budget is already overspent but the %complete is less than 100%. What i really want to do is create a forecast based on the billed to date or budget depending on which is greater and work out estimated spend based on whether the task is complete or there is still a % remaining.
View 2 Replies View RelatedI am trying to create a spreadsheet that will keep track of budget spent and budget remaining.
I need the values to take what is spent from expenses claim forms that are filled out,
Each budget category has a code and within that code there are sub heddings
I have the following information: Capitalized Expenses TOTAL, Date of Implementation, and Life Cycle as 36-months.
What I am trying to do is write a formula that knows to calculate the total amortized expense per year based on the implementation date and the life cycle. What's tricky is that not all implementation dates begin in 2010...so the formula has to know where to begin placing the amortized/depreciated expenses...see below:
ABCDEFG1Implementation Date: 5/1/112Life Cycle in months:363Total CAPEX: 3,500 45Lump Sum201020112012201320146Capitalized Expenses 3,500
I am looking for formulas to inset in rows C6:G:6...
I've got a spreadsheet where the start date is amendable and this is what I wanted. E.g. start date is Jun-14 and I want the formula to to calculate annual salary divided by 12 in each month from June onwards until Mar-15 (Financial year is April to Mar) and the same way to not show anything in April or May as they started in June... How can i achieve this please as I tried IF statements and it doesn't recognise dates and years...
Link: [URL] ....
I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.
One method has been using this sumproduct formula:
I have a category field, and a money spent field in a table.
Category Money spent
1 $12
2 $19
2 $122
1 $5
4 $65
5 $34
5 $12
3 $26
Category 1: $17
Category 2: $$$
and so on..
So I'm looking for a formula that returns information to one cell. A formula that looks if in 'this' column you see the category code '1' for example, then return to me the money spent for ALL the 1's. It should return $17...
The table above is small just for demo purpose, my table has 8 category codes at the moment and the number of rows can be between 15 and 50 I guess... it's a monthly break down.
I would like to see how much is spent in total for each category...
In addition if possible, can this be done for two separate tables... We are two people, and I have two table recording expenditure.
So the formula would need to, from these tables:
Person A Person B
Category Money spent Category Money spent
1 $12 2 $14
2 $192 4 $25
2 $122 4 $65
1 $5 1 $32
4 $65 3 $75
5 $34 3 $85
5 $12 2 $12
3 $26 2 $11
Category 1: $49
Category 2: $$$
and so on...
Look up in both tables, search the two category columns for category '1' for example, then return all the $'s added together... So for Category 1 now it would show $49...
finding a way to compare the two budgets i.e 08/09 and 09/10 and if a new cost centre appears in 09/10 it will bring a yes in column G(New cc) and if the Cost Centre already exist in 08/09 it will bring a blank--but in both cases i want a variance in the next column H i.e 09/10 less 08/09.
View 2 Replies View RelatedI have to run payroll every two weeks, and part of the process involves calculating overtime for over 50 employees. The total amount of overtime hours for each employee is usually split on two or three budgets and after I calculate the budget splits, I have to enter these amounts into the payroll system. (The payroll system takes up to 2 decimal spaces.)
I have a spreadsheet set up for each employee, but what I've noticed is that the sum of the parts does not always equal the whole.
For example, Bob Goodworker worked 9 hours of overtime this pay period.
His budget split will be as follows:
Budget A (58.5%) = 5.27
Budget B (31.5%) = 2.84
Budget C (10%) = 0.90
However, 5.27 + 2.84 + .90 = 9.01
The payroll system only allows for numbers up to 2 decimal places.
I am looking for a formula to sort out a budget problem I am currently working on... I am guessing that the formula is pretty simple but my mind is blank...
What I am trying to do is:
Cell A1 is my cash budget
Cell A3 is actual cash used
Cell A5 is The Difference between A1 & A3 (sum A1-A3)
What I want in Cell A7 is a formula to tell me if my Budget is over 50% either way..
For instance if A1 = 100 and A3 = 155 then I am 55% over and would like a note to appear saying "Explanation required".
If A1 = 100 and A3 = 45 then I am 55% under my Budget and would like a note to appear saying "Explanation required".
I am having an issue creating a formula that calculates % of Budget based on signs. What I mean is positive budget, negative actual and vice versa. Here are all the cases. Can someone please create a formula that I can copy down.
Actual Plan% Budget
85,60696,85188.4%
70-82185.9%
-1,530-3,786247%
-100100-200%
I am prepare budget salary base on actual joining date and budgeted joining date. I can only manage to get the formula by joining month not by joining date
View 10 Replies View RelatedI have a small online business and am slowly learning Excel to keep my records. I looked at Quickbooks and I think that it just a little too complicated for my needs, besides I like excel better.
The spreadsheet I want to make is how can I summarize the different categories, shipping, travel, EVSE, Wire, or whatever I come up with in the future from a daily expense spreadsheet. I guess the summary should be on another page.
I also guess I can make up a total also of the companies I buy from...
I've attached a beginning daily expense spreadsheet with some entries.2014 costs.xlsx
I work in a large Public Service Payroll Department. I am trying to create a spreadsheet that will automatically calculate arrears payments for salaried staff. What I need is something that will make the calculation after I provide a start date an end date and old and new salary per annum. I'm self taught using excel but at a very very basic level.
My problem is that I don't know how to enter a formula which will give me the correct amount between 2 dates. For example
01/10/2005 to 16/09/2005 old salary PA 16000 new salary PA 17500. The manual formula would be:
((17500 - 16000)x1/12) + ((17500 - 16000) x 1/12 x 16/30)) = £191.67
I have percentages set up for my monthly costs (student loans, food, spending money) and I want to be able to type in my weekly salary and have the percentages break it down into dollar amounts. Later, I'd like to put that into a pie chart, but I'm really only asking for help referring each category/percentage to the total $ value I enter each day/week/month.
View 4 Replies View RelatedI 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!
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"> Deposits
Expenses
Monthly Balance
Surplus/Deficit
$ 2,058.20
$ 1,509.10
$ 753.86
$ 549.10
$ 2,185.05
$ 1,939.35
$ 999.56
$ 245.70
$ 1,078.90
$ 1,278.50
$ 799.96
$ (199.60)
I want the Surplus/Deficit cells Highlighted in red if the Deposits are less than the expenses.
Can I this? Maybe using a macro or formula not entirely sure
If so how?
I would like to be able to this myself but could not figure it out using conditional formatting in Office 2007.
Lets say I have
Admin:
Base Salary
US Duties
Consultant:
Base Salary
Senior Advisor
Is there a way to create a macro that will change 1 of the Base Salary(s). I need one of them to be Base Salary - Admin, or Base Salary ' or something different for my vlookup.
Will this macro work for each different tab I have?
when you open up the file please ignore everything above the blue bar. That was the old data which was arranged wrong. I have actually figured a lot of this out but i am stuck. The graph is set up so when you click on the drop down menu (F26) it changes the graph to the relevant data. Now the bottom graph is currently graphing the Months Expense1 as a total of all Expense1. So January Expense1 was $100 so its 26% of all Expense1. and when you click on the drop down button you can changed the data to Expense2 and the graph changes. YAY ok thats cool. However, thats not what i want. I want the pie chart to be graphing the expenses as a percentage of total Expenses. So the drop down menu would be of the months instead of the expenses.
I have changed that with relative ease, but I cant get the data to graph how i want it to.
I have been using OFFSET() formula and the define name manager to set up the previous graph. You can easily look at the formulas i have used instead of me trying to explain everything. Please help. This is the test bed for a budgeting spread sheet I am working on.
if you can help me out that would be great.. Please try not to use Macros because i dont understand them all that well and I need to take what is done in this spreadsheet and learn from it and change it so it works when are thre 10 expenses.
I have this table which has employees names with basic pay and contribution.
I was tasked to create a excel sheet where in if the employees basic pay is input, the corresponding contribution for the employee will show depending on which salary bracket he/she is on.
I am not allowed to know what my workmates salaries are since im just an associate, so i just have to leave em blank and wait for my manager to input their salary.
See attached file for the workbook : PHEALTHWORKBOOK.xlsx‎
Example.xlsI have a list of people with 10 years of salary history for each (in ten consecutive columns on the spreadsheet). I need to calculate in excel the highest 5 consecutive year average salary for each (if they have less than 5 years with salary, then it should just average the years the do have). Some people have breaks in service (for five years, there is a blank in that entry). These years should be ignored and skipped in calculating the avergaes.
View 3 Replies View RelatedI'm working on making a monthly expense report with 2 sheets; the first sheet would include each individual expense and the expense would placed in a certain category. The second worksheet would be a summary of the total expenses for each category.
I would like column A to be a drop down menu:
Column A: Expense type (Stationery, Kitchen, Maintenance, etc..)
Column B: Amount
This would continue for as many rows as i need
Then on a different worksheet it would add each item based on its category and give a total for each category.
I am writing a sports schedule or fixture list. The home teams are in column B and the away teams are in column C.
I want to prevent any team having 3 consecutive home matches. Is there any way a formula can be used to alert me if there are 3 consecutive home matches.
I am using Excel 2007 and have to send monthly payslips to respective email ids. I have the Name and Email ids in Sheet 2 Range B1:C59 , and employee codes in A1:A59. And in Sheet 1 i have the Payslip format which was automatically displayed when i select the employee code from drop down list in the cell E7 in sheet 1.
Every time i manually save the files as PDF and send to their Emails. I want the out put as whenever i select the employee code from drop down list , it should automatically save as PDF with Name of that employee ( Name of the employee is located in B1:B59 in Sheet 2 ) and attachment should automatically go to that employee's email Id.
I have been searching Excel Help for a code that will allow me to program the Ctrl + Home function.
Specifically, I have a macro that leaves the selected cell too far down on the spreadsheet. Instead of making the user scroll up or press the Ctrl + Home keys, I want to write a code at the end of my existing macro.
I have used the PMT function but this gives me the total to pay per month.
I want to know what the repayments would be and also allow me to add in each one of the periods and extra repayment option.
So if in the year I wanted to pay an extra $100 per month, I would put $100 next to each period as in a particular period(s) I might not have to put in there but want to predict what the amount owing on the house is.
Is this possible or is this too complicated.