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

View 3 Replies
ADVERTISEMENT
Mar 15, 2007

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.

View 9 Replies
View Related
Feb 18, 2008

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?

View 9 Replies
View Related
Aug 3, 2006

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

View 9 Replies
View Related
May 4, 2008

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.

View 4 Replies
View Related
Aug 6, 2014

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

Link: [URL] ....

View 1 Replies
View Related
Jul 16, 2009

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:

View 2 Replies
View Related
Jan 26, 2014

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.

View 1 Replies
View Related
Jan 26, 2014

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.

View 2 Replies
View Related
Apr 27, 2007

I have a group of 8 numbers and I have to pick the 6 highest of them and make an average out of this 6.

How can I do that, using formulas?

View 6 Replies
View Related
Jul 27, 2007

I want to pick the top five numbers out of a random list of numbers and get its average. What is the best way to go about doing this?

View 2 Replies
View Related
Dec 4, 2013

1.jpg

I wan to calculate the average for three cells not in Sequence using AVERAGEIF with condition (VALUE >0)

View 5 Replies
View Related
May 2, 2006

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.

View 11 Replies
View Related
Aug 4, 2007

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.

View 4 Replies
View Related
Sep 24, 2006

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)

View 4 Replies
View Related
Sep 6, 2007

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.

View 5 Replies
View Related
Jun 4, 2014

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.

View 2 Replies
View Related
Aug 4, 2009

I have 5 years of data in 2 columns:

Col A. Col B.

8/2/2004 Value 1

to

7/31/2009 Value n

I have a table set up as follows

1 2 3 4 5 6 7 8 9 10 11 12

2004

2005

2006

2007

2008

2009

I was wondering how I could construct a conditional statement to pull the associated values with the given month and year in the table...

I tried the following to no avail... I'm just getting a zero value:

=AVERAGE(IF(MONTH(J6:J1255)=AC$35,IF(YEAR(J6:J1255)=$AB37,K6:K1255)))

View 9 Replies
View Related
Mar 5, 2014

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.

View 6 Replies
View Related
Jul 15, 2006

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.

View 8 Replies
View Related
May 8, 2008

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.

View 9 Replies
View Related
May 12, 2009

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?

View 9 Replies
View Related
Feb 1, 2012

I need to automatically calculate the number of months a deal runs through 2012 dependant on the start and end date.

I have attached a basic spreadsheet. Column C shows the results I would like the formula to calculate.

View 1 Replies
View Related
Nov 9, 2006

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.

View 5 Replies
View Related
Jun 19, 2014

So yesterday I created a thread [URL] ..... that would clear a specific content if a date (or rather a day) matched the criteria.

Today I'm continuing with that document and need to sum the columns if the date in a row is the correct month and year.

Ex. Row 1 contains the date "yyyy-mm-dd" and row 2-5 contains empty cells or the value x. The x values are all random placed.

So, I want to sum all the "x" for February 2014.

I'd rather use a formula here than a macro/VBA-code but anything will do. I have tried myself with =sumifs and =sumproduct but with no success.

See attached file for example and for my =sumproduct formula.

Excelforumexample1.xlsxâ€Ž

View 5 Replies
View Related
Dec 6, 2009

I want to calculate Year To Data in B1 based on some data in C1 to N1. The monthnumber is located in cell A1.

There is of course several ways to do this, but is there a simple and easy formula one can use.

View 3 Replies
View Related
Dec 13, 2012

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

[Code] .....

Attached File : VACATION DAYS ACCURED.xlsâ€Ž

View 9 Replies
View Related
Oct 16, 2008

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.

View 2 Replies
View Related
Jul 27, 2013

I need to calculate the number of quarters difference between two quarter and year values. So I have The following:

Begin Quarter

Begin Year

End Quarter

End Year

3

2005

2

2011

4

2008

2

2013

So I need calculate how many quarters have passed since the begin quarter and the end quarter.

View 1 Replies
View Related
May 19, 2014

I am trying to calculate the number of months in a specific year between two dates.

For example.

Start date 01/06/2012

End Date 01/02/2013

Number of months in 2012 = 6

Number of months in 2013 = 2

How can I write a formula to give me the answer of 6 & 2 from the start and finish dates?

View 10 Replies
View Related