My Texas Instruments calculator has a tax minus (tax-) key that I have used extensively in my business. I am a brand new Excel user and would like to know if there is a "formula" or "equation" that I can use to duplicate this on Excel.

thanks,

Winston

thanks,

Winston

- Minus Function?
- Date Functions
- Plus/Minus Function
- Problem with minus function
- Utilizing iteration in conjunction with Excel function
- Distance change , plus or minus , function please for col E
- How to subtract value in one cell from aanother cell value?
- #Value issue
- Tax help using vlookup and if function
- Need a function to calculate a mortgage payment
- Sliding Tax Function
- RE: need a function to calculate a mortgage payment
- Sales Tax Calculation
- NETWORKDAYS function minus 1 day
- Calculating Retail Price And Sales Tax Using The Round Function
- Application.Min function
- Tax deduction formula
- Date and % with IF function
- Sum If nested if function to return tax
- Creating A Tax Calculator In Vba
- Function, Loop and Sum
- Pay As You Go (payg) Withholding Tax Formula
- Sum function but for Subtraction
- Tax Year Date Splitting

Thanks

If we calculate the difference between 2 dates (the normal minus function), excel gives us the exact number of days between 2 dates. By any function can we know the years, months and days separately like we manually do. Means between 10/01/1998 to 10/02/2002 can excel give 4 years, 1 month and 10 days??

Example:

Cell A1 is 3, Cell A2 is 3.2, I want Cell A3 to turn red (or report "1")

Cell A1 is 3, Cell A2 is 3.1, I want Cell A3 to turn green (or report "0")

I'm hoping this is an easy one.

Thanks,

Tim

Would you please help me on this matter? Thanks. I also attached the file for your reference.

2 1990-12-10 John 1200

3 1990-12-11 Paul 1400

4 1990-12-12 George 2100

5 1990-12-13 Ringo 1600

6 1990-12-14 George 2100

7 1990-12-15 Paul 1200

8 1990-12-16 Ringo 1400

9 1990-12-17 John 1400

10 1990-12-18 John 1600

11 1990-12-26 Ringo 2000

12 1990-12-26 George 3200

13 1990-12-26 Paul 1100

14 1990-12-30 John 2000

15 1991-12-30 John 1800

16 1992-12-26 Ringo 1850

17 1993-06-26 Ringo 2100

one total cell from the other total cell. I don't find any reference to

subtract or minus functions in Excel. HELP

A B C

ticker x ticker y ticker y - ticker x

1 #N/A N/A #N/A N/A #VALUE! (#N/A N/A)

2 93.707 #N/A N/A #VALUE! (93.707)

3 #N/A N/A 790 #VALUE! (-790)

4 93.375 790.5 -697.125

5 93.707 795.013 -701.306

6 92.504 803.085 -710.581

The problem is with cells C1, C2 , and C3.

I need to do some further analysis with the data in column C, but because it returns as a #Value, I can't do them. I need the data in Column C to either be numerical or #N/A N/A. I know that if I use =SUM(A2,B2) my data in C3 will return the value in A2 (93.707), but i need to do a subtraction. There is no DIFFERENCE or MINUS function (similar to SUM) that I'm aware of and I can't multiply column A or B by -1 as this will still return #Value as an answer in Column C. any ideas on how i can achieve the results i'm looking for? i've put the answer in parenthesis for waht i'm trying to achieve.

Thanks

dw

Here's the outline. I have been told to use this to calculate tax: When calculating the tax, assume that the monthly gross profit taxed @ 20% for the first $20,000 then 30% for the remainder of gross profit.

The monthly gross profit is $20,153 (C14) and the tax table (table7) is below:

Taxable Income Fixed Tax Marginal Tax Rate 0 0 0.2 20000 3999.8 0.3

I don't know how to calculate the 20% of the first $20,000.

This is what I have done for the fixed rate: VLOOKUP(C14,Table7,2,TRUE) which gave me 3999.8

