Tracker
HOME    TRACKER    Excel

# Calculate Net Pay After Tax

## building a spreadsheet. I have the total price in cell f6. In cell C6 I need price with no taxes. D6 should be the pst of 8% and E6 should be the gst of 5%.

Related Forum Messages:
Formula To Calculate How Much Tax To Pay
Working from cell D19 I am trying to find a formula to calculate how much tax to pay.
I have a TAXABLE INCOME figure (in cell D18) , from this figure

The FIRST £1520 should be multiplied by 0.1 (in cell C19,)
The next £26880 should be multiplied by 0.22 (in cell C20, the answer to be put into D20)
Any remaining from the Taxable Income figure (put into cell C21), should be multiplied by 0.4 (the answer placed in cell D21)

Calculate The Tax On Any Income
I'm trying to write a function that takes a single argument grossIncome of type Currency. It should calculate the tax on any income using the following tax schedule

1) If income is less than or equal to \$15000 there is no tax.
2) If income is greater than \$15000 and less than or equal to \$75000 the tax is 15% of all income greater than \$15000
3) If income is greater than \$75000 the tax is 15% of all income between \$15000 and \$75000 plus 20% of all income greater than \$75000

Then I want to write a sub that asks for the user's income and have the above function calculate the tax on the income and say the tax in a message box.

Calculate BiWeekly Pay Periods
I have been working on solving this riddle since yesterday. What I'm trying to do is have Excel use todays date on the computer to tell me when my Paydays are in a given month. Or have it tell me the next two or three Paydays based on Today's Date.
I can get Excel to calculate Every Two Weeks by using the formula =A3 + 14 then have each sequential field add 14 days to the previous fields date. However what I end up with is just a column of Pay Dates. Not what I'm looking for. I want to have an active page that only shows 2 or 3 future Pay Days. I want to use the function =TODAY() in my formula so that every time I open up the file it can calculate the next Pay Dates automatically.

I have been playing around with this formula:.....

All this does is tells me the First Friday of every month. This won't work for me because the Pay Periods are separated by 14 days not the 2nd and 4th Friday of every month. Here is an example of my Pay Periods: Sep 19, 2008; Oct 3, 2008; Oct 17, 2008; Oct 31, 2008; Nov 14, 2008; Nov 28, 2008; Dec 12, 2008; Dec 26, 2008; etc.......

Formula To Calculate Pay Stub
I need help to create a formula to calculate pay stub. The calculation is based on a default start date of 1/1/2006. The end date will the pay period ending in the employee paystub.We would then use the Year To Date amount to calculate the monthly salary. I need to build this into an excel formula but could not find the correct formula.

Calculate Tax Based On Income
I am working on a tax deduction formula for payroll. I need 3 formulas. The first formula needs to find two cells that are on the same row based off the employees status and gross income.

Calculate A Pay Back Period For A Certain Investment
I need to calculate a pay back period for a certain investment. Excel has options for NPV and IRR but not for the Pay back period. (PBP). I've tried finding a solution using google discussion groups and some books but nothing helped me out. attached you'll find an example of my sheet. I need the PBP to be calculated in e.g. 5,3 years. but when the figures change it should automaticly recalculate the PBP.

Conditional Formula - Calculate Only If Net Unit Is Zero
Here's the table for one game

Profit/Loss(\$)
xx/xx----2----------------##--------------2--------------125---------- ##
xx/xx----##---------------1---------------1--------------150---------- ##
xx/xx----##---------------1---------------0--------------165---------- +65
xx/xx----##---------------4--------------(4)-------------170--------- ##
xx/xx----2----------------##-------------(2)-------------165---------- ##
xx/xx----##---------------1--------------(3)-------------180---------- ##
xx/xx----3----------------##--------------0--------------180---------- (10)

Number in bracket means negative.

## means empty cell.

How can I type a formula, so that every time there's no stock left (ie zero net
unit), it will calculate the net profit once.

