Subtracting and multiplying tax numbers witht he IF function

Ok here is what i have i have created a payroll. now i need to add this:

if pay is  $0 -  $95 deduct $0 plus   0c per dollar over 0
if pay is $96-$345 deduct $0 plus 20c per dollar over 96
if pay is $346-$480 deduct $63 plus 25c per dollar over 346
if pay is $481-$672 deduct $96 plus 40c per dollar over 481
if pay is $673-$961 deduct $183 plus 47c per dollar over 673
if pay is $962+ deduct $308 plus 48c per dollar over 962

And make it automatically deduct from the Gross Income.
I have tried this
=IF(C4>300,C4<400,(C4-43)*0.36,IF(C4>401,C4<500,(C4-60)*0.45,IF(C4>501,<600,(C4-200)*0.60)))

(numbers were just to try out)
and it didnt work.
How would i go about creating such a formula? or is there a different way of 
handling it?
0
Asta1 (8)
2/12/2005 10:43:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
444 Views

Similar Articles

[PageSpeed] 13

I made a table from your conditions as follows:

D1:D6 contains:  0, 95, 345, 480, 672, 961
E1:E6 contains:   0, 0, 63, 96, 183, 308
F1:F6 contains:   0,  0.2 0.25, 0.4, 0.47, 0.47

Then with the Pay in A1 enter the formula:

=A1-(VLOOKUP(A1,D1:F6,2)+(A1-VLOOKUP(A1,D1:F6,1))*VLOOKUP(A1,D1:F6,3))

in B1 to get the pay after tax.

HTH

Sandy


-- 
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Asta" <Asta@discussions.microsoft.com> wrote in message
news:294BA074-8E69-4171-ACBE-BF8160B6BEA8@microsoft.com...
> Ok here is what i have i have created a payroll. now i need to add this:
>
> if pay is  $0 -  $95 deduct $0 plus   0c per dollar over 0
> if pay is $96-$345 deduct $0 plus 20c per dollar over 96
> if pay is $346-$480 deduct $63 plus 25c per dollar over 346
> if pay is $481-$672 deduct $96 plus 40c per dollar over 481
> if pay is $673-$961 deduct $183 plus 47c per dollar over 673
> if pay is $962+ deduct $308 plus 48c per dollar over 962
>
> And make it automatically deduct from the Gross Income.
> I have tried this
>
=IF(C4>300,C4<400,(C4-43)*0.36,IF(C4>401,C4<500,(C4-60)*0.45,IF(C4>501,<600,
(C4-200)*0.60)))
>
> (numbers were just to try out)
> and it didnt work.
> How would i go about creating such a formula? or is there a different way
of
> handling it?


0
sandymann (252)
2/12/2005 11:38:31 PM
Take a look here:

    http://www.mcgimpsey.com/excel/variablerate.html

In article <294BA074-8E69-4171-ACBE-BF8160B6BEA8@microsoft.com>,
 "Asta" <Asta@discussions.microsoft.com> wrote:

> Ok here is what i have i have created a payroll. now i need to add this:
> 
> if pay is  $0 -  $95 deduct $0 plus   0c per dollar over 0
> if pay is $96-$345 deduct $0 plus 20c per dollar over 96
> if pay is $346-$480 deduct $63 plus 25c per dollar over 346
> if pay is $481-$672 deduct $96 plus 40c per dollar over 481
> if pay is $673-$961 deduct $183 plus 47c per dollar over 673
> if pay is $962+ deduct $308 plus 48c per dollar over 962
> 
> And make it automatically deduct from the Gross Income.
> I have tried this 
> =IF(C4>300,C4<400,(C4-43)*0.36,IF(C4>401,C4<500,(C4-60)*0.45,IF(C4>501,<600,(C
> 4-200)*0.60)))
> 
> (numbers were just to try out)
> and it didnt work.
> How would i go about creating such a formula? or is there a different way of 
> handling it?
0
jemcgimpsey (6723)
2/13/2005 5:04:26 AM
Reply:

Similar Artilces:

