# Function To Calculate Payroll

Nov 18, 2008
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?

View 2 Replies
ADVERTISEMENT
Jul 7, 2007

I have created a spreadsheet to work out deductions from gross weekly pay to give a net amount. I would like to be able to get from a net amount back to a gross amount but am unsure of how to do this.

Deductions are tax and national insurance. A person has a tax code for example 885 meaning they can earn up to 8850 a year before being taxed of 8850/52 a week so anything after 8850 is taxable. There are two tax rates (10% )and (18%). The band for 10% is 10500 per year or 10500/52=201.92 a week. This means any taxable pay above 201.92 is taxable at 18%.

To work out National insurance deductions for an employee, weekly gross amount- 100 x 10% giving the National insurance to be deducted. So Gross - Tax - National Insurance = Net amount.

Attached is a spreadsheet I created to work it out. Firstly does this look okay? Secondly is it possible to get back to the gross amount given ONLY a net amount and a tax code?

View 4 Replies
View Related
Jun 17, 2014

I have a problem in saving multiple pdfs from excel. I am posting a sheet here. First sheet has the employee details. Second sheet have the template for payslip. My aim is to save pdfs of templates of all the employee in the list using a macro.

currently i am doing it as

1. In sheet 2, I lookup each employee using the serial number of employees at the top left corner cell of the template.

2. Once the details are updated in sheet 2 i save it as pdf in my desktop.

The problem with recording macro and looping is that the pdfs are overwritting and the last pdf alone is saved. i could not find how to change the pdf name for every loop.

View 2 Replies
View Related
Jan 12, 2007

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.

View 11 Replies
View Related
Mar 7, 2014

Each payweek I receive about 600 records some with duplicat names if an ee works overtime. I then look at previous payroll and extract a given account code. Because some ee's work overtime the rows do not always synchronize in count or names.

Any BEST lookup function? Or BEST tool? Is access a more logical option?

View 1 Replies
View Related
Aug 2, 2006

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.

View 9 Replies
View Related
Jul 30, 2013

Sso if you've done payroll before, you may have run into the 53rd instance of a payroll week. Today I've been editing my payroll template to compensate for this occurrence and I've almost got it complete but of all things to mess up on me, a hyperlink is the problem.

I have a legend where I can see all of the payroll week periods, the amount paid, PTO used, etc. And ALL of my payroll sheets are named "Payroll #1", "Payroll #2" ... "Payroll #52" and have a cell on my legend that hyperlinks to cell $A$1 of the corresponding sheet. But when I went to add in this 53rd week, the hyperlink has decided not to work. It will work if I name it anything else, and it will work if I take the # out of the name it will work, but I want to have the # in the sheet name because all other 52 sheets are named thusly and I would rather fix this one link than go back and replace 52 other working links and risk breaking something else.

Here is an example of one of my working links:

Screen Shot 2013-07-30 at 5.41.42 PM.png

I created the Payroll #53 the same way:

Screen Shot 2013-07-30 at 5.42.50 PM.png

But it always resets to this as soon as I try using the link:

Screen Shot 2013-07-30 at 5.42.15 PM.png

And pops up this error:

Screen Shot 2013-07-30 at 5.47.35 PM.png

I do have a VBA Macro that hides both that sheet, and the row referencing Payroll #53 on the legend by using a checkbox. I'm not sure why the # sign is making everything so difficult when there are 52 other hyperlinks that use it also. I tried deleting my macro to see if that was part of the problem but it still refuses to properly create the hyperlink. I also tried rebooting to see if that was part of the problem but it wasn't.

As a side note, this particular cell will not link to any page with a # in the name.

Screen Shot 2013-07-30 at 5.45.51 PM.png

I tried creating the hyperlink on a Windows based machine (since Microsoft is native to the OS after all) and it worked. So it must be a bug or something in the Mac version.

View 1 Replies
View Related
Mar 6, 2013

I have multiple departments I need to keep track of multiple numbers with. For example, I need to see the cost of payroll for last fiscal year, vs this fiscal year and have excel show that as a gain (bad) or shrink (good).

I need to track sales this FY vs last FY, obviously, a gain is good and shrink is bad.