Calculate Pay For Mileage While Factoring In The Number Of Participants
I'm trying to do is calculate pay for mileage while factoring in the number of participants. In other words, if the mileage was between 0 and 50 miles, and there were 5 or less people, then this cell should be \$35.00. But if it's between 0 and 50 and there were more than 5, then this cell should display 175/the number of people (175 being 35 * 5). In other words, more than five people would receive an equal amount of the total pay for five people (this is because five people are the standard, but there are instances in which six or seven could participate and I was hoping to avoid hardcoding that).

The rest of the if statements simply follow that same formula, but for different mileage/pay rates.

Formula To Calculate Pay Based On Hours Worked
i am trying to make an excel spreadsheet so i can keep track of my hours and pay at work... i know how to do mult and add but i wanted to know how to do the 2 together.... for example

Hours ST OT Total Hrs Gross Pay

8 2 10 ?

i would like help figuring out the gross pay if say for example my st time rated is 21.21 an hr, and the ot rate is 31.82 how can i get the 8 times 21.21 = 169.68 and the OT 2hrs times 63.64 to show up in the gross pay as a total of 233.32 - what would the formula look like?

Calculate Total Pay Based On Sliding Percentage Scale
I am trying to set up a spreadsheet where all the sales manager will have to do is input the number of cars sold to determine what the total amount of commission will be for a salesperson. I have something set up so far, but need the spread sheet to do an if/then type thing where it will search the table, and produce the dollar amount for the number of cars sold based on the scale and the number in the # cars sold cell.

I've tried conditional sum, IF, goal seek, and all kinds of stuff to no avail. The spreadsheet is attached.

Lookup The City Tax And Add It In If The Customer Is Not On The Tax Exempt List
I am almost done with form I have been making and so far this forum has been invaluable.

My last hurdle is this:

I have a list of Tax Exempt Customers that when thier name is entered into the form, I would like it to not add tax.

I have tried a forumula like this:

The above formula does not work, but I dont know why. I want it to lookup the city tax and add it in if the customer is not on the tax exempt list.

Lookup Wage Calculations (calculate Pay Per Shift Dependant On The Type Of Shift)
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.

I have attached a screen shot of the current page,

In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.

The shift pay is the column i am having trouble with.

I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.

Calculate Pay For Shift Work With Different Rates Based On Shift Hours
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is \$8 when worked between 08:00 and 19:59 , night payment is \$12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!

examples:

start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333

start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000

start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000

Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................

Formula To Calculate Sales Tax From Total Sales
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.

Holiday Pay
I need a formula to compute holiday pay. I am having problems when there are two holidays during the month and the hire date of the employee is after the first holiday. For instance in November, November 11 and November 26 are holidays. If the employee is hired before November 11, he gets paid for the two holidays. If he is hired after November 11, he only gets the second holiday.

A B C 1 HOLIDAYS 2 HIRE DATE RATE/ 11-Nov-09 3 HOUR 26-Nov-09 4 5 10/15/2009 60.00 6 11/16/2009 60.00 7 11/28/2009 60.00 8

Net Present Value
I'm trying to set up a "simple" calculator for a co-worker who sells memberships at a country club. The purpose of the calculator is to help show an individual why it makes financial sense to join now (mid-year) and have monthly dues waived for some number of months as opposed to waiting until December and joining right before the membership deposit goes up in January and be required to pay dues from day one.

I'm not exactly sure how to build an NPV formula for the 'join later' column (cell C11). The assumption I am using is that the prospective member would take his money and invest it at the assumed rate and then use those funds to join and pay dues at the later dates. I've got a formula in there right now, but it is only giving me their net out of pocket expense, not the net present value of those funds.

here is what I have so far: ....

Tax IF Formulas
I need help figuring out an IF formula that would allow me to calculate the tax owed. The tax rates are 20%, 25% and 30% and the full bracket total for 20% is 4,000\$ and for 25%, 11,500\$.

In D14, I have as a taxable income, 20,000\$ and In E14, I would need a IF formula that calculates that... but I would need to copy only one formula down the E column to be used on varying taxable incomes...

Holiday Pay Calculation
I have a problem with a cell displaying the correct number of days.

We work a 38 hour week and therefore each day is 7.6hrs

I have a start date and current date and calculate the number of days employed. I know that each day employed is worth 24 minutes, so over the year it calculates to 20 days per annum.

