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?
View Complete Thread with Replies
Related Forum Messages:
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
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
PIRR = Application.WorksheetFunction.IRR(Values())
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
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 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
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:
What I want to know is how much last in appear each element.
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?
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
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.
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.
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.
Function Position(rCell As Range, Optional RightPosition As Boolean)
Select Case rCell.Text
vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)
vResult = "Invalid Position"
If RightPosition = True Then
Position = vResult
Position = "Position not valid"
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.
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:
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:
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
Select Case (Factor)
Pmax = ""
Pmax = Load
Pmax = Load * 1.09
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.
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.
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:
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?
Function Back Variables: Function Give Two Or More Output Variables
Can a Function give two or more output variables. e.g.
x = 5
result = Y(x)
Function Y (x As Integer) As Integer
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.
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?
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..
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
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:
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.
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