and for the 30% of the remainder ($153) I did: (C14-VLOOKUP(C14,Table7,1,TRUE))*VLOOKUP(C14,Table7,3,TRUE) which gave me 45.90

Is there a simpler way to do all this? And can IF function be used along with VLOOKUP at all? Thanks in advance

Faye

Additional info: I also need to allow the formula to show an operational loss (i.e. profit < $0), what does that mean?

Note: All formulae must contain VLOOKUP.

you've set the number of payments (NPER) to the number of months, you need to

adjust your interest rate accordingly (eg: not 6%, but 6%/12). You'll also

want to put a minus sign on the PV argument. Ex: a 30 year loan at 6.5% for

$145000: =PMT(6.5%/12,360,-145000). If you can't get the function to behave

as you expect, post the terms and the formula you're using. (Also, remember

that your total payment will probably include taxes and insurance, which

won't be captured in this formula).

--Bruce

"JSEVonda" wrote:

> Does anyone have a function to calculate a amortized mtg payment. I tried to

> use the pmt function, and it came out all wrong. Have tried many different

> iterations of the numbers, and they still do not come out to be same as my

> Loan Officer Point software indicates the answer should be.

>

> I need to put this function calulation in an excel spreadsheet.

>

> Thanks

Tax rate is:

Upto 110000 > 0%

More than 110000 but less than 150000 > 10%

More than 150000 but less than 250000 > 4000 + 20%

Above 250000 > 24000 + 30%

For checking I am providing some solution:

Income Tax Payable

110000 > 0

130000 > 2000

150000 > 4000

160000 > 6000

250000 > 24000

490000 > 96000

The function is:

VB:Single) Select Case income Case Is

you've set the number of payments (NPER) to the number of months, you need to

adjust your interest rate accordingly (eg: not 6%, but 6%/12). You'll also

want to put a minus sign on the PV argument. Ex: a 30 year loan at 6.5% for

$145000: =PMT(6.5%/12,360,-145000). If you can't get the function to behave

as you expect, post the terms and the formula you're using. (Also, remember

that your total payment will probably include taxes and insurance, which

won't be captured in this formula).

--Bruce

"JSEVonda" wrote:

> Does anyone have a function to calculate a amortized mtg payment. I tried to

> use the pmt function, and it came out all wrong. Have tried many different

> iterations of the numbers, and they still do not come out to be same as my

> Loan Officer Point software indicates the answer should be.

>

> I need to put this function calulation in an excel spreadsheet.

>

> Thanks

What function or calculation could I use in a spreadsheet to calculate the

price of an item minus the local sales tax so that in the end the final

price would be a whole number. I would like to enter the local tax rate in

percent so I could figure out what the price for an item should be so in the

end the final price is $5, $10, $15 etc. Using Excel 2000.

Thanks,

Linda

I am currently working on a project that requires me to get the total number of business days between two dates. I have used the NETWORKDAYS function and it works great. However, I need a formula (if their is one) that will give me the total number of business days between the dates and either A) minus 1 from the total or B) not include the first day as an actual work day.

example.

Start date - 3/20/12

End date - 3/23/12

Total number of business days minus 1 = 3

I can do this if i use the function, copy and paste special and then create another row next to the total and do a =A1-1 all the way down but was just wondering if their was an easier way.

Any help with this would be much appreciated.

Calculating the retail price and sales tax (7.75 percent) matching each total price in column A.

Solution:

Using the ROUND function, as follows:

To calculate Retail Price:

=ROUND(A2/(1+7.75%),2)

To calculate Sales Tax:

=ROUND(7.75%*B2,2)

this is not working correctly so realize I have a flaw in my logic any help would be appreciated . I have been thinking of using the application.worsheet.min function but I don't really understand how to set that up.

