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.
I use this formula to calculate a %, but naturally when there is a 0 excel returns the #DIV/0! error. Can someone fix my formula so the error gets replaced with a 0 in the formula the 0 would be in cell W2 =IF(N2="yes",BN2/W2,0.5).
i just started with a company Royal Mail and they have some very strange timings for starting and finishing and i need a formula to help me work my money out and my hours to the precise min.
for example i have a shift comming up this week and my start time is down for 22:45 and my calculated finishing time is 07:23. i need a formula that will show on a spread sheet my exact time of work.
the other time i had was 22:35 to 06:35...it all seems prity simple to alot of people but this is my other problem. if i work from 06 - 18:00 i get one rate and if i work from 18 - 06:00 i get another rate.
so i need to know how to calculate for the 35 min at say 15.24 ph.
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)
I am introducing a formula with VBA but it does not calculate it. When I get out of the execution of the macro, hit F2 then Enter, the formula calculates now. Obviously I don't want this because then I would have to do F2-Enter to all the cells. I need that when VBA pastes the formula and the formula sends me the result instead of sending me the #NAME? error.
The formula is: =if($A$2<>$A$3,TODAY())
I have tried the next instructions with no success:
The formula determine what was been completed on the previous work day. If it fell on a weekend or a holiday specified in another table, it would consider it the following weekday's work. However, the needs of management have changed and they would now like to see anything completed on a weekend or holiday push back to the previous weekday and I'm not exactly sure how to modify this formula to do that.
As an example, work was completed over Good Friday. Yesterday (Monday) when we ran the report, none of those showed up in our list of completed items until today (Tuesday). They would have liked those numbers to be included on Monday's report to get an accurate picture of work that was completed.
I have attached some sample data. Everything completed on the 18th, 19th is counted as 1 day behind but we would like 18th and 19th to be shown as 2 days behind, just like April 17. That way, if we ran the report today and set the filter to 1 (for column C), those items would not show up, but they would have shown up on yesterday's report.
how I have the data arranged... What I need to do is for each type I need to be able to calculate usage, so I will need to make sure it is subtracting say COKE usage the week of APRIL 1ST where beginning inventory on april 1ST was 99 and on April 7th the invnentory was 72, of course the usage is 99-72/7 will give me per day but I need the formula to be able to know which date will correlate to know which inventory to subtract from... So each time I add a new inventory in the sheet it will always be able to find where the data is it needs to subtract from to get the proper usage... Of course do this without manually entering the usage each week...
I cannot get a formula to transfer (copy) into a separate cell, the calculation will not display unless I select the f2 function or save. Also when I copy the formula into numerous cells below the original formula how do I stop it incrementing?
I have a spreadsheet that I was sent (with some resource calculations on it - nothing major or huge) and I have added a sheet to it. The issue is that, on the existing sheets, any formula I add works but on the new sheet - they don't!. I get on the new sheet is the formula written out in the cell:
=vlookup(b2,table,2,false) rather than the answer
I have - looked at the formula and it is correct - cut and paste all sheets (values only) into a new sheet - had a look in the VBA window - nothing stood out
I need to work out a formula to calculate the “value over limit” in the following scenario:
Cell D11 contains a total balance (input manually) Cell D1 is the total of cells E to H Cell A1 contains the formula to calculate the % of Cell D1 against the balance in Cell D11 Cell B1 will used to input a credit limit which will either be blank or the value of the limit
In Cell C1 I need a formula that will return 0 or value over limit based on the following-
Formula to calculate the value over limit: If cell A1 >10% of Cell D11 return the value difference between 10% of Cell D11 and the value in Cell D1 example; cell D11 = £100,000 & cell D1 = £22,000 (cellA1 = 22%): 10% would be £10000 therefore, the result would be £12,000 over limit
However, the comparison also has to take account of Cell B1. If this contains a balance then this needs to be taken into account e.g. in the example shown, if cell B1 contained a balance of £5000 then the result would be £7000 over limit or if the figure was =>£12000 the result would be 0.
If the % in Cell A1 is <10% the value is always returned as zero in Cell C1 regardless of any balance in Cell B1.
Calculate dates as The Number Of Days, Months Or Years Between Two Dates
The two formulas I tried below show the wrong output when trying to figure the days between 12/8/2011 to 1/2/2012. It should show 0 years, 0 months, 24 days, not 138 days. I did notice when the day of the month on the start date is before the day of the month of the end date, the formula seems to work. Ref. the second dates on the first example.
Example 1 Start End 12/8/2011 1/2/2012 = 0 years, 0 months, 138 days
Wrong display. should show 0 years, 0 months, 24 days
Start End 12/1/2011 1/2/2012 = 0 years, 1 months, 1 days
Note that when the start date "day", (1), is before the end date "day", (2) the formula seems to work.
First formula used above: =DATEDIF(B3,C3-(MOD(B3,1)>MOD(C3,1)),"y")&" years, "&DATEDIF(B3,C3-(MOD(B3,1)>MOD(C3,1)),"ym")&" months, "&DATEDIF(B3,C3-(MOD(B3,1)>MOD(C3,1)),"md")&" days"
Example 2 Start End 12/8/2011 1/2/2012 = 0 years, 0 months, 138 days
Should show 0 years, 0 months, 24 days
Second formula tried above: =DATEDIF(B10,C10,"y") & " years, " & DATEDIF(B10,C10,"ym") & " months, " & DATEDIF(B10,C10,"md") & " days"
I need a correct formula to calculate percentage...mine is not correct
Cell J18 will hold the formula.Range C18:I18 will have values.
C18 will have a value for today,D18 will get a value tomorrow,E18 will get a value 1 day after tomorrow etc..etc..
J18 formula - =sum(C18:I18)/7
If I only has a value in J18 then it will divide that single number by 7.......can a formula only divide by entries in cells....if I have 1 entry then it divides by 1,if I have 4 entries then it divides by 4 etc..etc until thee is 7 entries.
I need a formula to calculate the height of a cell. Basically I will repeat this formula thoughtout the database, and each cell in column U will have its row height.
have come up with the following
Function RangeHeight(rng As Range) As Double RangeHeight = rng.Height End Function
Now, the problem is, that the formula does not automatically update when the row height changes, and when I open the file on another pc, all the formulas change to #NAME since it does not recognise the custom formula.
I am trying to calculate a baseline average sales formula skipping weeks were there was a promotion. The maximum weeks = 8. In my table, promotions are indicated by equal values in the promotion column (C) and weekly sales column (A). If there was no promotion a 0 is in column (C).
Beginning the formula in cell B10, If A10<>C10, average A10:A2 If a11 = c11, average A10:A3 ( move up a cell in Col A and average that cell with the preceeding 7 numbers).
However, if another value in A equals the corresponding C values within the averaging range (i.e. A8=C8), I want to skip "A8" in the average calculation and include the next cell outside the range (A2 or A1). In other words, I want to average a maximum of 8 preceeding entries in Col A that meet the criteria A<>C.
i am trying to make a wage sheet calculator using excel.. and the problem arised was that how to deduct late fine .. i have a sheet which says for example if he is late for 15min then 10$ and if he is late for 30 min then 15$ ..etc..
how to write the formular if the person of salay say 1000$ range comes late after 15min. 30min . 60min .etc...
im doing an excel spreadsheet. on 1st sheet i have a summary page for some clients, on sheet too i have worked out and done the formula so that it calculats values i put it. i want to take this total value i have worked out (which is a variable- will change from day to day) and move this value to sheet one. from here i want the number that i show in the summary page to deduct from a fixed total in the cell nxt to it. and ideas ive tried several options coming close but no ciga
I have a file wiht 2 sheets, on one sheet I have a macro that opens 25 other excel files and pastes information into the sheet in a specific order. Then on the "main" page I have different calculations based on the information from the other sheet.
The Information is listed in a row so for example B2:M2 or B3:M3. If a value has not been assigned the vcell will be empty (No formula or anything, completely empty)
The Value in the cells will be between 0 and 100 (its a score from 0-100) so in the "main" page I want a formula that calulates the LATEST score (It will always be the first one on the furthest left and the latest one to the furthest right). Is there any easy formula to calculate this?
I am trying to work out a % score for a telephone operator. To explain further, I would monitor a call that an agent takes. The agent starts at 100%. There are 20 points to be scored and they are scored like this
1 - Yes 0 - No left blank - n/a
B3 counts the number of entries in the column B2 counts the number of 1's in the column B28 = B2/B3
Seems to work fine until the agent makes a fail which would enter a 0 in the column. Somewhere I think I need to take account that there are 20 possible fails or passes?
I am trying to put a time sheet together that will place the number 8 in a cell when the work "sick" or vacation is in another cell.
For example, if I am out sick on monday, I would like to put "sick" in the cell next to monday and have the number 8 be placed in another place. Then If I am out "sick for three days I would like all of those "sick" days to add up to 24.
I am creating a sales per day/week worksheet for my reps to keep track of their sales. I have created the sheet that they only have to enter in what they have sold and it automatically calculates what they have sold per hour in the day, and what that makes their weekly total.
The problem is sometimes they will work 5 days, sometimes 4, sometimes 6 or 7.
im trying to fix the weekly average formula to divide by 7.5 for 1 day worked, 15 for 2, 22.5 for 3, etc all through 7...
this is the formula I have now, obviously it doesnt work