Finally, I need to track d&d goods this FY vs last FY.

Where I'm getting held up is, for example last year's payroll was 12000 and this year's is 12500 but next months is 11000 this FY vs 13000. How do I get excel to figure out that payroll decreasing is good and not just do simple subtraction or negative addition thereby allowing me to just plug the numbers in and excel figure out if what I'm throwing at the cell is bad or good comparing to last year?

Also, if I could figure out the if/then to show positive numbers green and negative numbers red that would be quite awesome too.

View 1 Replies
View Related
Feb 1, 2007

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

View 9 Replies
View Related
Feb 9, 2007

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.

View 9 Replies
View Related
Dec 3, 2013

I am trying to attempt to create a payroll spreadsheet with certain aspects and with multiple worksheets. so what im trying to accomplish is this

1/ on my main worksheet would have the payroll template there would be 2 columns (1) would be "routes" (2) would be the dollar value of that route.

2/ i want to be able to pick from a drop down list in colume (1) which will have various routes such as A,

B, C, D, E etc

3/ once i have selected a route in colum (1) i want to have the dollar value that is associated to that route to show up in column (2)

ex/ route A worth $1

route B worth $2

create a dropdown list but thats about it, i have not been able to link any values together.

View 11 Replies
View Related
Jun 18, 2014

I have a payroll spreadsheet and I want to highlight cells that have hours entered for vacation. For example, in cell E4 I put the job name which is "VACATION" and in cells G4 through M4 I put the hours in for each day. (G4 is Monday, H4 is Tuesday, etc.)

I tried to put a conditional formatting using the following formula ="IF(E4=""VACATION"")" then I chose the fill color however it is not working.

View 3 Replies
View Related
Dec 30, 2008

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

View 9 Replies
View Related
Nov 10, 2013

I have a workbook that calculates a RAND function based on input in another cell. Specifically, the formula in E2 selects a random text string from another range depending on data entered in D2. I want to be able to fill this formula down for use multiple times.

The formula works fine on its own, but the problem comes when I, say, move on to D3 to input more data. E3 does its random calculation as it should, but E2 also recalculates; I want E2 to remain static once it calculates the first time.

I'm attaching the workbook in question. The calculation I'm referring to is in the 3rd worksheet.

TLL Injury Machine.xlsx

View 2 Replies
View Related
Jul 24, 2006

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.

View 9 Replies
View Related
Jan 15, 2008

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

View 9 Replies
View Related
Jul 6, 2014

I am making an excel sheet which can track the hypothetical effect building an order has on current inventory. The user is to change the quantities in this hypothetical order whenever they want.

It is composed of two sheets, the inventory sheet and the model sheet. The inventory sheet has the costs of every part carried and how many are currently in stock. The model sheet has a breakdown of what parts are required to build the different models.

I am looking for a function or VBA which can take a hypothetical order (build xx of model one + build xx of model two + build xx of model three), see how many total parts are needed, then calculate and display the effect it has on current inventory. This is only to see the hypothetical effect on inventory, not to actually change the inventory in the inventory sheet.

I've attached what I have so far - most everything is completed except for this final calculation. I have tried using the VLOOKUP function in VBA to try and come up with a solution but I couldn't figure out the best way to go about this. I think the hardest part might be that a hypothetical order can be made up of all three models.

View 9 Replies
View Related
Jul 21, 2014

I'm trying to calculate the mode for a large data set, but there is no 'mode function' in subtotal. I need to find the mode for each change in day - without having to retype the function.

View 11 Replies
View Related
Oct 27, 2009

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

View 2 Replies
View Related
Jul 9, 2012

I'm struggling with the VBA DATEDIFF function. I have a person's DOB and am attempting to determine their age by comparing to a different date. It seems like their age only changes when I change the year, rather than choose a date within the year past their birth date... Here is my code:

Sub Test_click()

DOB1 = #7/19/1930# 'DOB

date2 = #7/18/2012#

Date3 = #7/21/2012#

age1 = DateDiff("yyyy", DOB1, date2) 'age should be 81 (but is 82)

age2 = DateDiff("yyyy", DOB1, date2) 'age is 82 (correct)

End Sub

