Free Microsoft Excel 2013 Quick Reference

Tax calculation Results


I have 52 worksheets, (week 1 - week 52), each is basically a timesheet
for multiple and variable staff members.
There are various calculation with regard to hours worked, tax, one each
of the worksheets.

I have another worksheet on which I wish to reference each of these
worksheet to extract a total for each week.

Q1: How do I reference each worksheet (week 1 etc) without having to
enter week 1!A35+ week 2!A35 etc?

Q2: How do I get a cell value from a worksheet into a Word document.
specifically Cell c25 and j42?

I have another worksheet which I want to add some more calculations on.
These are based on a Thursday to Wednesday payment.

Q3: Is there a simple way to use a formulae which I just type in a week
number (based on the Thursday pay date) which in
turn goes to the two week 1 & week 2 (or week 2 and week 3)etc then
puts the data in the new worksheet?

Ref Q2, I have named the cells, but I can never see them in Word.

Using Office 2000

Thanks in advance


I created a workbook that will estimate the cashflow positive price for a condo apartment.

Rent-Mortgage payment - taxes - homeowners fees (if it's a condo and not a home)


I get a value of the cashflow ie. $235.45, ($13.12) etc...

I would like the property price at cashflow=0.

Issue being that mortgage payment and tax are calculated off of that number so it seems to be a circular function.

Please help.

i have a .exe file and i want to unlock it and convert it into plain excel for formulas modification...problem is that our programmer is now dead and there is no man to solve this by unlock he takes our mainbaord serial no. and give us a password to install it and then this file has been run but now we face problem to calculate the quarterly tax and reimburse values i want the software or the solution on it ...... please help expert

For whatever reason I've been assigned to another task for taking an Excel Spreadsheet and creating an application for mortgages. I only have a very basic understanding of VBA but need to do the following which I believe to be done best utilizing a web query, a user form and output form:

Must create an application that will allow a user to download the purchase price, legal fees, property taxes, and mortgage insurance homes using this website:

Must also allow a user to calculate the total price and mortgage payments for the purchase of the home they choose.
(This is a Canadian application. The monthly mortgage payment must be based on the full house price including legal fees, property taxes (monthly) and monthly mortgage insurance costs based on the data on the web page plus GST and less any down payment.)

The user must also be able to enter in the terms of the mortgage.

Afterward the application must report to the user on a screen the housing details along with the total cost (with a breakdown of costs), the monthly mortgage payment, and the total of all their payments (with a breakdown of principle and interest amounts) along with all the financing terms.

Finally the user must have the option of printing the report in a formatted Word document.

Make sense?

Can someone help me with the code to do this? Any help would be appreciated thanks.

Hello all,
I have to calculate VAT (Value added Tax) in my excel sheet but i cannot do so because there are two columns, one has got zero vat value entries while other has got vat entries. My question is how to calculate VAT so if I enter any value in Zero VAT column, vat is calculated as 0. and if i enter value in VAT items, it calculates at rate of 17.5%.

Two columns of numbers (A & B); A always has values entered - B may or may not have a value; I need a formula for C that chooses the B value (if a value is entered) or A value if no B value is entered.

Thanx in Advance


I am creating a spreadsheet to manage my personal accounts. As part of my calculations I have included my annual tax allowance. Bearing in mind that the tax allowance changes year on year I would like to provide myself with a reminder on the 01st April each year.

Please would someone be able to provide me with some VBA code to enable this.

The cell which represents my tax allowance is B7.

In addition......

Would someone be able to provide some VBA code in order to lock a single cell/range of cells? I have been able to do this so far by utilising the Tools>Protection>Protect Sheet function and only enabling the cells I would like locked within Format Cells>Protection>Locked. However, in locking those cells I lose the ability to do various things within Tools, Format, Data, Insert.

Any assistance on both questions would be greatly appreciated.

I'm working on a userform that looks up the options selected in several tables to find the base price and the different price multipliers of the options to calculate a final price. My question is how do i make the base price, sales tax, any discounts, and final price appear on the user form. I tried using a text box to display the price output, but I couldn't figure out how to make it an output box instead of an input.

thankyou in advance

i need to either make this from scratch or change it to suit my needs.
Firstly a link to the template it is from

the code is
Sub projection()

Dim revgrowth As Single, costpercent As Single, smgrowth As Single, devgrowth As Single, gagrowth As Single
Dim intincome As Integer, noncontitems As Integer, otherexps As Integer
Dim taxrate As Single, avgshares As Integer

'dialog box gets displayed
With DialogSheets("dialog3")
If .Show = False Then
Exit Sub
End If

revgrowth = .EditBoxes(1).Text
costpercent = .EditBoxes(2).Text
smgrowth = .EditBoxes(3).Text
devgrowth = .EditBoxes(4).Text
gagrowth = .EditBoxes(5).Text
intincome = .EditBoxes(6).Text
otherexps = .EditBoxes(7).Text
taxrate = .EditBoxes(8).Text
avgshares = .EditBoxes(9).Text
End With

'calculations of projected values
Range("rev97").Value = (Range("rev96").Value + (Range("rev96").Value * revgrowth))
Range("cost97").Value = (Range("rev97").Value * costpercent)
Range("rand97").Value = (Range("rand96").Value + (Range("rand96").Value * devgrowth))
Range("sandm97").Value = (Range("sandm96").Value + (Range("sandm96").Value * smgrowth))
Range("ganda97").Value = (Range("ganda96").Value + (Range("ganda96").Value * gagrowth))
Range("intinc97").Value = intincome
Range("othexp97").Value = otherexps
Range("tax").Value = (Range("incb4tx").Value * taxrate)
Range("avgshares").Value = avgshares

Worksheets("income statements").Range("k3:O3").EntireColumn.Hidden = False
End Sub

and it looks like this

thank you for any help

have the following columns with headers
A Date (this is the date bought
F penalty percent
G Total Paid
I Year 1 Taxes
J Year 2 Taxes
K Year 3 Taxes
L Year 4 Taxes
M Year 5 Taxes
O Date redeemed
S Value today

if Date redeemed is >0 do-not calculate
We buy on a date and are owed a 5% penalty immediately plus 1% per month till redeemed. ie buy for $100 in March, 2011 and date is now Nov,2011 we are owed 100+5 penalty + 8 interest for 8 months = $113.00
Each year in December we pay taxes for up to five years
We get 1% interest on this until redeemed
So, if we pay $10 in December of 2011 and is is now March of 2012 we are owed
$10 + 3% +100+5%+ 11% since it has now been 11 months since the purchase

Same thing year 2 thru 5

In other words we buy property and are owed a 5% penalty
1% per moth
Each year 1% per month on the new yearly tax payment from that date forward

Value today is calculated by the following

Total Paid * (1+ Penalty percent) +
TOTAL PAID *(1+ 1% PER MONTH from date +
1% per month on Year 1 thru 5 taxes from December of that year

I can do the formula but in 3 parts as it is longer that Excel can handle as one formula. There has to be an easy way.


How do I get Excel 2007 to automatically place two decimal points in on any number I place in the cell?

For instance, if I enter 1345 in the cell, I want it to automatically correct it to 13.45. Not 1345.00.

Or..If I type in 35, I want it to correct it to 0.35, not 35.00.

I have tried every function in the program (I think)...not to mention I've looked for every youtube vid known and there isn't anything about this. Only how to make decimal places vary...which I already know.

Thanks for any help.


***EDIT...I use excel to calculate tax info and I input all my receipt on one row and sales tax on the other. I just want those rows to be able to have me write in the full number without having to hit the decimal key. I know it seems lazy, but when you input a few hundred receipts, every little bit counts. Lol.

How would I intergrate a pivot table to accomodate the following

I have four columns M N O & P

M takes into account superfund contributions for member 1
N member 2
O member 3
P member 4

I currently have a the sumproduct formula in M12

M12=SUMPRODUCT(--(B$10:B$10000="Superfund Contribution"),--(C$10:C$10000=L$3),--(A$10:A$10000>=J12),--(A$10:A$10000<=K12),D$10:D$10000)

The first array is seaching through column B for the words "Superfund Contribution" which has a cashbook pasted of transaction types

The second array is looking to match a tax code manually entered into cell L3, these are specific to each member and is how the calculator discerns between the different members contributions. These are listed in column C of the cashbook pasted section.

The third array and fourth arrays are inplace to ensure the summed amounts fall between certain dates, ie for row 12 it is the month of June 2008. The boundaries of this month are stoed in J12 and K12 respectively.

The final summation is of the D column where the value of each transaction is stored.

At present I have this sum product formula in about 1000 cells and unless using a super fast computer the worksheet performs quite slowly.

It has been suggested that I implement a pivot table, yet have never done so and don't know what they are or how to use them.

If anyone could help me implement a pivot table that would work in this situation I would be eternally grateful.


P.S if you need a copy of the worksheet for context I can upload one.


OK, I use a spreadsheet to track my daily tips.

Issue #1: I must contribute 10% of my tips to a tip pool for non-tipped employees, but the maximum contribution is 20.00 per day. Additionally, those contributions are always rounded down to the nearest whole dollar, never up.

So, if I make 183.00 in tips, 10% of that is 18.30, and rounds down to 18.00.

If I make 253.00 in tips, 10% of that is 25.30, but, since my maximum contribution to the tip pool is 20.00 per day, that's what I kick up.

Here is the formula I'm currently using: "=G13*0.1" (G13 is where I enter my gross tips for the day)

After the formula does the calculation, I go back and manually round it down/cap it at 20.00. Is there a way to get excel to do this for me?

Issue#2: After my tip pool contribution is taken out, 35% is withheld for taxes. Again, it is always rounded down to the nearest whole dollar, and unfortunately, there is no cap.

So, if I make 183.00 in tips, 18.00 is taken out for the tip pool. Of the remaining 165.00, 35% comes to 57.75. That is rounded down to 57.00, and I go home with the rest.

If I make 253.00 in tips, 20.00 is taken out for the tip pool. Of the remaining 233.00, 35% comes to 81.55. That is rounded down to 81.00 and I go home with the rest.

The formula I'm currently using is: "=(G13-J13)*0.35" (G13 is where I enter my gross tips for the day, J13 is where the tip pool contribution shows up)

After the formula does the calculation, I go back and manually round it down.
Can excel do this for me?


EDIT: OK, I've solved the rounding down issue with the following formulae:

Tip Pool: "=ROUNDDOWN(G13*0.1,0)"

Taxes: "=ROUNDDOWN((G13-J13)*0.35,0)"

It works perfectly. I still need to know how to cap the tip pool contribution at 20.00, though

Thanks for taking your time to help this.
My doubt is: How can I make a formulae that has a condition?
Example, I want to make a formulae to calculate the Taxes, BUT, there's no taxes whatsoever if you have no profit. The taxes formulae is, for example: =-0,3*A1 (provided the desired income for the taxes to be apllied resides in the A1 cell).
Thank you

Hello all.

I'm trying to put together a nice little invoice template for myself to be able to use for any job I work on.

I seem to be having a problem with one of the calcualtions that I am performing. Please take a look at it;


This is in a line below a SubTotal line (E40) to calculate a 5% tax on all items. When there is nothing in the SubTotal, this should show a zero......but it's showing 0.05. How do I make it show a zero instead? Is it just something simple like the formatting on the cell?

Thanks for the help on this!


Dear All,

Now that my on-line bank allows me to download my quarterly account statements in .xls format, it makes sense to have Excel work for me to calculate my British VAT tax amounts once I have catorized each expense. Certain transactions are rated at 17.5% and others are exempt or zero-rated and I would simply choose, say, a car-related cost that I can allocate the VAT and net amount and for it to be totalled at the bottom. A perfect world I hope to achieve!

Has anyone in the UK come up with a template or a set of formulas to run these ideas?

Many thanks,


I have a spreadsheet for expanded payroll...In column I2 I am trying to calculate the withholding tax...In column H2 I have a total Taxable Pay of $344.00 for this row...

In columns G3:H17 I have the following:

0 (G13) .015 (H13)
250 (G14) .022 (H14)
300 (G15) .025 (H15)
450 (G16) .028 (H16)
525 (G17) .031 (H17)

The wittholding tax in cell I2 is based on the taxable pay and the tax table. Use a VLOOKUP function to determine the tax rate, then multiply by the taxable pay are the instructions for this operation...

Can anyone help???

what i want to do is create a form in excel that would allow a user in a
different state to calculate how much thier state taxes would affect thier

for example user A lives in Chicago, so he goes to the sheet enters his
salary in the salary box, then there is a drop down that shows the different
cities and then chooses Chicago and the result at the bottom shows how much
his deductions from taxes are. if someone could point me in the right
direction i'd appreciate it.


Hi there can someone help me...
worksheet has 4 colums, column 1 contains full price of article (known
amount), column 2 has tax amount (not always known), column 3 has gratuity
amount (sometimes known), column 4 has total without tax but with gratuity.

Tax, if unknown, is calculated at 7% of amount without gratuity.

What would the formulas be to have my calculations become automatic on input
of full amount?

Thank you

I'm a one man show small business electrical contractor and am looking for a
template to use in Excell or any other microsoft program for invoicing. I
need it to calculate 1.Labour 2.Materials 3.Markup 4.Tax Just someting
simple. I've looked all over the internet and am only able to find expensive
large programs. I just need something simple. If anyone has any suggestions
please let me know.


I have been wracking my brain on this one - hoping to find some help.

My goal is to calculate my total gross payroll costs each week as well as my payroll expenses by dept. I do not need to remove taxes or benefit expenses. I have 70 employees each with his/her own hourly rate and 4 different depts.

My depts are: Group exercise: general, Yoga, Pilates and Cycling.

What I would like to do is associate each employee's first name with his/her hourly rate.

And then, on Monday, for example, under my Yoga column enter the employee names who worked that day and then get a sum of the payroll expenses for the Yoga dept on that day.

So - how to I associate a person's name with an hourly rate? (is it using the
And then how do I set it up so that when I have individual cells containing for example, "Jon, Katie, Ben, Sally" -- the cell at the end of the row = $sum of their wages earned?

Thank you!!