as I have not entered all the hours taken off yet, my current holiday entitlement is 767:04 (hh:mm).

when I process this on the calculator it tells me I have nearly 101 days holiday due, but I can not get excel to display XX:XX:XX (DD:HH:MM)

What would like to know is how many days that equates to using the 7.6 hour days

Trucking Company Pay
Here is the Scenario, A friend of mine who works for a Trucking company asked me this Question; This is How their company calculates His Pay for the Trips that he Makes.
@ first 100 Meter + Loading A Value of 1 Which will be Multiplied by the weight of the Load L and then a Flat Fee of X, And Off course The weight is not of any Issue for us for time being.

@ first 100 Meter + Loading A Value of 1 * X
@ Next 500 Meter a value of Max. 500-100=400 => 400/100= 4, then A Value of 4 * Y, Then if Total Traveling is 350 Meter, There we will have 1*X*L + 2.5*Y*L
And again L is not important and for time Being Could left Out.
@ Next 10 Kilometer A value of [Number of Kilometer - .5]*Z Then Max would be 9.5 * Z
@ Traveling of Over 10 and Less Than 30 Kilometer would be [Number of Kilometer  10] * W Then a Traveling of 27 would be 17 * W
@ Over 30 Kilometer a Value of [Number of Kilometer  30] * V
And Finally all these would be added up accordingly.

So far so good, and There is no Problem and Easily I can Use Excel To Calculate Every thing for me Just by Entering the Total travel amount and the Weigh of the Load, Using If, And Vlookup.
But Here it gets Complicated, They have these Following additional Conditions;

1: If the Traveling Road is a High way or Major Paved Roads Then all the above Values would be multiply by .77
2: If the Road is a Semi Paved Road then all the Above Values would be multiplies by .9
3; If the Road is a Dirt Road then all above would be Multiply by 1

For Example if the Traveling is 17 Kilometer and 2 kilometer is Dirt at the Beginning, and 2 Kilometer is the semi Paved at the end and the Rest are Fine Paved Road.

1*X*1+ 4*Y*1 + 1.5*Z*1 + 8*Z*(.77) + 5*W*(.77) + 2*W(.9)

At work we have a roster in excel. The only problem is that it changes almost daily

The people that change the roster aren't very computer literate and I was wondering myself if there was a way to have the person updating the roster (which by the way has different sheets for different months) to simply click a button that says save --> In so doing the current workbook would be saved, then automatically uploaded to a website so staff can simply log onto the site and see the roster.

Progress Tax Calculator
I'm trying to come up with an efficient formulae or function to calculate tax

The problem I have is that the tax is progressive. As below

the first 20,000 is taxed at 5%
the next 20,000 is taxed at 6%
the next 20,000 is taxed at 7%
the next 20,000 is taxed at 8%
the next 20,000 is taxed at 9%
more than 100,000 is taxed at 10%

I'm trying to do a formula like below

Cell B3 is my taxable amount
Cell B5 =IF(B\$3>20000,20000*0.05,B\$3*0.05)
Cell B6 =IF(B\$3>40000,40000*0.05,(B\$3-20000)*0.05)

This gives me a problem in that for 35k say, I end up with a negative number for the second part in cell B6. My other issue is that each calculation will take up 6 rows on my
spreadsheet. I was hoping to set up a function that could do this in a cell, but even the simple stage defeats me at the moment.

Sliding Tax Function
I want to make a function via macro to calculate tax During search I have found a code but the result is not coming correctly.

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:
IncomeTax Payable
110000 > 0
130000 > 2000
150000 > 4000
160000 > 6000
250000 > 24000
490000 > 96000

The function is:

Public Function tax(income As Single)
Select Case income
Case Is <= 110000
tax = 0
Case Is <= 150000
tax = (150000 - 110000) * 0.1
Case Is <= 250000
tax = (250000 - 150000) * 0.2 + 4000
Case Else
tax = (income - 250000) * 0.3 + 24000
End Select
End Function

Bracket Tax Formula
I am working with a income statement in excel, and I need to calculate the tax rate on Net Income. I have to calculate this with both positive and negative net income, and insert this into the cell below net income. I also have to account for different tax rates.