VB:Sub NewCalculation2() Dim ws As Worksheet 'First Loop defines high low values '21710 Changed R1 from long to Double because Loop is marking everything green fixed problem Dim R1 As Double 'Second Loop Dim R2 As Double Dim LowM2, LowM1, LowP1, LowP2 As Double Dim HighM2, HighM1, HighP1, HighP2 As Double Dim MarkedRed As Double ' Tracker for column H Dim MarkedGreen As Double ' Tracker for column G Dim SR As Long 'LR is Last Row Dim LR As Long 'Dim LastRow1 as Double Dim LR1 As Double Dim BUY1 As Double Dim BUY2 As Double Dim BUY3 As Double Dim BUY4 As Double Dim SELL1 As Double Dim SELL2 As Double Dim SELL3 As Double Dim SELL4 As Double Dim STOP1 As Double Dim STOP2 As Double Dim STOP3 As Double Dim STOP4 As Double 'Last Row first loop Dim LRL1 'Last Row second loop Dim LRL2 Dim R3 As Long 'R4 is range4 Dim R4 As String 'Low Calculation minus 1 cell Dim ShName As String Dim CellRef As Range Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False Call BulkQuotesXL.UpdateData 'Start update format For Each ws In Worksheets With ws .Activate Range("A3").Select 'Calculate Last Row LR = ActiveSheet.UsedRange.Rows.Count Select Case .Name Case "BulkQuotesXL Settings" Case Else 'Freeze Panes Rows("2:2").Select ActiveWindow.FreezePanes = True ' Setup Worksheet BUY1 = 1 BUY2 = 1.236 BUY3 = 1.382 BUY4 = 1.5 SELL1 = 0.618 SELL2 = 0.786 SELL3 = 1 SELL4 = 1.236 STOP1 = 1.2 STOP2 = 1.3 STOP3 = 1.4 STOP4 = 1.6 Range("G1").Value = "High" 'B leg High header Range("H1").Value = "Low" 'A leg Low header Range("I1").Value = "Diff" 'b-a or a-b difference header Range("J1").Value = "Buy @ " & BUY1 'Buy1 output Range("K1").Value = "Stop @ " & STOP1 ' Stop1 Output Range("L1").Value = "Buy @ " & BUY2 'Buy2 output Range("M1").Value = "Stop @ " & STOP2 'Stop2 Output Range("N1").Value = "Buy @ " & BUY3 ' Buy3 output Range("O1").Value = "Stop @ " & STOP3 'Final output Range("P1").Value = "Buy @ " & BUY4 ' Buy4 output Range("Q1").Value = "Stop @ " & STOP4 'Stop4 output Range("R1").Value = "Sell @ " & SELL1 'Final output Range("S1").Value = "Sell @ " & SELL2 'Final output Range("T1").Value = "Sell @ " & SELL3 'Final output Range("U1").Value = "Sell @ " & SELL4 'Final output ' Ini Variables & Fill Static Vars. MarkedRed = 0: MarkedGreen = 0 '** START - Main Routine ** For R1 = 2 To LR ' 'Min Value Calculation // A leg Low location If R1 = 2 Then LowM2 = (Range("D" & R1) - Range("D" & R1 + 2)) If R1 = 2 Then LowM1 = (Range("D" & R1) - Range("D" & R1 + 1)) If R1 = 3 Then LowM2 = (Range("D" & R1) - Range("D" & R1 + 2)) If R1 = 3 Then LowM1 = (Range("D" & R1) - Range("D" & R1 + 1)) If R1 > 3 Then LowM2 = (Range("D" & R1) - Range("D" & R1 - 2)) If R1 > 3 Then LowM1 = (Range("D" & R1) - Range("D" & R1 - 1)) LowP1 = Range("D" & R1 + 1) - Range("D" & R1) LowP2 = Range("D" & R1 + 2) - Range("D" & R1) ' 'Max Value Calculation // B leg High location If R1 = 3 Then HighM2 = (Range("C" & R1) - Range("C" & R1 + 2)) If R1 = 3 Then HighM1 = (Range("C" & R1) - Range("C" & R1 + 1)) If R1 > 3 Then HighM2 = (Range("C" & R1) - Range("C" & R1 - 2)) If R1 > 3 Then HighM1 = (Range("C" & R1) - Range("C" & R1 - 1)) If R1 > 3 Then HighP1 = Range("C" & R1 + 1) - Range("C" & R1) If R1 > 3 Then HighP2 = Range("C" & R1 + 2) - Range("C" & R1) ' ' Leg LOW validator ' 'Setting up buy signal If LowM2 < -0.15 And LowM1 < 0.08 And LowP1 > 0.05 And LowP2 > -0.1 Then Range("H" & R1).Value = (Range("D" & R1)) Range("D" & R1).Interior.Color = vbRed ' ' record cell value for later calculations MarkedRed = Range("H" & R1) ' ' check other tracking marker to see if ' Sell calcuations are to be done at this time If MarkedRed 0 Then ' ' Put answer in Column I. ElseIf MarkedGreen = 0 Then End If End If ' ' Leg HIGH validator ' If HighM2 >= -0.2 And HighM1 >= -0.05 And HighP1 < -0.2 And HighP2 < -0.05 Then Range("G" & R1).Value = (Range("C" & R1)) Range("C" & R1).Interior.Color = vbGreen ' record cell value for later calculations MarkedGreen = Range("C" & R1) ' check other tracking marker to see if ' Buy calcuations are to be done at this time If MarkedRed 0 Then ' ' Put answer in Column I. Range("I" & R1).Formula = (MarkedGreen - MarkedRed) Range("J" & R1).Formula = (((MarkedGreen - (Range("I" & R1).Value) * BUY1))) 'First Buy Point Range("K" & R1).Formula = ((MarkedGreen - (Range("I" & R1).Value) * STOP1)) 'New Formula First Stop Range("L" & R1).Formula = (((MarkedGreen - (Range("I" & R1).Value) * BUY2))) 'Second Buy Point Range("M" & R1).Formula = ((MarkedGreen - (Range("I" & R1).Value) * STOP2)) 'New Formula Second Stop Range("O" & R1).Formula = ((MarkedGreen - (Range("I" & R1).Value) * STOP3)) 'New Formula Third Stop Range("P" & R1).Formula = (((MarkedGreen - (Range("I" & R1).Value) * BUY4))) 'Third Buy Point Range("Q" & R1).Formula = ((MarkedGreen - (Range("I" & R1).Value) * STOP4)) 'New Formula Third Stop Range("R" & R1).Formula = (MarkedRed + (Range("I" & R1).Value) * SELL1) 'First Sell point Range("S" & R1).Formula = (MarkedRed + (Range("I" & R1).Value) * SELL2) 'Second Sell point Range("T" & R1).Formula = (MarkedRed + (Range("I" & R1).Value) * SELL3) 'Third Sell point Range("U" & R1).Formula = (MarkedRed + (Range("I" & R1).Value) * SELL4) 'Fourth Sell point ' ' RESET trackers for next group of numbers (if any) MarkedRed = 0: MarkedGreen = 0 End If End If Next R1 '' ** END - Main Routine**' 'MsgBox "Differential Calculations Are Done ", vbOKOnly ' Columns("V").Select Range("V1").Value = ("Prev Close-High") Range("V3" & ":" & "V" & LR - 1).Formula = "=(C3-E2)/E2" Columns("W").Select Range("W1").Value = ("Prev Close-Low") Range("W3" & ":" & "W" & LR - 1).Formula = "=(D3-E2)/E2" Columns("X").Select Range("X1").Value = ("Prev Close-Open") Range("X3" & ":" & "X" & LR - 1).Formula = "=(B3-E2)/E2" Columns("Y").Select Range("Y1").Value = ("Day High to Day Low") Range("Y3" & ":" & "Y" & LR - 1).Formula = "=(C2-D2)/D2" Columns("Z").Select Range("Z1").Value = ("Prev High-High") Range("Z3" & ":" & "Z" & LR - 1).Formula = "=(C3-C2)/C2" Columns("AA").Select Range("AA1").Value = ("Prev Low-Low") Range("AA3" & ":" & "AA" & LR - 1).Formula = "=(D3-D2)/D2" '20 Day Moving Average Range("AB1").Value = "20 day Average" Range("AB20:AB" & (LR - 1)).Formula = "=SUM(E1:E20)/20" '50 Day Moving Average Range("AC1").Value = "50 day Average" Range("AC50:AC" & (LR - 1)).Formula = "=SUM(E1:E50)/50" '100 Day Moving Average Range("AD1").Value = "100 day Average" Range("AD100:AD" & (LR - 1)).Formula = "=SUM(E1:E100)/100" '150 Day Moving Average Range("AE1").Value = "150 day Average" Range("AE150:AE" & (LR - 1)).Formula = "=SUM(E1:E150)/150" '200 Day Moving Average Range("AF1").Value = "200 day Average" Range("AF200:AF" & (LR - 1)).Formula = "=SUM(E1:E200)/200" End Select Select Case .Name Case "BulkQuotesXL Settings" Columns("B:C").Select With Selection .NumberFormat = "mm/dd/yyyy" End With Columns("F").Select With Selection .NumberFormat = "text" End With Columns("J").Select With Selection .Columns.Autofit .VerticalAlignment = xlCenter .HorizontalAlignment = xlRight End With Columns("K").Select With Selection .Columns.Autofit .VerticalAlignment = xlCenter .HorizontalAlignment = xlRight End With Range("A3").Select Case Else Rows("2:2").Select ActiveWindow.FreezePanes = True Columns("A").Select With Selection .NumberFormat = "mm/dd/yyyy" End With Columns("B:E").Select With Selection .NumberFormat = "0.00" End With Columns("F").Select With Selection .NumberFormat = "#,##0" End With Columns("G:U").Select With Selection .NumberFormat = "0.00" End With Columns("A:U").Select With Selection .Columns.Autofit .VerticalAlignment = xlCenter .HorizontalAlignment = xlRight End With Columns("V:AA").Select With Selection .NumberFormat = "0.00%" End With Columns("AB:AF").Select With Selection .NumberFormat = "0.00" .Columns.Autofit .VerticalAlignment = xlCenter .HorizontalAlignment = xlRight End With R3 = ActiveSheet.UsedRange.Rows.Count R4 = "F" & R3 If R3 > 21 Then SR = R3 - 20 If R1 > 15 Then SR = R1 - 14 If R1 > 10 Then SR = R1 - 8 If R3