View 5 Replies
View Related
Feb 27, 2007

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.

View 9 Replies
View Related
Aug 28, 2008

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.

View 9 Replies
View Related
Nov 28, 2009

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.

View 9 Replies
View Related
Jan 29, 2010

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 ....

View 9 Replies
View Related
Mar 25, 2014

I currently have in my equation the trend function to linear extrapolate the date a line would cross either my upper or lower limits (100.1 and 19.9) between 2 data points. However if the 2 data points span both the upper and lower limits (as per the 2 attached examples) then unfortunately the trend function is only able to return the 1 date (where I've specified the x value). Is there any way for the TREND function to trend the 2 values? Do I simply add the 2nd x value to the new x's?

I have attached both the data and their graphs to visually explain my problem : EF Workbook.xlsx

View 2 Replies
View Related
Jun 15, 2014

If the data looks like this:

10

7

6

10

9

9

3

What would the formula be to calculate that array of values = 57% because 4 of them are equal to or greater than 9, and there are 7 of them in total? The formula should accommodate however many numbers are listed and calculate what percentage of them are 9 or 10.

View 5 Replies
View Related
Nov 12, 2008

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.

View 4 Replies
View Related
May 15, 2008

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.

View 10 Replies
View Related
Jun 11, 2009

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.

View 11 Replies
View Related
Apr 11, 2012

I am making an IF function that calculates and converts units, for example kg to g and g to kg etc.

This is the formula I typed in

=IF(E6=kg, ((F6/D6)*G6/1000), ((F6/D6)*G6))

When I evaluate it I get the #NAME error. What do I need to change to make it work?

View 3 Replies
View Related
Aug 7, 2009

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

View 9 Replies
View Related
May 1, 2014

I am trying to automate a process involving a Grubb's outlier test and calculating averages/stdev of a set of numbers. I have the data in one column and have calculated the average/stdev/%CV of that data. I have also added a formula to calculate Max/Min outliers of that column of data. Then there are cells with IF statements that display either "yes" (for an outlier present), or "no" (if no outlier is present). What I would like to do, if it is feasible, would be to set up formulas to recalculate the average/stdev of the column, taking into consideration either the presence or absence of outliers.

Here is what I invision(cells are for example purposes)

=IF((M9 = yes, Avg(H2:H40)-max(H2:H40)),M9 = no, avg(H2:H40))

So it would calculate the average without the outlier if the outlier "yes" was present, or just calculate the average if "no" is present.

I would also do this with a minimum outlier as well, but i can set that up if this first one is possible.

View 8 Replies
View Related
Feb 21, 2014

I'm trying to exclude cells that are filtered in a separate sheet.

The sheet I'm working with gathers information from this separate sheet using the following SUMIF formula:

=SUMIFS(Claims!$H:$H,Claims!$G:$G,$A9,Claims!$B:$B,C$6,Claims!$E:$E,$B9)

This formula extrapolates information perfectly for me if I do not filter columns in the separate sheet with all of the information.

I know that SUBTOTAL functions can be used in a way to exclude hidden cells. Can I combine the SUBTOTAL function into my SUMIF function above to produce results that exclude filtered cells?

View 5 Replies
View Related
Jan 1, 2009

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

Any other grade:F

View 5 Replies
View Related
Oct 10, 2013

I want to create a VBA function that can calculate the CRC32 checksum for a file.

I found this webpage which looks promising: http:[url].....

The only catch is that the code provided is VB not VBA. How hard would it be to tweak this code to run in VBA?

View 10 Replies
View Related
Nov 26, 2009

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.

View 4 Replies
View Related
Mar 7, 2013

I have a column which contains a list of countries and the next column is filled with Yes and No's.

Is there a formula which calculates how many Yes a country receives?

e.g. USA

Yes

Germany

No

France

No

[Code] ..........

View 2 Replies
View Related
Jul 3, 2006

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.

View 4 Replies
View Related
Jul 25, 2014

I have added 3 tables data .. also I had created a sample solution calc for emp1 and project1 ... I need to calculation the ratio between the months based on the working days and allocate the efforts accordingly.

View 2 Replies
View Related
Feb 10, 2009

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.

View 2 Replies
View Related