\$0\$7,500 10.0%
7,500-30,650 15.0
30,650 74,200 25.0
74,200154,800 28.0
154,800336,500 33.0
336,500 and up 35.0

Filtering For Multiple Pay Types
I need to find the total \$ collected on repair orders that contain customer pay and warranty repairs. The problem is that C and W show up on differerent rows, if an RO has 4 lines 3 may be c and 1 line w. I don't know how to use pivit tables to get the info I am looking for, or if that is the best way to fnd my answer.

Making A Calculator From Vb.net To Vba
i made a claculator in vb.net, and now i have to make one in vba, what i did was as follows, first ill post the code from last year, then my vba attempt;

Compare 2 Files And Do A Net Send
Here's my first file, a txt file. It's just 1 line:
5 10

Here's my second file. It's an excel file:
Name CIQ ASA
Bob 4 20
Joe 5 10
Bill 6 8

I need something that compare's the first number (5) in File 1 with the first number for each person in File 2. The first line in File 2 is just a heading. If Bob's CIQ is greater than 4, I need to issue a net send that says "Net Send bob Alert! Check the montor" Or if Bob's ASA limit of 20 is higher than the second number in File 1, I need the same message to go out to Bob. Same for Joe and Bill.

This seems like it should be simple but I've struggled with this for over a week.

Tax Year Date Splitting
I have a date that is save as a date variable. Is there any quick functions that can be used in VBA that will get a date split by tax year.

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.

Average Net Investment Calc In One Cell
How to calculate ANI in one cell? The only way I can currently find the result is to build an amortisation schedule and then divide the sum of the outstandings by the payment frequency.

I'd like to avoid building the amort.

VB.NET Automation Of Charts With Named Variables
I'm having a lot of trouble automating charts from my VB.NET program. I'm simply graphing one series of data as a time series that must maintain its order as the user filters the data, deletes some data, etc. Because of the changes the user can make, I am using a named variable with the offset command to maintain a dynamic range. I am aware of how to manually build the graph by supplying the series range =workbookname.xls!namedvariable, but I can't figure out how to supply this to my chart from my VB program. The following is some of my VB.NET code.

xlapp.names.add("graph" & i, "=OFFSET(Hidden!\$A\$20,0," & 1 + i * 3 & ",Hidden!\$A\$20,1)")
chartrange= ?
chartPage.ChartWizard(Source:=chartRange, HasLegend:=False, Title:=ParameterNames(i) & " Time Series")

I can supply more code if needed, but figured this would give a general idea of what I am trying to do. This has to work across multiple Office platforms so I am using late binding.

How To Have Excel Match Numbers And Net/add Them Together
how to match two exact amounts (one positive and one negative) and add them together. i've been doing this process manually but i bet there's a macro code out there that can help me automate this process.

fyi here are the things that i've tried:
- created an absolute value column and sorted by absolute value then netted numbers against each other...this is only good if you're working with a few lines but if you try to match 2,000 lines/rows in excel it will take you forever [like what i'm doing now sigh!]

if you do have a solution, please do step by step instructions. i.e. pls tell me what to click etc. i'm an excel dodo so i need very specific and elementary instructions so i won't screw it up.

Formula In A Cell That Configures Sales Tax
I have a formula in a cell that configures Sales Tax. How do I add to my existing formula so that if someone is tax EXEMPT to not display or calculate ANYTHING in the Sales Tax Cell? I want to add this to my existing formula in my sales tax cell: IF(Y33="YES") and then I want it to override any existing formula and display nothing in that cell at all.

Vsto .net Security Policy Not Permitted
I am using Visual Studio 2005 Profesional with VSTO, making a program for Excel Office 11. My code is working when inside the Studio. But when building the code, and then trying to run the xls file direktly I get the error attached. I need information about what I am doing wrong, and how I can change the .NET security policy so that the code will also run outside the Studio.

Could Not load file Or assembly 'TestExcel, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. Failed to grant permission to execute. (Exception from HRESULT: 0x80131418)............................

