Tracker
HOME    TRACKER    Excel

# Function To Calculate Payroll

## I am trying to calculate payroll. I have 2 columns where regular hours and overtime hours are manually inputed. They are then multiplied by their respective pay per hour columns to come up with regular and overtime pay. The next column adds these to get total gross pay. That gross pay is then multiplied by the FICA and FICA Med factors to figure those taxes(2 different columns). I then have a column the adds all deductions to get total deductions(Fed,FICA,FICAMed,St). The last column subtracts total deductions from the gross pay column to get net income. My problem is the net pay column is \$.01 off sometimes. I think what is happening is I obviously have all columns in dollar amounts with 2 decimal points. Some function columns have multiple decimal points in the answer and then are only showing the 2 decimal points. When those columns are used in the next equation, instead of using the dollar amount that is showing with 2 decimal points, it is remembering the multiple decimal points. This is resulting in being a penny off when I get to the end. How can I get the equations to use what actually shows in the columns(2 decimal points) instead of remembering multiple decimal places?

Related Forum Messages:
Payroll Calculation With Overtime
We are working on a spreadsheet that would project what our labor cost would be for next week. I need some help in figuring out how to calculate overtime when an employee reaches 40 hours.

Payroll :: Calculating These Formulas?
I am having a little trouble with IF. My pest control sales people are paid on straight commission. In addition to paying them a sliding percentage of the total contract value I pay them 50% of everything above \$100 they charge on the initial service.

calculating these formulas?

Attached is the payroll document.

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.

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

Function To Calculate IRR
My code below comes up with #VALUE! error msg

Function PIRR(NumPer As Single, StartVal As Double, EachVal As Double)
Dim Values() As Double
Values(0) = StartVal

For i = 1 To NumPer
Values(i) = EachVal
Next

PIRR = Application.WorksheetFunction.IRR(Values())

End Function

If Function- To Calculate The Units
I have a spreadsheet that keeps attendance. Now I need to calculate the
units (1 unit= 15 minutes) the children are actual here. On a normal
schedule they are here for 16 units but if the are late the units get
subtracted. When entering the data, they would like to use letter instead of
numbers.

Calculate Working Days Function
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.

Function Or Macro To Calculate Students
I have a dataset of students' grades (see sample below) which I need to compute the aggregates for.

Blanks indicate that the student did not take the subject. Aggregate is computed based on 6 relevant subjects, lowest is best.

Rules for computing aggregates:
1st subject: EL or HMT
2nd subject: H1 or H2
3rd subject: M1, M2, S1 or S2
4th subject: H1, H2, M1, M2, S1 or S2 (but not previously used)
5th & 6th subjects: Any other 2 not previously used

I'm guessing this is too complicated to be done in a function, but have no idea how to implement this in VBA.

Calculate A Nested IF Function In Speadsheet
I'm writing a spreadsheet where I need to calculate a nested IF function (I think), and I am unsure of how to do it.

The problem is this:
The total \$ value of the quote is entered, then depending on two variables, a rebate may be deducted from this total value. The first variable is eligibility (a Y/N response), the second is the type of project - there are two types of project and each attract a different rebate amount.

So I have:

A: total quote \$
B: Eligibility - 'Y' or 'N' (if 'Y', then value of 'C' is deducted from 'A'), (if 'N', then no value is deducted from 'A')
C: Type - '1' or '2' (if '1', then 'C' = \$1000), (if '2', then 'C' = \$1600)
D: final result

Function To Calculate Frequency Of Items
I have a matrix w/ alpha characters in a 10x10 grid. I want to see how many times each letter shows up and then rank them asceding.

What is the function to do this? I looked at Frequency and Count, but do not think either will do the required work.

Function To Calculate IRR Of An Array
I have written the code below. But it didn't work. In my opinion the error is most probably caused by irr worksheet function. Because the code is actually bigger and I tried it all part by part.

Option Base 1
Dim i As Integer
Dim j As Integer
Dim counter As Integer
Sub General() 'Makes general calculations
Dim Cash_Flow_Project_Entity(49) As Variant 'Toplam Yatýrým Nakit Akýmý
Dim FIRR(49) As Variant 'Yearly FIRR
Analysis_Period = Worksheets("Veri"). Cells(15, 5) 'Takes " analysis period" from "Veri" Worksheet
End Sub

Function To Calculate Averages Of Percentages
I am wondering if there is a function in excel to calculate the average of multiple percentages. Currently I rely on the following formula to achieve this result and I am curious if there is a quicker way:

Try this formula. .....

Just today I learned there is a function to calculate the compounded return (FVSCHEDULE). It does part of what I am looking to do by taking away the PRODUCT function and the array, but does not really simplify things. Using FVSCHEDULE my function would look like ....

A Function For Calculate The Cells Takes To Appear Each Element
I want to obtain from some elements the number of cells it takes to appear:
We have for example A,B,C, and D,
and they appear in the next order:

1A
2C
3D
4A
5D
6B
7C
8A
9A

What I want to know is how much last in appear each element.