I am trying to show the status of the task completion by using IF function. I have the focus and actual columns in my worksheet. The focus is formatted as date, and the actual is date or percentage (if it is date or 100% then mean that the task is completed). The result which i am looking for is as below:

- If Focus is blank then it would show nothing in the Status

- If Actual is 100% or any date then it would show DONE the Status

- If Focus is smaller than today date then it would show OVERDUE in the Status

- If Focus minus today date is smaller than 8 then it would show TO FINISH IN 7 DAYS in the Status

The enclosed attachment is my sample worksheet. Please kindly advise.

A similar question was post http://www.excelforum.com/excel-gene...ml#post2526109

Sorry for any inconvenience.

Thanks,

sanlen

please see attached example to oz final

i cant work this out!!!!!

1. row 17 needs to: equal row 3 until row 3 hits a value>0

2. following the first entry in row 17 the next cell must equal the previous cell minus the cell below that one eg: f17=E17-E18

3. this all needs to be in one formula!

I'll get straight to the point. I have a n number of rows with 3 columns: Date, Tax, Value. My objective is to have my code loop throught the rows and sum a monthly amount of tax collected. I need to do this for the entire year. Now I don't think rewritting the loop 12 times is the most efficient way of doing this. Also, I need to have the count and the sum reset after the loop is done. Is there a simpler way of doing this? I'm thinking this coudl be done with a function and some defined variables at the beginning, but i'm not sure.

