Free Microsoft Excel 2013 Quick Reference

- Referencing worksheets
- As Sum Approaches zero indicate number.
- want to convert excel_exe file to plain excel
- Web Query & Userform
- Choosing an Excel Value from multiple columns
- VBA Automated Reminder Message based on Date
- Out on userform
- How do i change or make this macro??? pictures and code included
- Caluclating value of Investment
- Just two places...like an adding machine
- Implement a Pivot table
- Excel 2003: rounding down and capping
- How can I input a formuale with a condition?
- Formula should return zero, but gives 0.05 as percentage
- UK VAT template, anyone?
- Vlookup function-for expanded payroll
- Creating Forms in Excel
- Help with a formula:gratuity amount
- Is there a small business time and material invoice template?
- Tracking payroll by employee's first name

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

Kevin

--

Kevin

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

=$Q2-$S2-$T2-IF($U2="Non-Condo",0,$U2)

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.

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: http://zakhomes.blogspot.com

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.

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.

thankyou in advance

Firstly a link to the template it is from

http://www.exinfm.com/excel%20files/FY2000WhatIf.xls

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

http://i2.photobucket.com/albums/y10...5/094c74ca.jpg

thank you for any help

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

Rules

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.

Thanks

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.

-Andy

***EDIT...I use excel to calculate tax info and I input all my receipt data...total 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.

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.

Cheers

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

Thanks

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?

Thanks

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

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

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;

=IF(PRODUCT(E40,0.05)>0,PRODUCT(E40,0.05),"")

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!

Cheers!

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,

Richard.

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???

different state to calculate how much thier state taxes would affect thier

pay.

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.

thanks,

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

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!!

No luck finding an answer? You could always try Google.