1A1
2C2
3D3
4A3
5D2
6B6
7C5
8A4
9A1

For example, the first “A” last one in appear, but the next element “C” last two in appear. In the forth line again cames the “A”, then are three cells. The “C” was in the cell2, and cames again in the seventh cell, then it takes five cells. In the cells eight and nine are two “A”, then in the cell nine takes one cell in appear again.

WORKDAY Function To Calculate The Start Date
As everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends?

For example,

Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009

Start Date: ???
Finish Date: 8 Jun 2009
Duration: 5 days

The Start Date should be 30 May 2009.

Seems like I am not able to use WORKDAY function to calculate the start date.

What Triggers A User Defined Function To Calculate
Suppose I have a UDF: function fubar(rg as range) as variant

and I place: =fubar(\$B\$2:\$D\$11) in cell \$A\$1

When will fubar be calulated? Is it once initially, and then whenever there is a change in any of the cells within B\$2:\$D\$11.

A hyperlink to an explanation would be just as welcome as a typed reply

Date Function Query (calculate The Last Day Of The Month)
I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank
=IF(ISBLANK('ASHBY RISE'!\$C\$5),"",'ASHBY RISE'!\$C\$5)

The last day of the month function is shown below
=DATE(YEAR(A13),MONTH(A13)+1,0)

This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.

Forming An IF Statement Function (calculate The Grades Of The Students)
Writing a single nested IF statement function to calculate the grades of the students the following rules:

If the student grade isHis/her letter grade is
Greater or equal to 90:A
Greater or equal to 80 and less then 90:B
Greater or equal to 70 and less then 80:C
Greater or equal to 60 and less then 70:D

Calculate The Number Of Days Between Two Dates Using The Days360 Function
I was calculating the number of days between two dates using the Days360 function. However, now it is not accurate enough for me... Is there a function that is more accurate? based on 365 days and includes leap years... that I could use as I need this calculation to be exact? For example, number of days between April 1st, 2010 and August 31, 2012 is 884, but the days360 function returns 870.

SUMIF Function: Calculate What Is The Total For An Individual For The Whole Month
I have the same data (but not necessarily on the same cell for everyday) for everyday of the month. Now I need to calculate what is the total for an individual for the whole month. Example,I need to total John's data for the month of January from the individual sheet for Region 1, Region 2 and region 3. I tried the sumif function as below but it does not seem to work. =SUMIF('Jan 1:Jan 3'!B5:B7,"John",'Jan 1:Jan 3'!C5:C7). I have simplified the file as an attachment below.Appreciate need as the original file is for 6 months and I need a way to compute the data.

Using Lists Function Calculate The Outstanding Work Estimation Based On Available Information
I am trying to create a worksheet which can calculate the Outstanding Work Estimation based on available information.

The information available with me is presented in Rows 3, 4, 5, and 6. For these types of work orders, I have information regarding their Previous Outstanding Status, New Orders, Completed Orders, and at presented Unworked Orders (or Outstanding orders at the end of Jan 2008).

Second set of information is available in rows 9 to 23. This information provides for which type of orders were completed by Staff 1 and Staff 2.

Based on these two sets of information, I am trying to get to the point where I can calculate the following:

a) Applying the weight of orders (namely 1A, 1B, 1C and Blend) by using the drop down list function in column B, I should be able to get the results in Column C (C25 onwards till C40), Column D (D25 onwards till D40), and Column E (E25 onwards till E40). Simlarly for all other months...for February, March, and so on...till December 08.

Auto Calculate Color Function On Cell Color Change
I would like to be able to change the color of a cell in V4:AB31 and have the formula in AM10:AM13 automatically calculate the new result. As it is now the user has to press Ctrl ALT f9 for the formula to recalculate.

Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?

Function To Mirroring Cell Relative To Function's Location
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.

Is someone aware of a function which will target a relative location instead of an exact cell?

Force Function To Use Row Relative To Cell Housing Function
I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).

My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.

My Script:

Function Position(rCell As Range, Optional RightPosition As Boolean)
Dim vResult

Select Case rCell.Text
Case "QB"
vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)
Case Else
vResult = "Invalid Position"
End Select

If RightPosition = True Then
Position = vResult
Else
Position = "Position not valid"
End If

End Function

So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).

My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.

Show Insert Function Dialog Pre Set To Function
A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)

Nested IF Function Error (entered Too Many Arguments For This Function)
I keep getting the "You've entered too many arguments for this function" error.

Here is the formula:

=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))

User Defined Function Function Tooltip
when you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?

Write The VBA Function Through The Function Wizard
I have written a VBA program and using in Ms.Excel. The problem is when I try to write the VBA function through the function wizard it works but when I write the function in excel worksheet cell, it doesnt execute. I do not now the reason.

2007 Right Function With Embeded Find Function
I have a range of cells, for this example I will use 2.

Cell E17 = 77/170
Cell E18 = 8/9

Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))

This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"

Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.

Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....