Payroll Extract To Work Only On Certain Pay Period Dates.
I currently have an Excel payroll extract that populates a start date and end date via the calender control 11 user form.

What I have found out is I need to inject some sanity to this application. I can not have users select days that are outside of a pay period.

The users should only be able to select the first or the 16th of the month for a pay period start. Then they can only select the 15th or the 28th/31st for the pay period end.

What I am trying to do is have them select the month and the pay period start date, then the end date would automaticly be selected. But I don't want to have to create a bunch of loops to counter for the differing month end dates or leap years.

NET WORK DAYS Between 1 Fixed And 1 Variable Date
Im wanting to calculate then no of days between 2 dates. The first one is variable.

Date 1: (Contract start date) Either the date entered in a cell A1, or 01/04/2008 - whichever is later

Date 2: (Contract end date) Date entered in cell A2.

example 1:

Date 1 = 1/1/8
Date 2 = 31/12/8

In the above example i would want the calculation to work out the n o. of working days between 1/4/8 and 31/12/8

example 2:

Date 1 = 1/5/8
Date 2 = 31/12/8

In the above example i would want the calculation to work out the n o. of working days between 1/5/8 and 31/12/8

This will allow me to calculate the number of contract days in this financial year, even if date 1 was in last financial year.

Excel 2007 Net Working Days Function
I am trying to use the NETWORKDAYS function in excel to calculate the working days for my company. the problem is I m located in UAE and here Saturdays and Fridays are official days off for my company. Now the problem is that the excel have this built in function that Sunday and Saturdays are weekend days. I want to customize the function for my local days off for my company.

Desperately Seeking Solution - Payroll Tax Withholding
I need to determine federal tax withholding from gross pay.

If gross amount (for S-0 = single no dependents) is
Over.................But NOT Over............Then Subtract.........And multiply
\$0.00...............\$195.00.....................\$5 1.......................10%
\$195.00............\$645.00.....................\$99 .......................15%
\$645.00............\$1,482.00..................\$351 ......................20%
\$1,482.00..........\$3,131.00..................\$447 ......................25%
\$3,131.00..........\$6,763.00..................\$849 .....................30%

If gross is \$400 then what are the total taxes taken out? Could someone please provide a formula?

