Calculating A Formula
Sep 13, 2009
i need to creat a 12 month calculation of accounts
month 1 month 2 based on a precentage so i need to calulate the cell 1 pluse a precentage equals cell 2 and then cell 1 plus 2 and the precentage equils that
View 9 Replies
ADVERTISEMENT
Feb 12, 2012
I've made a spreadsheet that allows the user too enter their clock in and out times in a day for a week, it allows you too fill in two weeks worth (because we get paid fortnightly). It automatically calculates each week's pay once it has been filled in, all I need is a formula too calculate how much they would be paid after tax. I can work how much they will be paid for the 2 weeks befor tax, but I just need a formula too deduct tax from this.
View 9 Replies
View Related
May 6, 2014
I am trying to create a formula that calculates multiple commissions based on profit margin. So here is what I'm looking to. If the profit margin is between 50 and 70% than there is an additional 2% commission, if it's between 70.01-100% profit margin, than it's an additional 5% here is the equation I have=IF(OR(E2>50,E2<70),D2*2%,(IF(OR(E2>70.01,E2<100),D2*5%)))but it's still calculating at the 2% even thought it's an 86% margin.
View 4 Replies
View Related
Dec 16, 2009
Currently I am using the following formula ....
View 9 Replies
View Related
Apr 15, 2008
calculating a cell. It has to reference a chart, so it has to validate three answers before it knows what to input.
example CHART ...
View 9 Replies
View Related
Oct 4, 2009
I was trying to figure out how you could caluculate a project end date, based on a start date (dd,mmm,yyyy) and a project duration of lets say 5 work day and the work week is five days long (monday to friday). If i have a leave on the week days like tuesday is bank holiday then it should give be the date excluding that tuesday and wednesday leave and caluculate 5 working days and shoule give the project end date.
Example date: 5-Oct-09
Project end date:09-Oct-09
If i have bank leave on 06-Oct-09 means.
My project end date:12-Oct-09
Note:Bank holidays are fixed date example(every year bank holiday 06-oct-09,02-Dec-09,25-Dec-09)
Please dont need a macro.
View 9 Replies
View Related
Sep 1, 2006
How do I return the data from a single cell in a named range. If I type
=INDIRECT("B12")
into cell "B10". It will return data once, but as cell "B12" changes the value in "B10" does not update. The "B12" cell is part of a named range called "Datainfo".
View 6 Replies
View Related
Dec 15, 2013
I have 3 basic job categories... each of those categories start overtime at a different hour.
So, if employee A is a dock worker, he starts overtime at 25 hours. If employee A is an office worker, he starts overtime at 40 hours and if he is a driver, he starts overtime at 55 hours. So based on that info, I'm wanting my spreadsheet to figure out how many hours each employee has left for the week.
The 2nd part question is how many hours per day is left for the week. Rather than making a separate tab for each day of the week, I'd rather the spreadsheet know what day of the week it is and divide accordingly.
View 4 Replies
View Related
Feb 19, 2014
In cell BQ65, I have 7:00 pm. Next to that, in cell BR65, I have 6:00 am. This should total 11.00 hours, yet with the formula I have in cell BS65, the result is showing up as 13.00 hours, so I'm ending up with two extra hours, which is not good.
Here's the formula I have in cell BS65:
=IF(ABS((((BR65-BQ65)*1440)/60))=0,"",ABS(((BR65-BQ65)*1440)/60))
View 3 Replies
View Related
Feb 20, 2014
Example: In cells A1:A10 random number between 0 & say 20, need to sum ABOVE 8 = (calculating overtime hours)
E.g.
A1 = 0
A2 = 8
A3 = 8
A4 = 10 (giving 2)
A5 = 12 (giving 4)
A6 = 5
A7 = 13.5 (giving 5.5)
A8 = 8
A9 = 0
A10 = 16 (giving 8)
A11 = (Total overtime above 8 hours) 2+4+5.5+8 = 19.5
Need to be able to increase rows and drag across.
View 3 Replies
View Related
Mar 12, 2014
What is the formula for calculating duration in min/hrs between start time and end time without mentioning any date, and if the end time stretches to next day i.e beyond 12.00 midnight. And what is the logic used in the formula.
View 1 Replies
View Related
Oct 7, 2008
B C D
ON 135
NY 122
PA 117
WV 50
VA 126
NC 25
VA 35
MD 110
My issue: If ON I need to times (x) the miles (column C) by .0285 to equal D If NY I need to times (x) the miles (column C) by .0125 to equal D
View 3 Replies
View Related
Nov 15, 2008
I am trying to work out an excel workbook for calculating family recipes. I have gotten most of it figured out but am having a problem with one thing. When the serving size of an item is 8 oz & the recipe calls for 14.5 oz the only way I can get it to work properly is to enter 1.75 in the serving size.
But I would like (actually need) to be able to enter the 14.5 and have it calculate the calories correctly - since sometimes the amount to be added to the recipe may be 15 ounces and the serving size is 8 ounces etc....
View 6 Replies
View Related
Oct 21, 2009
I use Excel 2007 , I created Interest calculator , on Daily basis , to caluculate interest , compounded quarterly.
But I want to make it compact , as d one I created is long enough.
A3 = Principal Amount
B3 = Date of Investment
C3 = Interest as on Date
D3 = Number of Days , amount Invested {comes out of formula set}
E3 = Rate of Interest
Now in F3 I want the Interest amount , compunded quarterly.
Some times NUMBER OF INVESTED CAN BE LESS THAN 90 DAYS TOO...then what ?
View 8 Replies
View Related
Nov 6, 2008
In the last column of this workbook, you'll see a cell that says "=D2". Whenever I type a formula into this worksheet, it doesn't calculate.
If I select that cell and do text to columns (making sure to remove any separations if it tries to split it), then the formula calculates.
View 2 Replies
View Related
Sep 24, 2010
I am trying to calculate the average Talk Time into Minutes and Seconds. Every time I have tried, I get the wrong average.
For example, if I have a total of 53 calls and the total talk time is 4:19:05, what would the average talk per call be? I tried doing everything in seconds (this would give me a total of 15545 seconds) but the total I was getting is 4:89.
What would the formula be to get the correct average time?
View 5 Replies
View Related
Mar 29, 2012
I am trying to calculate when data is out of hours based on it being before 08:00 and after 16:30, plus also weekends.
I have used the following formula to calculate the day of the week.
=TEXT(WEEKDAY(AE2), "dddd")
I have this in a separate column.
This is how my sheet looks.
Actual Start TimeOut of HoursActual Start DateDay of Week
14:1525/12/2010Saturday11:0028/12/2010Tuesday22:3028/12/2010Tuesday
13:3029/12/2010Wednesday09:4030/12/2010Thursday13:0002/01/2011Sunday
09:3806/01/2011Thursday08:0507/01/2011Friday12:0009/01/2011Sunday
11:1010/01/2011Monday08:1512/01/2011Wednesday10:1113/01/2011Thursday
11:1514/01/2011Friday15:2815/01/2011Saturday10:4016/01/2011Sunday
16:3028/12/2010Tuesday13:0220/01/2011Thursday13:0624/01/2011Monday
15:0827/01/2011Thursday17:4029/01/2011Saturday
The out of hours column is where I figure I need an IF statement but not sure which way to approach it best.
View 2 Replies
View Related
Apr 28, 2012
Cell A1 = Employee
Cell B1 = Salary
Cell C1 = Years Worked
Cell D1 = 401k contributions
Cell A2 = 3%
Is there a formula I can use in Cell D1 (401k contributions) to multiply B1 (salary) by A2 (3%) if C1 (years worked) is greater than "1" and for it to show a "0" if C1 (years worked) is "0"?
If there is a formula that will work for this, it gives me the opportunity to adjust the percentage amount in cell A2 to see how contributions would differ.
View 5 Replies
View Related
Jul 11, 2013
I'd like a calculation that gives me a total of 7.50 hours instead of 8:00 hours (I'm hoping to add up the time worked into a meaningful hour:minute format)
View 2 Replies
View Related
Mar 25, 2014
30 days
03/24/14
I am trying to add 30 days to the above date . I want to leave the days because I'm calculating intervals.How can I do this?
View 4 Replies
View Related
Nov 21, 2007
I've got a list of marks (col T), and I want to create a new column subtracting 5 from each value.
Obviously, the formula in this new column is
=T1-5
=T2-5
etc...
But I enter that formula and excel doesn't do any calculating, it just displays "T1-5"
It even seems to recognize that it's a formula, highlighting T1 in the formula and the T1 cell itself, but no resulting value...
I've tried changing the format of the cells (in both columns) to Number and back to General again, but without success.
I've also tried copying and pasting (values only) the whole T column to another column, and tried there, also without any success.
View 9 Replies
View Related
Oct 8, 2008
I need to create a formula that calculates the number of days a person has worked within a quarter, if they have also left the organisation within the same quarter.
For example, I have someone who left on 26/08/08. I need to establish how many working days this person actually worked within the quarter (01/07/08 - 30/09/08). I need to do this for a large number of staff, so would appreciate it if anybody can let me know whether there is a formula that would calculate this.
View 9 Replies
View Related
Oct 21, 2009
I have the following information: Capitalized Expenses TOTAL, Date of Implementation, and Life Cycle as 36-months.
What I am trying to do is write a formula that knows to calculate the total amortized expense per year based on the implementation date and the life cycle. What's tricky is that not all implementation dates begin in 2010...so the formula has to know where to begin placing the amortized/depreciated expenses...see below:
ABCDEFG1Implementation Date: 5/1/112Life Cycle in months:363Total CAPEX: 3,500 45Lump Sum201020112012201320146Capitalized Expenses 3,500
I am looking for formulas to inset in rows C6:G:6...
View 9 Replies
View Related
Jul 6, 2004
I have data from a tems current win/loss record. I want to calculate what the longest win streak is and the longest losing streak as well. What formula do I use?
View 9 Replies
View Related
Apr 16, 2014
I am using a spreadsheet to determine if students are meeting Growth Targets between a pre test and post test.
Here is my formula
=If(D8>=C8,"yes","no")
D8 is the Growth Target and C8 is the Post test Score.
After doing this, I need to calculate the percentage of Yes results. This year it will be easy, as it only applies to 25 students. Count the yes's and multiply by 4. Next year, it will apply to number in the neighborhood of 150 students.
Here is a sample
PreTarget Post
Student 15778 82Yes
Student 25369 91Yes
Student 35778 75No
Student 45778 68No
Student 55369 50No
Student 63761 50No
Student 74061 68Yes
percent of yes's
View 5 Replies
View Related
Jun 10, 2014
I am currently using a pivot to return and populate the required data but this does not refresh correctly and returns zero values every time this report is refreshed. I am therefore hoping that there is a way to calculate this using formulas .
The following applies:
The attached document has 2 tabs: Date AND Report
The Data tab feeds the report tab
The REPORT tab has 2 reports in it but doing the first one then I should be able to do the second one. If you look at the first table you will see the data that needs to be populated. I have added a highlighted (in YELLOW) row at the bottom of this table which shows you which columns populate what column
Requirement:
I am after something that will populate this table in a descending order - IE: the figures in the DATA tab Column BE should be sorted from highest to lowest. It must be filtered to the top 30 results only and then all the other columns must be populated.
Column H in the REPORT tab should be formatted to [h]:mm
Column F & G in the REPORT tab should be formatted to dd/mm/yyyy hh:mm
View 2 Replies
View Related
Feb 4, 2014
I have developed a financial calculator that asks the user for the "input date" which is used to record balances as of the input date. My interest calculation for the first year is based on the current date compared to the input date. For example, if the user is keying in a current balance of 10,000 @ 10% interest, and the "statement date" or "input date" is 12/30/2013, and the current date is today, 2/4/2014, then it should calculate interest for the entire year of 2014. It is not doing that. It calculates $3 interest.
But if input date is 6/30/2013 with current date of 2/4/2014, it seems to work OK. It calculates interest of $504 in that case. It appears to get messed up with the year transition between current date and input date. The formulas I have listed below appear to work fine except when the input date is 2013 for the year and the current date is 2014. The formula does not "see" that input date was last year. There must be a minor tweak to formula I am overlooking.
Cell C2 = Today's Date=TODAY()
Cell C3 = Input date (user keys in date in mm/dd/yyyy format)
Cell E2 = "translate input date to year format" =DATE(YEAR(C3),12,31)-C3
Cell E3 = Investment Rate
Cell G2 = yr 1 interest rate adjusted =(E3/1)*($E$2/365)
Cell C21 = Current Balance
Cell D21 = Interest Yr1 = C21*G2
I need the interest calculation to account for partial year accrual.
View 3 Replies
View Related
Mar 11, 2014
I am trying (and failing miserably) to create a fomula to 'score' my pickers in a warehouse environment.
The basic variables are;
1) Units (Total quantity of units on picklist)
2) Lines (Number of different products on picklist)
3) Time (Total time to complete pick)
To give an example, if Units = 8, Lines = 1 and Time = 00:01, I want 'Score' to = 100.
(Based on benchmark, rounded down to lowest common denominators)
The added complication I face is the relationship between Lines and Units; it would take someone longer to pick 1 unit from 10 lines than 10 units from 1 line.
View 2 Replies
View Related
May 22, 2012
creating a formula to accrue PTO as well as track available PTO as it's used. And, not go negative if possible... if an employee reaches 0 PTO, they just take time off without pay, so a negative balance would cause issues with the calculations. Here are the specifics:
Pay periods are from the 1st of the month through the 15th. and the 16th through the end of the month. Each employee receives 3.33 hours of PTO for each pay period. Employees are eligible to START accruing PTO after 6 months of employment.
[URL]
That seemed close, however I'm just not efficient enough in Excel to understand exactly what the formulas are doing... specifically the formula for available vacation. I think I have the Rate formula figured out by using:
=IF((TODAY()-C2)>160,3.33,IF((TODAY()-C2)<160,0))
View 7 Replies
View Related
Feb 23, 2010
I know this ain't going to be easy if at all do-able, but would anyone know if there can be a formula/s made to calculate the largest size "square" shape (e.g. all 4 corners at 90 deg.) that would fit in to a known size Quadrilateral?
Value A,B,C,D and Z are all known values as are the diagonal lengths between corners
I failed dismally in Geometry at High school, but how was I to know that 35 yrs later I would need to know this stuff!!
Please see diagram for an idea of what I'm trying to achieve.
View 10 Replies
View Related