Free Microsoft Excel 2013 Quick Reference

# Formula for sales taxes

I would like to create a custom invoice that would automatically apply the total sales tax amount in the designated cell. I assume I would have to create some sort of conditional formula. Some of the items listed will be taxable and some not. I would like to place a T in a column (for example column A) that precedes the column (for example column B) containing the amount of the taxable item in the same row. Column B would also contain non taxable items. Then I would like for Excel to add up all of the items in column B that had a T in column A on the same row and multiply the sum by the tax rate. I assume the formula would start out with =if(a1=t) but not sure how the rest should go or even if my start is correct. Could anyone help?

## Related Results

### Formula for sales taxes

I would like to create a custom invoice that would automatically apply the total sales tax amount in the designated cell. I assume I would have to create some sort of conditional formula. Some of the items listed will be taxable and some not. I would like to place a T in a column (for example column A) that precedes the column (for example column B) containing the amount of the taxable item in the same row. Column B would also contain non taxable items. Then I would like for Excel would add up all of the items in column B that had a T in column A on the same row and multiply the sum by the tax rate. I assume the formula would start out with =if(a1=t) but not sure how the rest should go or even if my start is correct. Could anyone help?

### Multiple sales tax rate

I am looking for a formula for sales tax, where the tax is based on the amount of the item sold
example - if one item sells for \$5000, the first \$1600 of the item is taxed at 6.25%, the amount between \$1600 & \$3200 is taxed at 2.75% and the amount over \$3200 is taxed at 1%
Thanks

### Nested If/Then/Vlookup on an array

Posted this question yesterday but I'll be darned if I can't locate the thing in any forum!

Ok, I have a workbook with two worksheets.

Worksheet 1
---------------
Column A = 5-digit Zip Code
Column B = Zip+4 low end (4 digits only)
Column C = Zip+4 high end (4 digits only)
Column D = Sales Tax Jurisdiction Code

Worksheet 2
---------------
Column A = Customer ID number
Column B = 5 Digit Zip Code
Column C = Zip+4 (4 digits only)
Column D = Formula for Sales Tax Jurisdiction Code

I need to be in Worksheet 2 and look up the 5 digit zip code from W2/C:B and match to W1/C:A. Once it finds the correct 5-digit zip, I need it to loop thru the zip +4's on worksheet 1 (within the correct 5 digit zip) until it finds the 4 digit zip that falls =< W1/C:B AND >= W1/C:C. Once the correct 5-digit zip is found and the proper row for the 4 digit zip I need it to return the Sales tax jurisdiction code from W1/C:D to W2/C:D

Can anyone help me get started?

### Formula for figuring Pre-Taxed Price & Sales Tax when Total Price is known

Formula for figuring Pre-Taxed Price & Sales Tax when Total Price is known:

If I know the TOTAL PRICE (say \$30) and the SALES TAX is 8.25% of the PRE-TAXED PRICE, what would be a good formula to figure the PRE-TAXED PRICE & the SALES TAX?

### How do I create a formula to calculate sales tax using Yes & No

I have a spreadsheet set up and two of the columns will have yes or no in
them, I want to calculate the sales tax that was included in the po......see
below......

PO Amt. Taxable material Taxes Included in PO Sales Tax Due
\$900.94 Yes Yes
\$68.64 (how do I
get this # with a formula)

Kel :-)

### Formula to calculate sales tax from total sales

My husband and I just opened a business and I have created a chart on excel
for us to track daily sales but also to figure sales tax so we know what to
send the IRS each month. We have been figuring the sales tax ourselves and
filling in the chart on excel but I would like to create a formula that
automatically does it for me based on total sales.

### Sales tax formula

Hey guys,
I need a formula for the following issue:
the sales tax in a Purchase order i want it to be like that, if it has the word "included" chosen from a drop down menu, then the sales tax cell shall be 0. Otherwise, if "included" is not chosen then sales tax shall be normally calculated.

can anybody help with this problem please ASAP!!

Also the excel sheet is attached.

Thanks in advance.sample mec po 11.xlsx

### Help! Need simple template to record invoices + sales tax

