Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Conditional tax calculation formula

Hello all,

I am new to this forum and happy to have found it!

I am attempting to create an Excel spreadsheet with many conditional formulas. This will be used to help me determine appropriate state transfer and recordation taxes for title and settlement purposes.

My problem is that I cannot figure out how to implement multiple conditions for the total calculations.

For instance, I need to calculate both a county and state transfer tax, as well as a state recordation tax. The county rate is 1% and the state transfer rate is .50%. The recordation tax is $3.50 per $500 of the purchase price.

This is all straight forward until I try to make the formula apply to all 24 counties in MD (which each have a different rate). I would like to have the correct calculation apply once the appropriate county is selected from a drop-down list.

Furthermore, there are options such as a first time homebuyer tax rate which will apply if this option is selected.

I don't really know where to start with all of this and am wondering if Excel is the way to go?

Any help would be appreciated!


Post your answer or comment

comments powered by Disqus
Hello one and all!

First of all, forgive me if my explanations are extremely simplified. I am not assuming my fellow Boarders here lack intelligence or something; I do audits on work processes in my daily job (i.e. how to make them more efficient etc.), and I have learnt that pure clarity is the quickest and most effective way to get to an appropriate and effective solution.

So, here tis:

I have what appears (at least to me as a newbie) to be a complex problem in Excel.

I am doing a Timesheet which I hope will work out the Gross, the Tax rate, and the final 'Net' for each pay week.

