Calculate The Highest 5 Consecutive Year Average Salary
Feb 1, 2013
Example.xlsI have a list of people with 10 years of salary history for each (in ten consecutive columns on the spreadsheet). I need to calculate in excel the highest 5 consecutive year average salary for each (if they have less than 5 years with salary, then it should just average the years the do have). Some people have breaks in service (for five years, there is a blank in that entry). These years should be ignored and skipped in calculating the avergaes.
I have a list of people with 10 years of salary history for each (in ten consecutive columns on the spreadsheet).
I need to calculate the HIGHEST 3 consecutive year average salary for each (if they have less than 3 years with salary, then it should just average the years the do have, be it 1 or 2 years).
Here is the kicker: some people have breaks in service (for these years, there is a blank in that entry). These years should be ignored and skipped in calculating the avergaes.
So if someone had salary figures in years 1, 2, and 4, but a blank in year three, the average of years 1, 2, and 4 would constitute one three year average (whether or not it is the highest is a whole other matter...).
I have been round-and-round the best way of doing this. I was thinking of maybe creating a UDF that calculates a three average, then do it up to 8 times (one for each starting year) in 8 "helper columns", and taking the highest average.
I have data in 3 different columns (A, C, and E) to name a few. I want to average each of these columns, but if any of them include zero values, I want to exclude that from my calculation.
Example:
Column A = 10 Column B = 0 Column C = 3
Right now, my "average" formula, is showing the average as 4.33. (average a5, b5, c5)
The real average I'm looking for is 6.5. What is the best way to setup my formula?
I work in a large Public Service Payroll Department. I am trying to create a spreadsheet that will automatically calculate arrears payments for salaried staff. What I need is something that will make the calculation after I provide a start date an end date and old and new salary per annum. I'm self taught using excel but at a very very basic level.
My problem is that I don't know how to enter a formula which will give me the correct amount between 2 dates. For example
01/10/2005 to 16/09/2005 old salary PA 16000 new salary PA 17500. The manual formula would be: ((17500 - 16000)x1/12) + ((17500 - 16000) x 1/12 x 16/30)) = £191.67
I have percentages set up for my monthly costs (student loans, food, spending money) and I want to be able to type in my weekly salary and have the percentages break it down into dollar amounts. Later, I'd like to put that into a pie chart, but I'm really only asking for help referring each category/percentage to the total $ value I enter each day/week/month.
I've got a spreadsheet where the start date is amendable and this is what I wanted. E.g. start date is Jun-14 and I want the formula to to calculate annual salary divided by 12 in each month from June onwards until Mar-15 (Financial year is April to Mar) and the same way to not show anything in April or May as they started in June... How can i achieve this please as I tried IF statements and it doesn't recognise dates and years...
I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.
One method has been using this sumproduct formula:
Objective: I am trying to find consecutive (2 or more) lap time that is above a certain value per lap. Once that is accomplished. I would like to know the summation of these values and the average. These values (summation and average) are not over the entire data, but for the consecutive periods only. Ideally I would like the for n number of laps and lap-time.
(i.e) if threshold is 85 seconds. Please view the attached image and sample spreadsheet.
Lap # Lap Time Threshold 2 or more laps
0 118.2 85 118.2
[Code] ........
The formula I used to get the consecutive data above
formula I used is =IF(AND($B2>$D$2,OR($B3>$D$2,$B1>$D$2)),$B2,""). This yielded the values on the last column.
My question is, how can I sum and find the average and summation of consecutive value till blank cell. Using the example above,
I would require the average, ((118.2+92.74)/2) in one cell. I would require the next cell to give me the average, ((87.5+86.5)/2. Also I would require the summations used.
Objective: I am trying to find consecutive (2 or more) lap time that is above a certain value per lap. Once that is accomplished. I would like to know the summation of these values and the average. These values (summation and average) are not over the entire data, but for the consecutive periods only. Ideally I would like the for n number of laps and lap-time.
(i.e) if threshold is 85 seconds. Please view the attached image and sample spreadsheet.
Lap #Lap TimeThreshold2 or more laps 0118.285118.2 192.7492.74 284.82 387.587.5 486.586.5 582 681 784.2 887 981
The formula I used to get the consecutive data above
formula I used is =IF(AND($B2>$D$2,OR($B3>$D$2,$B1>$D$2)),$B2,""). This yielded the values on the last column.
My question is, how can I sum and find the average and summation of consecutive value till blank cell. Using the example above, I would require the average, ((118.2+92.74)/2) in one cell. I would require the next cell to give me the average, ((87.5+86.5)/2. Also I would require the summations used.
I must determine out of 52 radom numbers the average of the 48 highest even if one or more of the lowest numbers is repeated 7 times without exempting the 3 out of the 7 that must be included in the 48 to extract the average.
I have a set of values in the 2nd column (Col B). I have shown in Column C (I've added some notes to the right).
mon........7..........(first value, so nothing here) tue........8..........na, or ERR.......................... 8 IS the highest value in B, so no math to do yet wed.......10..........na, or ERR.......................... 10 is greater than 8…the new highest value in Col B thu.........8...........(20.00)............................. The value, 8, is 20% less than the Highest value so far in Col B fri..........6..........(40.00)............................. The value, 6, is 40% less than the Highest value so far in Col B.....
Namely, any value in Col B that is LESS than the highest, greatest, largest value that has preceeded it in chronological order will reflect by what % it is lower. (Eg, 8 is 20% less than 10). When a NEW higher value appears in Col B, there would be no answer in C (or simply "ERROR", or n.a.), and this new, largest value would be what subsequent values in Col B are compared to.
I need a cell formula that will (a) identify the highest N values in an above specified column range, (b) color the interior of those N cells (I suspect that this is not possible), and most importantly (c) return the average value of N corresponding cells, where the corresponding cells are located on the same rows as the identified N high value cells but in a specified column to the left (not necessarily adjacent)
Does anybody know what this formula would look like?
Example:
----------------------- ...| A | B | C | D | E | F | ------------------------- 1 |....| * |.........| 7 |... ------------------------- 2 |....................| 2 |... ------------------------- 3 |....| * |.........| 6 |... ------------------------- 4 |....................| 1 |... ------------------------- 5 |....| * |.........| 5 |... ------------------------- 6 |....................| ? |... -------------------------
? = average of B1,B3,B5 where (N = 3) and (specified column to the left = B)
Assume Cell A2 + A3 as the fraction: 60/100. How can I get the smallest fraction (3/5 in cells B2+B3). I do not want to use the build in cell format for fractions) There are a few samples of want I would like to get a s the result (Red Digits). I will appreciate 2 ways: via VBA Code and via Sheet Build-In Functions.
So I have some data that I would like to have average only if that data was entered in the same month and year as specified in another cell. What I have tried so far is:
[Code] .......
-RenewalMonths is a dynamic range where each cell in the range shows the month of the date in that row. -RenewalYears is the same but for the years. -RenewalOverallStuff is a dynamic range where I would need to average the data that meets the criteria.
I need to calculate SUM and AVERAGE of rainfall for each and every year separately and must be displayed separately in a separate column. For your easy understanding, I have done manually and attached the excel sheet.
I have a very basic Excel Gradesheet that's designed to simply record letter grades, and show the grade average as the year progresses. I've hit a snag with problem in the formula which shows a grade of "F" - when the grade range can only be that shown on the table (A+ to E-). I've attached the Excel sheet so you can see what I mean.
I collect unique prices each day. I am trying to find a way to determine the average of the numbers collected from the 21st of the previous month to the 20th of the current month. This formula will need to calculate for multiple months and years. So for example, I need Feb2008 average-which would be the average of numbers found between Jan21-Feb20, I then need Mar 08 average which would be data from Feb21-Mar20 etc. My spreadsheet is setup with the first column having the dates (ex. 01/01/08, 01/02/08 etc) and the second column containing the value for that particular date ($2.85, $3.00 etc).
As the number of days between the 21st and 20th change each month, I just can't seem to find a way to do it without a whole lot of manual effort.
I'm trying to come up with a formula that calculates a year to date target, taking into consideration the actual working days and the target for those days.
So for instance today is the 12th of May and my year to date percentage is 40% but it should be 100%. How do i create a formula to calculate that?
How do I calculate/display the financial year for a given date? For instance if my source cell has 25/11/2006 how do I get the result cell to show 2006/07 (or 06/07, 2006/2007, etc).
I've tried a Text() function but the best 'format_text' I could come up with was "yyyy/yyyy+1", which not only doesn't work, but would be wrong if the source date was in the second half of the financial year.
The formula that I currently have in E2, is giving me the number of years served by an employee. Is there another formula that can give me the number of years each employee has served? This is the formula that I have in E2
i have a dataset which is like a timeseries with 3 columns first is year second month third values so lets say like this
year month day value 2004 12 29 100 2004 12 30 200 2004 12 31 300 2005 01 01 50 2005 01 02 60 .....
I need to calculate the quartiles with the condition of the year or the month or even both... I suppose i need something like an array but i couldnt make it work untill now.