My needs are simple. I have a huge stack of handwritten invoices that I want to record into Excel (I guess). I would like a template set up to allow me to enter/retrieve all customer contact info (name, address, phone #"s, invoice date, amount billed, tax charged and a non-tax category, a cell for reminder codes, a cell for notes about the job or customer. And I want to be able to have the worksheet set up so I can easily get subtotals for invoices written in any given month or quarter or year (selecting a range of cells). AND I want it to calculate the taxable and non-taxable totals by quarter so I can figure total sales tax owed per quarter, as well as taxable and non-taxable sales.

I want this template to have the formulas built in (I am not a math wiz and not a bookkeeper, although I am forced to the task.)

Have searched online for such a simple template but everything seems so complex and not suited to my needs.

Can you help me find such a creature or make a recommendation?? There must be an easy way, if I can only find it. (Sales taxes are due soon! Help!) We are a small business.

Thanks for listening!

### State and Local Sales Taxes

Our state carries a 4% sales tax on all items except food and prescriptions.
Our county carries a 3% sales tax on everything.
Attached on my work sheet:
Column "C" determines if an item is either food or non-food.
"G5" is the subtotal of column G
"G4" is the S/tx on "G5" at 3%
"G3" is the S/tx on "G5" at 4%.
"G2" is the gross pay out.
My question is:
I'd like a formula for Cells "G3" and "G4" that can determine which items paid for in column "G" match a "N" or an "NF" in column "C".
If an item in column "G" represents a "F" in column "C", then there should not be anything in cell "G4"
If an item in column "G" represents a "NF" in column "C", then there should be a figure in "G3" & "G4".

### Formula for Customer Sales Trends

I am trying to find a formula for the following scenario;

Column A Customer
Column B Average Sales Trend ex. -3%
Column C Current Sales Trend ex. -25%
Column D (Need Formula)

If Column C is less than or = to 10% lower than column B, TRUE

Any help would be appreciated!

Thanks!

### Backing sales tax out of a total including sales tax

I have only Totals including sales tax. I am needing to back out the sales
tax to figure what the sub totals were before applying the 7.75% sales tax.
I can't figure a formula for this task can anyone help me?

### Create a formula for price * discount* tax =final price

I am trying to compute the following: multiply the price times the discount %
and then subtract that from the price. Then take that answer and multiply it
by the tax %. For example price is 10, discount is 20% and tax% is 8%.
price is 8 after discount
multiply 8 times 8% = sales tax
and then subtract that from 8 to get the final
price of 7.36
Thank You very much for your help
A

### Complex Sales Tax

I have created a spreadsheet for aiding me in an automobile purchase.
It computes the invoice, fees, taxes, and monthly payments. Generally,
sales taxes are straight-forward, yet sales tax in my county remains
complex. My County charges 7% on first \$5,000 and 6% on the rest. My
spreadsheet has a cell with the sales tax rate, but the sales tax
calculation formula assumes a constant rate. I need some advice on the
best approach on calculating the sales tax and the related formula.
Should I use the MOD function or compute a fixed rate? Please explain
the formula since I am fuzzy on it.

I need some suggestions from the community. I'm doing a spreadsheet for the Sales department in the company.

I have used formulas such as AVERAGE , MAX , MIN , SUM. This are considered Basic formulas.

I also used VLOOKUP, SUMIF, COUNTIF, AVERAGEIF , SUMIFS , COUNTIFS. This are considered advanced functions.

I need 3 additional formulas. and for that , i used ROUND and RANK.

I need 1 more formula. Can someone suggest to me what formula is suitable?

This is urgent. Thanks!

### Sales Tax Spreadsheet formula help

Hello.
I made a spreadsheet for computing sales tax in Pa. I am coming up with different amounts of sale tax when figuring tax on each item and when figuring tax on the total. attached is an example sheet. Rows 2-26 are individual items with the totals in row 28. Row 30 contains tax and price using the total in cell A28. I know that 3 cents is not alot but I think both figures should be exact. I hope I have explained this correctly.

Thank you very much for replies,
Chuck

example.xls

### Need help creating an Excel formla to calculate price before sales tax

Hi, can somebody tell me how to set up a formula that will give me the
BEFORE TAX PRICE given SALES TAX RATE and FINAL PRICE?

For example, I sell 3 apples to Bob for \$8. Assuming sales tax is 7%,
I need a formula that will show me what the before tax price is.

I know that the equation on paper would be something like:

BEFORE TAX PRICE + (BEFORE TAX PRICE X 7%) = \$8

But how do I set something like this up in Excel so that I can simply
input the FINAL PRICE and have the BEFORE TAX PRICE outputed? Thanks!

Jason

### Formula for loan interest..

I have not used excel for years, I would appreciate help with formulae for the following..

I have an overdraft loan and have borrowed money for 2 items (a car and a house deposit) I must show our tax office exact amounts for the capital and interest for each item monthly going back about 2 years. The bank are not helpful as they just take one payment monthly, the statement does show the monthly interest rate and the rate has changed a few times.

I have been considering setting up a spreadsheet that would:

Divide the total monthly payment made as a percentage: say 58% for Car and 42% for house.. showing:

Car capital repaid
Car loan interest paid
House capital repaid
House loan interest paid

I hope this is logical, perhaps there is a better way?

Sincerely

Piazope

### Copy formula for the 12 month

i have in column a the 12 month (a1:a12)
in column b i have to write the formula for the sales result of a saleman
eg in b1 i will have his sale for january (=january!m12)
i want to copy down the 12 month so i will obtain in b2(=february!m12) in b3 (=march!m12) and so on... i did not find the way of doing this and i dont want to do it by clicking the month sheet and then the cell in wich i have the sesult
thank

### Seperation of discretionary sales tax from total amount

I am looking for a formula for the following

data
b1: invoice total w/tax
b2:5510
c1:pretax amount
c2:????

d1 6% tax
d2 =c2*.06

e1 .5 tax
e2. ???

So what i have is the total invoice with tax, i can figure the 6% but in my state we have a discretionary tax of 6.5%on the first \$5000, and you need to seperate how much the additional .5% is (need this formula)

plus i am having a problem finding the pretax as a result....any help would be appreciated, and i may have forgotten some information that you need to answer so i appologize

### Need formulas for Net Income

I need to do a spreadsheet with the following data:
1stqtr 2dqtr 3dqtr 4thqtr
150% 200% 150% 250%

Cost of sales 30% of sales
fixed expenses:\$20/Mo
Variable expense: 10% sales
Can you help me get the formula for the Net Income?

### Formula for payroll tax limits? any ideas? thoughts?

to be used in an annual budget that tracks employees salaries by month in columns left-to-right? what formula could be used to manage payroll tax limits and thresholds? i'm hoping to find a way to build the formula in the sheet so that as salaries increase over time, the formula will stop the different taxes as the salaries reach the limit, like fica, futa, and suta do? any ideas?

thanks from a newbie.

### ROUND formula on GST _(TAX 6%)

My ROUND formula for GST 6% (tax) on the Invoice is off by One cent. If my SUBTOTAL is … \$1045.40, The SHIPPING … \$14.07 Then the GST (tax 6%) on SUB TOTAL and SHIPPING should be \$63.56. My invoice shows \$63.57
Here are my formulas that I use on the Invoice

Quantity: L20 ___ ( 1 )
Unit price: C20 ___ ( \$1045.40 )
My formula for TOTAL: =IF(L20"",ROUND(L20*C20,2),"") ___ (\$1045.40 )
My formula for SUBTOTAL: =SUM(M19:M43) ___ ( \$1045.40 )
SHIPPING : blank _ insert able ___ ( \$14.07 )
Formula for GST (tax 6%) : =ROUND((M51+M53)*6%,2) ___ ( \$63.57 )
Any help would be greatly appreciated.
Thanks,

### Sales Tax, help with formula

Hello, I am new to excel and need your help in trying to solve this, so your help is greatly appreciated. I'm trying to multiply a cell C12*A6 plus add a sales tax 0.08 in cell D26, but I would like to have this rounded up to the next dollar amount, i.e. \$1,546.48, would read \$1,547.00. How would I go about this. Thanks,

### Sumproduct or array formula for countif

Hi

I have a spreadsheet with Months as headings across the columns and products down the rows.

The range: A4:G50. ( Including headings ).

The cells are populated with sales per product for each month listed.

In cell E3, I would like to count how many products had Zero Sales across the six months.

I can do it by separately summing the columns for each product and counting the products with zero sales for the six months - but I have other info in those cells and I do not want to change the spreadsheet.

Thanks

JVN