Thanks.

Here is the code...

Sub Monthly_Sum()

Dim row, count As Integer

Dim sum As Long

Worksheets("Monthly").Activate

row = 2

count = 0

sum = 0

'April 2010

Do Until Cells(row, 2) = 20100501

count = count + 1

sum = sum + Cells(row, 4)

row = row + 1

Loop

Cells(8, 8) = row

Cells(6, 8) = count

Cells(7, 8) = sum

'May 2010

count = 0

sum = 0

Do Until Cells(row, 2) = 20100601

count = count + 1

sum = sum + Cells(row, 4)

row = row + 1

Loop

Cells(8, 9) = row

Cells(6, 9) = count

Cells(7, 9) = sum

As the new financial year is approaching, the ATO has released another PAYG tax table, for payments made on or after 1 July 2008.

The code below will calculate the weekly withholding amount for Scale 2 (Where payee is eligible to receive leave loading and has claimed tax-free threshold Scale 2)

You may easily edit the figures below to suite your required tax scale

http://www.ato.gov.au/content/downlo...1n10040508.pdf

Simply paste the following code into a module and whenever you need to use the formula simply type:

=tax(A1) - Where A1 is the cell that contains the weekly Gross wages

VB:tax(gross) 'A and B are co-efficients which should be changed according to your tax Scale 'Below they are set to Scale 2 Where payee is eligible to receive leave loading 'and has claimed tax-free threshold 'For more info on the tax formula visit: ' http://www.ato.gov.au/content/downloads/BUS00138601n10040508.pdf Dim a Dim b 'Add 0.0001 to gross to round to nearest dollar if tax = $X.50 cents exactly, as required by the ATO gross = gross + 0.0001 x = CInt(gross) 'Calculate Tax If x < 186 Then a = 0 b = 0 y = (a * (x + 0.99)) - (b) tax = CInt(y) ElseIf x < 329 Then a = 0.1514 b = 28.2692 y = (a * (x + 0.99)) - (b) tax = CInt(y) ElseIf x < 387 Then a = 0.2524 b = 61.5554 y = (a * (x + 0.99)) - (b) tax = CInt(y) ElseIf x < 571 Then a = 0.1666 b = 28.2697 y = (a * (x + 0.99)) - (b) tax = CInt(y) ElseIf x < 647 Then a = 0.1868 b = 39.8081 y = (a * (x + 0.99)) - (b) tax = CInt(y) ElseIf x < 1147 Then a = 0.335 b = 135.8235 y = (a * (x + 0.99)) - (b) tax = CInt(y) ElseIf x < 1532 Then a = 0.315 b = 112.8697 y = (a * (x + 0.99)) - (b) tax = CInt(y) ElseIf x < 3455 Then a = 0.415 b = 266.1004 y = (a * (x + 0.99)) - (b) tax = CInt(y) Else a = 0.465 b = 438.8697 y = (a * (x + 0.99)) - (b) tax = CInt(y) End If End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Is there a "Sum" function that can be used for a cumulative series of subtractions?

For example, if I have 10, 12 and 15 in 3 respective cells, and use the sum function upon them, it will return 37.

However, I want to say 10 minus 12 minus 15, and thus return -17.

Any help would be greatly appreciated.

Many thanks,

Rich

For Example

If I was wanting to look at tax year 06 this would be from April 06 to March 07. The year would have to be determined by a entered year Input box.

After it has been split by year I then if it falls between the dates to split the date by Month. Is there any simple functions and comparitors I can use on a date defined variable.

Thanks for your Help.

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