Page Numbers in Publisher 2000
I am completing our Employee Handbook and do not want page umbers on my first 9 pages. Page 10 is the first one I want numbered beginning with page #1. Instead my page #10 is labeled page #1. Can anyone help and let me know if this is possible with this software? Thanks. -- Kay -- Kay Correction "Kay" wrote: > I am completing our Employee Handbook and do not want page numbers on my first 9 pages. Page 10 is the first one I want numbered beginning with page #1. > Instead my page #10 is labeled page #10. Can anyone help and let me know if > this is possibl...

sequential invoice numbering
How do I formulate a cell to add one to the previous invoice# each time the "xxxx" invoice file is opened. Please and thank you Scott You could add code to the workbook_open event, thus (to add this open the VBE (Alt+F11) and right click on the ThisWorkbook entry for the file name. Paste the code in the resultant window, close the VBE and save the workbook. (Change the references accordingly) Private Sub Workbook_Open() Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value + 1 End Sub -- HTH Nick Hodge Microso...

two columns range of numbers need to list all numbers in the range
have two columns range of numbers i need to list each number in the range start end 5 9 15 19 20 29 i need for each row to show the numbers in the range 5 6 7 8 9 15 16 .. .. pls help Was this post helpful to you? Check your other post. arsovat wrote: > > have two columns range of numbers i need to list each number in the range > start end > 5 9 > 15 19 > 20 29 > i need for each row to show the numbers in the range > 5 > 6 > 7 > 8 > 9 > 15 > 16 > . > . > pls help > > Was this post he...

Formula for current month minus one = Quarter number in a macro.
I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quar...