I would like to calculate the Tax for each working days earnings and have it displayed onscreen, and then have it totaled for the whole pay week (which will also be displayed). But here in Australia it is not a simple matter of getting a Tax 'percentage' and then timsing the days gross by that (i.e. day's gross x .3 [for 30% Tax]).

In the Land of Oz, we have tax 'brackets':

If you earn: You pay:

BRACKET 1:

$0 - $6000:Nil tax

BRACKET 2:

$6001 - $21600: 17c for each $1 over $6000

BRACKET 3:

$21601 - $58000: $2652 plus 30c for each $1 over $21600

BRACKET 4:

$58001 - $70000: $13572 plus 42c for each $1 over $58000

BRACKET 5:

Over $70000: $18612 plus 47c for each $1 over $70000

The thing to note is that the way the Tax system here works, is that if you Gross $500 one week, then that amount is timsed by the 52 weeks of the year (i.e. which comes to $26000, placing one in 'Bracket 3'), and you are then taxed as if *that* will be your earnings for the whole year. BUT! if in the next week I gross $1200, then THAT amount is timsed by 52 weeks (i.e. $62400), and I now find myself in the higher tax bracket (i.e. Bracket 4) being taxed (for THAT week) as if I'm on 62 Grand a year (I wish!)

Theoretically, we get any excess back at the end of the Tax year... But I digress...

For example, here is the mathematical Tax formula for a gross of $500 in one week, which puts me into bracket number 3:

($500 * 52 = $26000) ($26000 - $21600 = $4400) ($4400 * .3 = $1320) ($1320 + $2652 = $3972) ($3972 / 52 = 76.384615384615384615384615384615)

Tax for this weeks Gross of $500: $76.38
_______________

I'm assuming that for this to work effectively in Excel I will need to do a Vlookup for starters... but I have one more dilemma:

Once again, I would like the Tax for each day of work to be displayed, but I can't get the Tax for the weeks gross and simply divide it by 5 business working days to get the Tax for a particular day, because sometimes I work 7.5 hours a day, and on others I will work up to 9 hours in a day.. and other days - for example - I might be unwell and not show up at all (which is rare I admit)!

And this, to me (as a relative excel 'newbie') seems to be the hardest part of all to grasp...

How do I get the individual Tax for each working day to display (as well as formulating and implementing all of the above)?

*sigh...

I would appreciate any help

Thank you all,

Mark
_________________________

I need a formula for cell D22 which calculates the tax amount, based on a
value in B6. The tax rules are: 0.5% of the dollar amount from $0.01 to
$55,000.00 PLUS 1.00% of the dollar amount from $55,000.01 to $250,000.00
PLUS 1.50% of the dollar amount from $250,000.01 to $400,000.00 PLUS 2.0% of
the dollar amount from $400,000.01 and up.

The values in B6 will be all over the map, and so far, I have not yet
figured out how to write a formula which ignores calculation of the higher
percentages, when using a value within a lower threshold, ie. if I use
$350,000, I would need the formula to calculate the 0.5%, the 1.00% and the
1.50%, but obviously not the 2.0%, since under the $400,000 threshold.

Anyone have any ideas or a snippet of formula syntax that would work?

able?Hi:

I am trying to modify the "Price quote with tqx calculation" template. We
have 2 sales taxes. The first tax is calculated as a % of the total sales+Tax
1. I need to show each tax separately, then total them.

The Quote template has a column "Taxable?" (Column E) The tax is calculated
only on the items with a T in Column E.

Cell F31 is Subtotal: =SUM(F22:F30)

Cell F32 is Tax Calculation 1: =7%*SUMIF(E22:E30,"T",F22;F30)

Now I need to calculate Tax 2, which is 7.5% of the (Subtotal + Tax1). i.e.
I need to add the Subtotal and Tax 1, multiply it by 7.5% and show that
result. But, like Tax 1, only for the items with a T in Column E.

I am trying the formula: =7.5%*SUMIF(E22:E30,"T",F31:F32)

but without success:

Any suggestions would be much appreciated.

Thank you.

--
Peter

Hello!

I am hoping someone can give me some insight on this problem. I am using
Excel 2002 and ACCPAC 6.1.

When I generate a report based on data in an ACCPAC report the numbers are
sometimes out by a few pennies.

My spreadsheet formulas are to calculate tax =E9*1.15 and =SUM(G8:G36).

I have tried the prescision as displayed but it still wont work properly!!!!

I am sure the problem is in the differences in which ACCPAc and Excel
calculate formulas but I don't know where to go from there.

Any feedback is appreciated.

Many thanks!

I am in field sales and would like to make a chart in Excel that would have a
range of dollar amounts for a 7% rate. In other words for a one cent tax,
cell A1 would have the minimum value and cell B1 would have the maximum
value. Then for a two cent tax, A2 would have the min. and B2 the max. I
would copy the formula into other cells to create a chart/table that I could
take into the field.

able?Hi:

I am trying to modify the "Price quote with tqx calculation" template. We
have 2 sales taxes. The first tax is calculated as a % of the total sales+Tax
1. I need to show each tax separately, then total them.

The Quote template has a column "Taxable?" (Column E) The tax is calculated
only on the items with a T in Column E.

Cell F31 is Subtotal: =SUM(F22:F30)

Cell F32 is Tax Calculation 1: =7%*SUMIF(E22:E30,"T",F22;F30)

Now I need to calculate Tax 2, which is 7.5% of the (Subtotal + Tax1). i.e.
I need to add the Subtotal and Tax 1, multiply it by 7.5% and show that
result. But, like Tax 1, only for the items with a T in Column E.

I am trying the formula: =7.5%*SUMIF(E22:E30,"T",F31:F32)

but without success:

Any suggestions would be much appreciated.

Thank you.

--
Peter

Hi

I wonder if you could help with a formula please - Im sure it will be pretty simple when I see how to do it, but Im having a complete mental blockage with it!

Im making an inheritance tax calculator, my problem is that when the total estate figure is less than the nil rate band, the IHT and Estate available figures are incorrect, Ive tried pasting in belwo but it hasnt come out very well

Inheritance Tax Calculator Column B Formula

Row 1 Gross Estate Value 525,000.00
Assets Exempt from IHT 0.00
Nil Rate Band Available 650,000.00
Net Estate Applicable for IHT -125,000.00 =a1-a2-a3
IHT Due - 50,000.00 =a4*40/100
Estate Available to Beneficiaries 575,000.00 =a1-5

Where the estate figure is higher than the nil rate band, it works fine but I am tying myself up in knots here, any assistance would be much appreciated!

Many thanks

Tab

Dear All,

Thanks a lot for all the help i got through this forum. I am facing a problem while calculating formula. I have to calculate some variables from two existing data sets i.e company wise data and Group wise data. My final data sheet includes calculation of new variables on the data from these two data sets. I am facing problem that i have to calculated variable on the basis of company level of information, and i have group code for all these companies. Is there any formula or VBA code which will help me in calculating the new variables (on the condition that When the Group Code is same).

Any suggestion will be highly appreciated.

With Sincere Regards,
Upananda

how do I delete a calculated formula created in the pivot table that is no longer needed?

******** ******************** ************************************************************************>Microsoft Excel - Fuzzy index match ( conditional index match formula).xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEFGHIJK1           2  CAB  ABC 3           4 John534 Fred    5 Fred273 John    6 Bruce186 Bruce    7           8           9           Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

In the above table i need in H4 to return the value of H2 in C2:E2 and matching G4 in B4:B6
and to copy the formula so far .

I appreciate your support, Thank you .

Hi

I was wondering whether there is a way in VBA to detect if a cell contains a value or calculation/formula?

Thanks
Peter

OK - I need a calculation to reference a range of cells rather than specific-case.

For instance, this calculation formula responds with concatenated values for column 'B' based on logical test from corresponding column 'D' values within a specific range .

I want the function to be simpler in that it would return concatenated values for column 'B' corresponding to rows where values in column 'D' are same over the entire range .

Any ideas?

=IF(D2=D4,B2&B3&B4,IF(AND(D2D4,D2=D3),B2&B3,B2))

I'm trying to conditional format to highlight a date cell if its in the
current week.

I know whow to use WEEKNUM to check a week's number (1-53). I did check to
make sure the Analyst Pack was loaded.

The challenge I have is getting conditional formatting using formulas to
check the current cell

I'm trying the following formula: (In Conditional Formatting)
="WEEKNUM($C$4,1) = WEEKNUM($A$1,1)" with no results
C4 is the current cell, A1 is a refernce cell with TODAY().

I would like to create a formula within an Excel 2000 Pivot Table. This is
the scenario:

Row contains Product
Column contains Region
Data contains Sum of Sales
There are automatic grand totals on the column and row

The formula I want to create will divide the total sales of each product
into the grand total sales figure to provide a % of sales. Using the
calculate formula, I can select the Sales field, but am not able to reference
the Grand Total for sales.

By researching on the Internet, I understand that there is a GETPIVOTDATA
funtion available, but it is only for Excel 2002 and higher.

Any suggestions? By the way, my data source will be via an ODBC driver to a
SQL database.
Christina
--
Christina

Hai. I need a hekp to solve this problem of condition formatting with
formula. Please refer to the cell:-
A B
1 50 4
2 100 3
3 60 10
4 80 7
5 92 5

If I want to put colour blue colour in A1 to A4 with the condition if B>6
If I want to put colour red colour in A1 to A4 with the condition if B>4
If I want to put colour blue colour in A1 to A4 with the condition if B>2

Thanks

When I asked the following on the 5th:-
Is there a way of permanently arranging in col. H that the result of the
amount in col. D plus amount in col.E less the amount in col.F is
automatically shown once the entries in cols. D, E & F have been made.
I was given the following formula by Max of Singapo-
=IF(COUNTBLANK(D2:F2)0,"",SUM(D2:E2)-F2)

My question now is:- how does one change the formula to accommodate other
calculation e.g what is the formula for the following:-H3 = H2 plus E3 minus
D3.
I have many other worksheets some of which contain many columns of figures
for which I would like to have a calculation formula.

Hi everyone,

I know this isn't the right forum to ask this question, but I hope to get
some responses from UK guys.

I would like to know if someone knows some good sites which would help me
calculate income tax for UK. Or if someone has a ready-made excel template
for UK income tax calculation could mail me directly.

Thanks in advance.

--
- Mangesh
------------------------------------------
Office 2003 & Windows XP

I am having a problem calculating formulas using goal seek. ANy help you can
give me would be appreciated

Hi all,

New to this forum and an utter newbie to excel!

I would like to make an Income Tax Calculator using with the sheet attached.

For example, a person making $20,000 a year would pay a total of $475 in taxes for that year

E.g. $175 for the first $10,000 and the next $10,000 would be charged at 3%.

I was wondering how I could do this where I can input the value in say cell A1, and it will spit out the tax chargeable on that income in say cell A2 in Excel 2007.

Many thanks

Hello Everyone,

I need help with a fluid calculation formula. I am using excel to calculate total IV fluids needed in a day, and have everything except the last portion I need. I have the numbers down to decimal place in the tenths for one second. What I need is a formula to calculate based on the tenths decimal value to get a whole number as close to one as possible. It may be simple but I'll give an example for clarification.

Daily maintenance of fluids is 66*weight in (kg). 22lb dog is 10(kg). 10*66=660 660/24=27.5=hourly. So 27.5 ml per hour is the rate. Now using a macro drip system is 15 drops per/mL. so 27.5/60=.45 ml/min .45/60 is .008 mL/sec. .008*15(drip system)=0.113 drops/second. it is impossible without a fluid pump to judge 0.113 drops per second. so what i need is a formula that will multiply the 0.113 up and get as close as possible to 1. the number used by the formula will be the number of seconds it takes to get one drop.

I have all of the other formulas worked so when i enter the animals weight in lbs it converts everything all the way down to drops per second. Any help would be GREATLY APPRECIATED.

Hi Guys,

I've got a report that I churn out every month that calculates total spend, average daily spend, median, number of people etc and as well as that I have to calculate formulas based on the top 20% and bottom 80% of the data. The attached sheet shows the number of people under different business areas, and costs in descending order.

At the moment I do it manually so I filter for each business area and work out the top 20% of people in range and then work out the number of employees, mean, median and SUM of the top 20% and bottom 80%. Below I have 20 people and I take the top 20% which is 4 and then I work out the Total number of people, SUM and median of the top 20 and I do the same for the bottom 80.

EXAMPLE:

TOP 20%
£1,330.00
£1,070.36
£997.50
£840.00

Employees - 4
SUM - £4,237.86
Median - £1,033.93

BOTTOM 80%
£825.00
£794.50
£785.00
£750.00
£748.00
£743.00
£728.68
£714.00
£700.00
£678.63
£655.00
£643.29
£622.50
£584.00
£577.50
£14.00

Employees - 16
SUM - £10,563.10
Median - £707.00

I'm just wondering if I can do this automatically using VBA. In my attached workbook I have the data on sheet 1 and the statistics in sheet 2, so I want to be able to run a macro and for the figures to come up in sheet 2, for the different business areas.

Is this possible.

At the moment it's taking up a lot of time but IfI could automate this, it would be great.

Thanks very much for your help.

Regards
Harry

I have a range of cells that I would like to either format directly, or use conditional format on.

All I need to do is apply the Number format with 2 decimal places any time a cell contains a decimal point (.) I have tried formatting, conditional formatting with formulas, with specific text, and I just cannot seem to get this to work.

Thank you in advance for your help.

Hi,
I am using this formula {=S3:S13} but the problem is if it does not find the value it returns 0. Is there a way to put condition in the formula not to show Zero? thanks

Hi,

I have a spreadsheet that has a column called script with values such a DL1, DL2 etc and another column called value with a number figure in it. What I would like to do is run a calculation formula where if the script value = DL1 then divide the value by 0.4 but if script = DL2 divide it by 0.32.

Does anyone know if there is a formula that will make this easy?


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