I tried this one: =IF(M10

IF And VLOOKUP: Get The Correct Sales Tax When Selecting A Particular County
See attachment for full explanation of problems. I'm trying to get the correct sales tax when selecting a particular county. I can't get the vlookup to work and also I can't get the formula in the data validation of the county to work.

Formula Change To Gather The Absolute ABS Sum Instead Of The Net
I am using the following forumla, it gathers the net sum of a column of \$ amounts.

I need to have it changed to gather the Absolute ABS total instead.

=SUMIFS(\$B\$23:\$B\$500,\$A\$23:\$A\$500,">="&\$V23,\$A\$23:\$A\$500,"<"&EDATE(\$V23,1))

V23 has a date "Month" value.

Retirement Calculator For Citizen In Canada (RSP, Provincial And Federal Tax)
I don't know if some of you have an example on how to create a retirement calculator for citizen in Canada (RSP, provincial and federal tax) ? Do I need to create some sort of Monte Carlo simulation do to so ?

Formula For 2 Low Net & 1 Low Gross Score In A 5 Man Golf Team
I am using Excel 2002 in Xp and also want to use the spreadsheet on my Pocket PC using Pocket Excel.

I have a column of 10 numbers with rows 1,3,5,7 & 9 representing the individual gross scores of the 5 team members. Rows 2,4,6,8 & 10 have either a 0 or -1 (calculated) in them representing the strokes (or discount) the player receives for that hole. So the net scores for each player will be row 1 + row 2 and so on.

I need a formula that will calculate the total of the 2 lowest net scores and 1 low gross score with the score for each player only being used once which will then represent the team score for that hole.

A sample column would have 4,0,3,0,4,0,3,0,5,-1 in it.

Check If 2 Dates (or Any Dates In Between) Fall In Tax Year
I want to create a function that will check 2 user input dates (and anything in between) to see if it lies in a defined tax year. I will define the tax year in Cells A1: 6/4/2009 and A2: 5/4/2010 and ask the user to input dates in Cells B1 and B2 to check if any of these dates (or anything in between) falls between A1 and A2.

For example: Tax Year is 6th April 2009 - 5th April 2010, Cell A1 is 6/4/2009 and A2 is 5/4/2010. User inputs 2nd March 2009 in Cell B1 and 10th May 2009 in B2. Because the 10th May falls in the tax year the output should be "True". I can Excel to check one user defined number (Cell B1) using this =IF(AND (B1>=A1,B1<=A2),"Yes","No") but not sure which route to take to check 2 numbers B1 & B2 and anything in between.

I am running an exe file using vb.net.Exe generates output in the form of excel sheet which has some records .Now I would like to upload this excel sheet to my path ie C:/MiS Application/file.xls.

Lookup Or If Function: Find The Room Nights And Net Room Revenue Values For The Specific Rate Code In The "MATRIX" Workbook
I am having a bit of a problem creating a formula for this report that I have to update every week. I need to be able to find the room nights and net room revenue values for the specific rate code in the "MATRIX" workbook.

On "CURRENT REPORT," I need D4 to pull up the value on "MATRIX" that equals "Room Nights" in column H and "CONABC" in column J. The same thing needs to happen for F4 but with "Net Room Revenue" in column H. The full report has about 500 of these codes in column J, and I need a formula that I can copy easily and will not be affected if codes are added or removed. This is super last minute - I need to finish this report by tomorrow morning, so take a look at the attachments.

Sub Calculate
I am trying to call a sub calculate but I keep getting errors, is calculate a reserved sub name?

How To Calculate Combinations ?
I want to see how much combinations are possible when i got 6 numbers..

-- got this numbers 1--2--3--4--5--6
-- want to calculate how much combinations of ( 2 ) numbers possible
-- want to calculate how much of ( 3 ) numbers possible
-- want to calculate how much of ( 4 ) numbers possible
-- want to calculate how much of ( 5) numbers possible

-- never 2 same numbers together (2-2) or (2-2-3) or (2-2-3-4) may not be in list

-- How can i make the result visible in kind of list ?

I would be nice if somebody knows a good solution..

Calculate Variances
Just wanted to find out if the formulas in the attached apreadsheet are correct. Formulas from E6 to E10.

Also, if you multiply the "new daily target" with "working days" should'nt this give you the "remaining" figure? Currently it's not doing this

Calculate Attendance
I got struck in preparing absenteesm report from 22nd Sep 08 to 21st Oct 08. I've to put the dates on which a employee was absent ...

Calculate Totals
I have a spread sheet that is used to review calls placed by a call center.

Column A has the extensions of the phones, and Column E has the type of call (Outgoing or Incoming). Each line is a new phone call.

We have about 8 extensions, but the worksheet could have a couple thousand calls. So, Column A could have extension 1401 from rows 1-100 as extension 1401 made 100 calls. I'm looking for a formula or macro that will summarize how many outgoing and incoming calls extension 1401 had. Thoughts?

My initial thought was something like this:

=SUMPRODUCT(--(A2:A5000=H2),(E2:E5000))

In this formula, I would type in the extension of H2 and it would scan Column A and add up the values in Column E. The only problem is, that Column H doesn't contain a numerical value. It only has "Incoming" and "Outgoing" (minus the quotes), so this doesn't work.

Calculate Ratio
I'm trying to create a quote... basically I need to find out about calculating ratios.

We have 47 students going on a trip to different venues, we offer 1:10 teachers (i.e. one teacher free, for every ten students going), however some venues offer a different ratio e.g. 1:5 teachers free of charge.

I need to somehow create a column where it will calculate the amount charged for the different ratios the venues offered.

Calculate The Variance
i have a dynamic list of numbers....currently 10 numbers in the list.

how can i calculate the variance?

i have the upper limit (=MIN(1,(mean+half width))
i have the lower limit (=MAX(0,mean-half width)
i have the mean (avg of all numbers)
i have the t value (TINV(alpha, (n-1)))
i have the half width (t value * SQRT of Var/N)

i just don't know how to get the VAR/N