seach function in Outlook XP
Hi, Used software: Windows XP (Dutch) or w2k (Dutch and English), Outlook XP(Dutch), on an Exchangeserver (2002 ?). When using the search function in Outlook XP there is no result, whether searching on subject, sender or mail text. There is no result, all mail disappears. Only by leaving the search field blank all mail shows up again. The problem appears both with w2k and xp operating systems (English and Dutch), but only using office xp (Dutch). Outlook98 worked OK on the same server! By using webmail the problem does not exist. (The improved search options should be on of the main advant...

Books or websites for Functions
can anyone give me the name/names of books or websites that have a GOOD set of examples and descriptions of functions. The ones with MS excell are just vague enough to help. This website is a help, but I can't always take it with me. If I could print out info and examples to help me understand the construction of functions, I might be a better earthling, i mean person. -- humanoid/earthling Hi jackle I have a file from Peter Noneley on my site(bottom of the page) See also Norman's file and my ID file http://www.rondebruin.nl/id.htm -- Regards Ron de Bruin http://www.rondebru...

How to change left bottom note that tell number records found?
Hi, At the left bottom corner of Exel 2003 spreadsheet, a display note 'Ready' remains as it is, though I have filtered the items using the filter command, the note usually displays number of records found. How to return back the display note 'number of records found' in Excel 2003 spreadsheet? -- Thank you, Cpviv If you have any formulas that read these filtered records, you will typically not be able to view the # of records found. If you go to Tool - Options - Calculation and change to Manual, you would be able to view # of records. Beware, as this will not au...

Strage function in worksheet {=TABLE(I64,)}
I am attempting to understand what appears to be a custom function in a workbook I was given, author unknown. It seems to be some type of index or look up but their is no help on it or macro defined for it. has anyone used or familiar with this? If you need more information I would be happy to provide it. {=TABLE(I64,)} Thanks, Mark On Thu, 26 Feb 2004 19:30:25 -0600, "mkadon" <mkadon@hotmail.com> wrote: >I am attempting to understand what appears to be a custom function in a >workbook I was given, author unknown. It seems to be some type of index or >look up...

Unsubscribe Button Not Functioning
When distributing an email activity in CRM, I am unable to insert an "unsubscribe" option. The button is available, but when I click on it nothing happens and nothing is inserted into the email that I distribute. Thanks, Tony You have to select some text such as "click here to unsubscribe" and hightlight the word "here" and then click unsubscribe. That will make the selected text a hyperlink and it should work, but for me it does not. See my post on this same subject. "TonyLI" wrote: > When distributing an email activity in CRM, I am unabl...

can you count the number of letters (inc spaces) in a single sell
I have a spreedsheet which has 3000 rows. Each cell has a different amount of words in it... ie ABC Learning Centre Ferrymead. Is it possible to count the number of characters (letters) including spaces in each cell??? (please note each cell has a different number of letters in it)... or do I have to do this manually? none of the help options seem to help me... Thanks use the formula =len( -- PF Wannab ----------------------------------------------------------------------- PF Wannabe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2579 View this thread: ...

Sort numbers alphabetically
Let's say I have a list: 1 2 5 20 35 I want it alphabetical, ie: 1 2 20 35 5 Even if I make them text, they still sort numerically. Is there some trick? Thanks, Joe One method................ In B1 enter =LEFT(A1) Copy down. Copy and paste special>values. Select column A and B and sort on column B Gord Dibben MS Excel MVP On Tue, 26 Aug 2008 14:19:10 -0700 (PDT), Pungh0Li0 <joe.dellwo@gmail.com> wrote: >Let's say I have a list: >1 >2 >5 >20 >35 > >I want it alphabetical, ie: >1 >2 >20 >35 >5 > >Even if I make them text...

convert month text (MAR) to month number (3)
I am trying to work with a data set that unfortunately has spit out all the dates in a text format - i.e. 03/01/2009 is MAR 3 2009. Is there a way to convert that text date to an actual date format? Any advice or suggestions are greatly appreciated. Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1 Debug.Print CDate("MAR 3 2009") = 3/3/2009 One problem with the CDate function is that it will bomb out on things that can't be evaluated as a date. Therefore you may want to use the IsDate fun...

Count 350 SS numbers, exclude duplicates
I need to be able to count information based on approximately 350 social security numbers, exlcuding duplicates. Any ideas on the formula? Thanks so much, Marsha Hi Marsha, One way =SUMPRODUCT((A1:A200<>"")/COUNTIF(A1:A200,A1:A200&"")) -- HTH RP (remove nothere from the email address if mailing direct) "Marsha" <Marsha@discussions.microsoft.com> wrote in message news:E362D9A0-8A06-43E4-81DD-0B2F28C6C18E@microsoft.com... > I need to be able to count information based on approximately 350 social > security numbers, exlcuding duplic...

Word 2007
In Word 2003 you can define a new number format list with Number style (None). It is not possible in Word 2007 because the OK button is not activated when (None) is choosen. To create a list like this is useful if you want to repeat a text on every line in a list without a number. My work around for this time was to do a list in Word 2003 version and then copy it into Word 2007. I look forward to get an answere from someone out there. Best regards, Lisa It is true that you can't do this for simple lists in Word 2007, but you can do it for multilevel lists; see h...

Changing the order of page numbers
Example: On a 10 page doc. you want the first 5 pages to be numbered '1' to '5' then the next 5 pages to be numbered '14' to '18' Is there a way of changing page numbers like the above example? Thanks! Ben Ben Don't understand why you want to do this but the best way is to record a macro while you do it manually. 1. print pages 1 to 5 2. go into page setup and change the first page from auto to the value 9 3. print pages 6 to 10. The page count should start at 14. 4 go into page set up and change the first page back to auto. you'll get so...

#DIV/O! Errror with Absolute Number Calculation
Hi, I am calculating the following formula. =ABS(K41-J41)/ABS(K41) The result is #DIV/O! many times because K41 is 0 and the result is #DIV/O! I'd like the result to be "N/A" or something that I could put in between parenthesis. I'd be happy with any recommendations you have. Thanks!! Try this... =IF(K41="","N/A",ABS(K41-J41)/ABS(K41)) -- Biff Microsoft Excel MVP "Brent" <Brent@discussions.microsoft.com> wrote in message news:93A3C05A-89EC-4229-BA99-C23EF2E24AA8@microsoft.com... > Hi, > > I...

How do I apply State O Maine 5% Sales Tax schedule to $ amounts.
Maine 5% Sales Tax Schedule From To Tax $0.00 $0.10 $0.00 $0.11 $0.20 $0.01 $0.21 $0.40 $0.02 $0.41 $0.60 $0.03 $0.61 $0.80 $0.04 $0.81 $1.00 $0.05 If you normaly multiple 10 cents by .05 it equals $0.01.... That would be wrong under their tax schedule. Can anybody help me? Thank you Something like this. For the Case Else section, adjust the formula as needed. Function MainTax(myCost As Double) Select Case myCost Case Is <= ...

Who can explain this code for me?about virtual function
/////////////////////////////////////////////// // CIOMessageMap.h class __declspec(novtable) CIOMessageMap { public: virtual bool ProcessIOMessage(IOType clientIO, ClientContext* pContext, DWORD dwSize) = 0; }; #define BEGIN_IO_MSG_MAP() \ public: \ bool ProcessIOMessage(IOType clientIO, ClientContext* pContext, DWORD dwSize = 0) \ { \ bool bRet = false; #define IO_MESSAGE_HANDLER(msg, func) \ if (msg == clientIO) \ bRet = func(pContext, dwSize); #define END_IO_MSG_MAP() \ return bRet; \ } Why class CIOMessageMap should be declared by declspec(novtable)? The macros implements the member ...

Is there a cell format for HEX numbers?
Hello. Is there a way to format cells for HEX numbers? I currently have to format them as text. I would like to be able to automatically increment in HEX, as I can with general numbers or dates. Is this possible without writing a macro? Betty wrote: >Hello. > >Is there a way to format cells for HEX numbers? I currently have to format >them as text. I would like to be able to automatically increment in HEX, as I >can with general numbers or dates. Is this possible without writing a macro? > > To display a decimal number as hex use: [ ] = DEC2HEX(A1,n) If it...

IF Function #4
in the IF function is there anyway that i can have the 'if true' section of the formula copy the values in another workbook in specific rows. In other words, =IF(ISNA(MATCH($A5,[SDS.xls]NOW!$L$3,0)), IF TRUE then copy cells n8:n12 and n14:n15 in workbook SDS to cell b5:g5 in workbook wkly mgt? Brigida no. sorry. formulas return a value, they cannot perform an action like copy. it may be possible to use a macro to do that but without seeing your data and knowing more about, i can only guess. >-----Original Message----- >in the IF function is there anyway that i can have ...

Reorder number in Item Wizard
The RMS item wizerd seems to be lacking the reorder number field. It is important for bullding a new matrix item. The problem is that when you generate a PO for reorder purposes, there is no reorder number available. Populating the reorder number manually for matirx items is just not an option. Or, Is there another way? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the...

Text Functions
I'm trying to split a text string ... Apart from it not working quite right yet! I'm sure I've over complicated it. Base Data "Path": \\SERVER\X$\data\Team Name\Personal Data\JOE BLOGGS\mail\ \\SERVER\X$\ \\SERVER\X$\RECYCLER\ \\SERVER\X$\$Extend\ \\SERVER\X$\$Extend\Test I'm trying to split into High Level Directory (HLD) / Team/User Directory (TUD) / Team/User Sub-Directory HLD: =IFERROR(SUBSTITUTE(LEFT(Path,(SEARCH("\",Path,(SEARCH("\",Path,(SEARCH("X$",Path,1))+3))+1))),"\\SERVER\X$\",""),SUBS...

does not recognize numbers
When I copy a table from Schwab and paste it into Excel 2000 it looks fine. However, the numbers cannot be used in any calculation. I can't format the data as numbers (eg I can't change the number of decimal places, can't make it currency etc) but I can change the font and other aspects. I can't add any cells. They seem to have a numerical value of zero. I can copy and paste to other cells but it still does not work like a number. It shows up in the cell and the formula line like normal numbers. If I put in new numerical data, that data works fine. I tried to ...

Publisher 2002 not showing page numbers
Hi all, I have just installed Publisher 2002 after drive crash When I open Publisher and select a blank document, in this case a tent fold greeting card, only the first page is accessible. There are no page numbers to click to go to the 2nd, 3rd & 4th page. Have searched around Toolbars, Options etc and can't seem to find anywhere to enable this feature. This is the only version of Publisher I have ever seen do this straight out of the box? Can anyone suggest where I might go from here? Thanks In Advance -- Roland Fitzthum Lidoroll Pty Limited t/a Cooma Computer Consultants ...

get slide number to start at 1 on slide 4
i need to number my samples so i'm using the slide number as a sample number. problem is my samples start after my index on pg 4 and i need my numbering to start from 1? what can i do "Belinda" <Belinda@discussions.microsoft.com> wrote in message news:B4C58782-7866-438A-A393-45C29BEC6A26@microsoft.com... > i need to number my samples so i'm using the slide number as a sample > number. > problem is my samples start after my index on pg 4 and i need my numbering > to > start from 1? > > what can i do Just drag the slides to th...