Concatif Function Breaks Beforedoubleclick Function Just By Being There?
I have a tracking sheet (attached) that has many functions, but I'm having trouble with two of them. First of all, I have a Worksheet_Change event set that when a cell is cleared, it fills the cell with a formula to use the record above it as a default value if another cell is equivalent. This worked when I first wrote it, but now it seems to break every other time I use it. When assigning the formula it returns a Method Default/FormulaR1C1/Offset failed error message. It seems to be different for each one, each time and I can't figure out what the problem is.

The other problem I'm having is that I wrote a BeforeDoubleClick event to expand or contract any given record, or series of records. this too worked when I initially wrote it, but now is only hiding one row when it should be hiding eight or more.
I'm not terribly versed in VBA and totally baffled as to why I'm having these problems. As I side note, any ideas to clean up any of the code and make this run smoother (as ther will be many more series added when complete).

AVERAGE Function Embedded Within An IF Function
TPR, DISPLAY and FEATURE columns generate a rating based off of an IF function. In the Executed column, I need TPR, FEATURE, DISPLAY to be averaged together...BUT....In I want the average only include columns where there are numbers. For example in row one the eqn would be (1+3+2)/3, but in row 2 the eqn would be (1+1)/2...can I state an average function within an if function? Or what would be the best way to create an eqn for this?? I have thousands of rows to complete and doing it manually is not an option.

0- Did not meet expectations
1- Below expectations
2- Met expectations
3- Exceeded expectationsTPRDISPLAYFEATUREExecuted?Effective?Comments132Coming off of a Dec promotion113111111221

Vlookup Function Together With An Offset Function
I am trying to use the vlookup function together with an offset function but i am not getting it to work properly.

The situation is as follows:

I have a column E in which i use the vlookup function to find its corresponding value in B which in turn refers to a named range. However, the figure i want the function to return is 2 columns to the right and 2 rows above the value which the vlookup funtion finds in the first column.

I have though of using the offset function, but i cannot figure out how to make this work.

Changing A VBA Function Into An IF Function
i am using the following code to create a function in excel

i have to put the worksheets onto mac's so i cannot use a macro

can anyone help me change this into an iF function

load is cell D9 and the factor is in cell E9

Function Pmax(Load, Factor) As Double
Application.Volatile
Select Case (Factor)
Case "0"
Pmax = ""
Case "1"
Case "2"
Pmax = Load * 1.09
Case "3"

Work Around To The 7 Function Limit With The IF Function
I'm comfortable with the IF statements in excel and not familir with many of the other functions available to me. I am trying to create a character calulator for Elder Scrolls III (Marrowind). So that a person may enter their race, ***, and other choices and their by calculating all starting stats. There are 10 races you can choose from and I can cover 9 of them with the if function (leaving one as if all other options are false). I would like to know if there is a better way such as a drop down list or some way to search an array or anything that would be better suited to search the race cell and return data if it's true. I'm running into the same probably with choosing the constalation (13 choices in this catagory).

I thought to split it two different data fields, by letting you choose from 4 in one cell and the other 9 in a different cell, but realized I don't know how to make it check two different cells for a choice to return it to the display cell without hitting the limitation of the 7 nested funcations in a forumla again.

Sum Function With Address Function
On my worksheet =ADDRESS(18+BH8-1,60,4) gives me BH21. However = SUM(BH18&":"&ADDRESS(18+BH8-1,60,4)) gives me the #VALUE! error

DATE Function & IF Function (return A TRUE Or FALSE Based On A Date In A Cell)
I am trying to return a TRUE or FALSE based on a date in a cell.

for example:

if cell A1 = 07/11/2009

I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.

I have tried stuff like:

=IF(A1=TODAY()-14,"True","False")

but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?

Contains Function: Function That States If A Cell In The Email Column Contains A String Or Value From The Names Column
On sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,

I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.

Function Back Variables: Function Give Two Or More Output Variables
Can a Function give two or more output variables. e.g.

Sub a()
x = 5
result = Y(x)
End Sub

Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B

this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.

Use The OR Function Inside An IF Function
I am trying to use the OR function within an IF condition.
when I use each function seperatly, it works fine. but both functions
together always return the value of "B" - as false result, even when the
requested cell holds one of the true values.

for example:
=IF(B18=OR(148,150),"A","B")

Rand() Function In Another Function
I want to call on the existing rand() function as a part of the function i've written, however I'm getting an error saying sub/function not defined when I go to compile. How do I have to define rand() so that vba knows that I want the use the existing function?

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

Replace Function "Sub Or Function Not Defined"
I'm getting a Sub or Function not defined error on the code below. It highlights the Replace Function as the problem, have also tried the substitute function too with the same result.

VBA Round Function Vs Worksheet Round Function
I don't know if there is a setting I'm missing or I'm going mad but when I use the round function in VBA it doesn't round.

I am using Excel 2000. See the example attached.

In the cell A2 I have a value 0.525, cell B2 has a formula "=round(A2,2)" which = 0.53, but cell C2 is assigned via VBA ie
Sheet1.Cells(2, 3).Value = Round(Sheet1.Cells(2, 1).Value, 2)

and the result is 0.52??

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