Free Microsoft Excel 2013 Quick Reference

# Formula to calculate avg/wtd avg on a subset of a list

I will do my best to explain but just in case I have attached a worksheet to make things easier. I have a list of a few thousand products with data on each product. When I run through a series of cuts, I get a subset list and want to be able to calculated a weighted average by somehow saying to do a weighted average (and/or count, and/or average, etc.) on the characteristics of only the products in the subset. Let me know if this doesn't make sense but hopefully the attached example helps. Thanks!

## Related Results

### Formula to Calculate Age based on date of birth

I need a formula to calculate age today based on a person's date of birth. I used to know this but I have not used it for awhile. Please help if you can.

### Formula to calculate time base on a date

Hi all- this is a re-post (post - 2689573) since I did not have any luck the first time around,

For the purpose of automating vacation time allotment - I am looking for a way to calculate the number of years between dates (ie. the start date of an employee to present) - catch is, the number of applicable 'years' change if the start date is before June 30 Additionally, a new year's worth of vacation time is accrued on January 1st of each year.

Example - an employee who started on June 30 2011 qualifies for 1 year's worth of vacation by the next day (July 1) and then another year come Jan 1 of the following year....an employee who starts on July 1 does not qualify for 1 year's worth of vacation until January 1 of the following year...hope this makes sense - sample attached
TIA

### Formula to calculate income (with lead time)

Hi All

I'm Really struggling with a formula. I wondered if any of you could put me on the right track. Its not a big complicated one - its just that I can't see how to attack it.

I want a worksheet to calculate revenue generated by a team of telesales people.

The worksheet is here;

telesales-rev.xls

I want to be able to change the number of telesales people each month (in blue text) and the worksheet will calculate the figures for total revenue (red question marks).

I know how much one telesales person can generate as you can see on the chart (row 10).

I added the grey rows to show how to calculate the total revenue the 'long way' - its just to demonstrate how the revenue stream applies when each new telesales persons start.

I posted this before on another forum and no joy - but a few questions were asked - I answered them as follows;

> So why does the revenue from 1 sales person (row 10) increase every month?

As time goes by the telesales person has made more phone calls and therefore the revenue increases over time. The product is not one that clients would want to buy on the spot - they would buy as and when a need arises. So as more and more people are exposed to the product the revenue starts increasing (a snowball effect), although it does peak and stabilise eventually. (hope that answers that one).

> why not add a line saying monthly total revenue (say line six) with the formula: =B10*B3 (copied across)

The reason this won't work is due to the lead time. When new telesales people join the team, they bring in zero revenue for 4 months because of the lead time - but meanwhile the existing telesales people are earning revenue, the amount of which depending upon when they started. Hope that clarifies the complexity.

Can anyone lend a hand with this?

### Formula To calculate sales projection for the coming months

Hi Guys

I was just wondering if there is any formula to calculate sales projections for a coming month based on performance for the last couple of months?

I have attached a spreadsheet of the info..

### Excel Formula - to calculate highest number from a set of numbers

I have a list of about 14 numbers in each row, and I need a formula that
calculates the highest number out of a specified set eg. =max(c2, e2, g2,
j2). The problem is that some lines require two cells to have this formula
and when I do that it creates a circular reference. How can I fix this?

### Formulas: to show last cell on a pivot column

Evening !
I want a formula ....to sit two rows above a pivot table column (with sub-totals and grand total).....that will constantly show the value of the Grand Total ( whose position will change as the pivot table changes) .

Thanks

Colin

### Formula to calculate maximum principal debt

I am trying to enter a formula to calculate the maximum principal a company can borrow. I have the following data:
Maximum annual comittment to principal and interest - \$1,704,752
Term of Loan - 5 years
Annual Interest Rate - 3.58%
Annual payments

I tried the PPMT function but am not getting the right answer?
I need this for a meeting tonight - appreciate any help I can get. Thanks.

### Formula for calculating 12 month rolling period

I'm trying to link two absence spreadsheets together, each worksheet being a
separate month. I need a formula to calculate the absences on a rolling 12
month period, therefore, each time I input the current month's figures, it
adds them to the 12 month rolling total but takes one month off the beginning
of the 12 month period!

### Formula for calculating 12 month rolling period

I'm trying to link two absence spreadsheets together, each worksheet being a
separate month. I need a formula to calculate the absences on a rolling 12
month period, therefore, each time I input the current month's figures, it
adds them to the 12 month rolling total but takes one month off the beginning
of the 12 month period!

### Calculate time based on a percentage of tasks

Hello all
I am creating a spreadsheet that will calculate time based on a percentage given to tasks.
My end goal will be to put total time in a cell (60 mins) and have it multiply the task percentage by that cell to get a percentage time of each task. I am setting it up Task 1 will take 10% of the time, task 2 will take 25%, etc I want to multiply the time with the percent to get a value. (For example  Enter 60 minutes, task 1 should take 10%, the task will take 6 minutes)
I also want to put a time in (2:00) and add the time per task to determine the end time and when each task should be complete. Therefore Task 1 should complete at 2:06/
I am having issues with the percentages coming out correct and adding the time.
Can someone help? Let me know if you have questions or if I am not explaining this correctly.
Thanks
Megan

### Calculating Values based on a range or 2...

Hi all,

First time poster here and I am hoping someone out there can help me.

I arm running Excel 2002 (Office XP) and need to calculate something based on a value in a cell on another sheet - but I don't know where it is. I shall elaborate and hopefully make it easier to understand.

I have a sheet with 2x tabs that make up invoices. The first sheet (we shall call it Header) contains invoice numbers, a date, net value, VAT value, gross value and VAT rate. The VAT rate is entered as a whole number and the VAT value and gross values are calculated from it - easy as it is all on one line.

However, moving to the other sheet (we shall call this Detail) there are many rows that will make up the detail of the invoice, including an invoice number. And 3 of the columns on this are the line net value, line VAT value and the line gross value. Now, the figures are easy to calculate, apart from the VAT one as I need this to link to the VAT value on the first page.

So, I want to say for the VAT value on my Detail screen:-

"This cell equals the Net figure to my left times the percentage value that is set on the Detail sheet in column M (VAT rate) where the Invoice number in this row on the Detail screen matches the Invoice number on the row on the Header sheet that is the same."

But there is no way of knowing in which row on the Detail screen the invoice number actually is.

Does this make sense to anyone, and if so, can you advise how I can achieve this? It sounds quite complexly easy, but I need a pointer so I can get it to work seamlessly.

Thanks in advance to all for any assistance.

Regards

tmwsccsh

### Median on a variable range

Is it possible to calculate the median on a range of values that can change each month. I have a list of values (by month), and I'd like to calculate the median for each month.

ArrDt..Tim to PCI
Feb, 2008..73
Feb, 2008..83
Feb, 2008..90
Feb, 2008..70
Mar, 2008..108
Mar, 2008..85
Mar, 2008..90
Mar, 2008..100
Mar, 2008..99
Apr, 2008..180
Apr, 2008..99

The problem is, that the number of entries can change as time progresses. So, I guess I'm looking for some kind of "median - if" statement. Can this be done?

I know I can do it with VBA code, but I'm trying to keep from doing that.

Thanx

### Need help with sliding scale compensation formula

Need to create an excel formula to calculate bonus based on a percent of revenue. Here is what I am looking to do in "pseudo-code":

Revenue
\$0 - \$500,000 - bonus = x% of revenue
\$500,000 - \$1,000,000 - bonus = y% of revenue
\$1,000,000 - \$1,500,000 - bonus = z% of revenue
and so on....

The tricky part is that while this is a tiered scale, it still needs to continually calculate revenue to date. Example, the company does \$1,250,000 in revenue. The calculation would be something like:

The first \$500K in revenue is bonused at x%, the second \$500K is bonused at y% and the last \$250K is bonused at z percent.

Any help will be greatly appreciated.

### Using .Find method on a range of dates

Hi Guys,

Hope all is well

Got a strange problem . I am trying to use the .find method on a range to locate a date that matches my search string.

Basicaly it's something like this:

```
VB:
'and is filled with a sorted list of dates only

Dim test_rng As range

Set test_rng = rSearch_Rng.Find("02/2000", LookIn:=xlValues, LookAt:=xlPart, searchdirection:=xlNext)

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
In theory this should return the cell that contains a date like 01/02/2000 or 24/02/2000 because for one they are in the range and my search string actualy is a part of the value of the cells being searched.

Of course the above does not work

I even tried full date ie 01/02/2000 but .find still returns Nothing.

Why?

Here is what I have and what I need to have:
1. I got a column of dates sorted in ascending order
2. I also have a string that has only the last part of the date ie mm/yyyy 02/2000 etc.
3. I need to search this column and find the first date that relates to my month and year ie 24/02/2000 etc.

The reason I am trying to utilize the .find method is because it's fast.
I do have a for loop that uses instr() :

```
VB:

For Each rNext_Cell In rSearch_Rng
If InStr(1, CStr(rNext_Cell.Value), sAnalysis_String) Then
Exit For
End If
Next

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
It is almost imposible to use this method on a range of 30k + rows!

Is there a way to use find for partial cell value lookups or is there another way?

### Formula to calculate on a quarterly basis the fee due based on the % threshold it's c

Help!

I'm not sure if this is possible or not?

Basically every quarter I must submit a fee to my head office based on 2 variables - profit & YTD profit conversion %

Now I calculate these figures monthly semi-manually and put them into the table attached.

However the fee I must submit is based on the profit per month * 7%, and then the YTD proft conversion % is used to calculate whether I pay the full fee or 50%, 65% or 80%

Over the course of a year this conversion % goes above cetain thresholds and also below which makes it more difficult for me to think how to do it?

What I really need is a formula to calculate on a quarterly basis the fee due based on the % threshold it's currently in, but this formula will also have to take into account the overall YTD% and fees paid so that if the % level drops then I'm either not over/under paying

I know this is a really bad explanation, so I've atatched a test spreadsheet which shows what I'm talking about

Any help greatly appreciated

Thanks

### Formula to calculate if files are on time

I have a spreadsheet to track the timing of incoming files and a formula to calculate if the files arrive after their deadline. My problem arises when there is a file that is actually early but the formula reads it as late because it comes before midnight (i.e. the file is due by 2 AM and it arrives 3 hours early at 11 PM and the formula marks it as late). The dates are in the first column. Is there a way to create a formula that calculates the timeliness of the file based on the day? Thank you!

### Formula to calculate avg of last 10 grades based on criteria

Hello again.

I need help on a formula.

I need the average of last 10 tests taken by "males" (M) and "females" (F).

This is what I have in my sheet.
F = female
M = male

C214 = F
C215 = M
C216 = F
C217 = M
C218 = F
C219 = F
...
...
...
etc, etc

D214 = 95
D215 = 90
D216 = 88
D217 = 90
D218 = 91
...
...
....
etc, etc

This is the formula I have that calculates the last 10 grades regardless if it was a male or female.

{=AVERAGE(OFFSET(D214,COUNT(D214:D295)-1,0,IF(COUNT(D214:D295)>10,-10,(COUNT(D214:D295))*-1)))}

I want a formula that averages the last 10 grades taken by Females ONLY. (Not overall)

Remember, that I note "female" with "F" in my C214:C295 cells. (randomly)

How do I accomplish that with the above formula?

### Function to calculate based on a portions of a number

How would I write a formula to produce various results in differant cells
based on a portion of a number in another cell. Excel 2003

Cell A contains a variable number.
Cell B also containes a variable number.
I need to calculate:
Cell C results need to be based on 1/3rd of Cell A x Cell B, if Cell A is
evenly divided by 3. This part is not a problem (B/(A/3). If Cell A can't be
evenly divided than: Cell A results need to be (B/(A/3) + 100% of cell B if
the remainder is "1" or 50% of Cell B if the remainder is "2" .

Than comes Cell D: This result needs to be (B/(A/3) + 0% if remainder is "1"
and 50% of cell B if remained is "2"

Than comes cell C: This result needs to be (B/(A/3) only on the hole number
result of A/3.

### Formula to calculate commission based on sales percent?

Hi,

I'm trying to create a formula that will calculate the appropriate commission depending on sales % falling within a certain range. See attached, I'm looking to put the formula into column C based on the compensation model in columns F and G.

Any suggestions? Thanks!

### Formula to calculate price based on sheet2

Ok, I'm trying to modify the formula below and I'm not sure if it's even possible (although knowing you guys, I'm sure it is ). I've been using the following formula for a while and it works great, but I need to add to it if possible.

=IF(ISNUMBER(MATCH(A2,'Price Overrides'!\$A\$2:\$A\$400,0)),ROUND(CHOOSE(MATCH(VLOOKUP(A2,'Price Overrides'!\$A\$2:\$C\$400,3,0),{"Add";"Subtract";"Divide By";"Multiply By";"Exactly"},0),C2+VLOOKUP(A2,'Price Overrides'!\$A\$2:\$D\$400,4,0),C2-VLOOKUP(A2,'Price Overrides'!\$A\$2:\$D\$400,4,0),C2/VLOOKUP(A2,'Price Overrides'!\$A\$2:\$D\$400,4,0),C2*VLOOKUP(A2,'Price Overrides'!\$A\$2:\$D\$400,4,0),VLOOKUP(A2,'Price Overrides'!\$A\$2:\$D\$400,4,0))*10+0.01,0)/10-0.01,C2)

Currently the formula looks to the 'Price Overrides' sheet to calculate if it should Add, Subtract, Divide By or Multiply By the retail price. I would like to take this one step further and have the formula also calculate based on my choice of the Retail or Cost. In other words, to have the formula for example add \$2 onto the Retail, or multiply by 2 on the Cost.

An example of what I'm trying to achieve can be seen from the spreadsheet below:

http://i490.photobucket.com/albums/r...nr/Example.jpg

On the first tab (Scan Genius Import), Column 'D' is where the formula is being calculated. Column 'C' is my retail, and Column 'B' is my cost.

One the second tab (Price Overrides), Column 'D' is my denominator, Column 'C' decides the equation (+,-,x, etc), and Column 'B' is the one I want to have added to my existing formula, so I can choose either the Retail or the Cost.

Also attached below is the working spreadsheet!

### Using a formula to calculate Last 10 Games and Win/Loss Streak

I've created a workbook for the 201112 NHL season so I can calculate standings based on what would happen if the NHL were to change their current standings format of Win-Regulation Loss-Overtime/Shootout Loss to a basic Win-Loss format, where standings are based on Win Percentage instead of Points.

To do this, I've used a workbook of the 201112 schedule created by Dirk Hoag of On the Forecheck, where I can enter the results of each game in the sheet labeled "Results", and all the team's stats will be calculated automatically using formulas and reflected in 3 standings tables, on the sheets "League", "Conference" and "Division" (for the entire league standings, the standings of the two conferences, and the standings of the six divisions, respectively). I found formulas to calculate each team's stats, i.e. Wins, Losses, Home/Away Record, Goals For/Against/Difference, etc. from various places on the internet, especially here.

Attached is the workbook, but here's a sample formula, used to calculate the Home Wins for the team whose name appears in B2 on the sheet labeled "League":

=SUMPRODUCT((Results!G\$2:G\$1231=B2)*(Results!D\$2:D\$1231<=Results!E\$2:E\$1231)*(Results!C\$2:C\$1231<=Re sults!F\$2:F\$1231)*(Results!E\$2:E\$1231<>""))

(Since the season hasn't started, I've entered some hypothetical results to test the formulas.)

The only thing I can't figure out how to calculate are the columns labeled "L10" and "Streak". "L10" means the team's Win-Loss record in their last 10 games, and "Streak" is the team's current Win-Loss streak. Can anyone figure out formulas I can use to calculate these? I only need the formula to be used on the sheet named "League", I can transpose info to use them on the other two sheets. Also, I'd prefer a formula, as I'm not familiar with using VBA. Thanks in advance.

### Formula to calculate highest values in a range

Hi Guys,

I need to create a formula that uses several columns BUT only adds the highest value columns (number of columns will be dynamic).
e.g.

B1=7
C1=2
D1=3
X1=4
Y1=5
Z1=6

Normally: A1= B1+C1+D1+X1+Y1+Z1

I need: A1= max values (n) added together
n=number of columns (or number of values) to add together so it is dynamic.
if n=2:
A1=B1+Z1
if n=3:
A1=B1+Z1+Y1
if n=4:
A1=B1+Z1+Y1+X1

So i need a dynamic formula which can find the max values within a range of columns depending on the n value, which specifies how many of the max columns are added together.

I know it sounds weird & complicated but i'm hoping there is a formula of some sorts that i can insert into the cells which calculates this value for me.

If your wondering what the use of this is, it is for calculating the BEST OF marks for exam questions (6 questions, answer 5 and you will be awarded highest 3 marks). There are several exams with variances on this scenario & i need a formula that can cover this.

Any ides?

Regards,

Shuja

### Help Please. Need a formula to calculate score

Hi everybody .. I need you help,please

I have the following table in MS Excel 2010. In the column named "Score" I need to put a score from 0 to 100 so that the least value in the column "Avg. CPC" takes the highest score.

(Image of the table in the attachments)

I need a formula to calculate the score for each field.

### Formula To Calculate Same Numbers As One In Multiple Cells

Hi All,

I have a long list or work items that I track on a log on daily basis.Some work items are repeated twice or trice to input a certain comment.Therefore, when I try to calculate the total number of work items for the day using counta formula, if results in an inaccurate total since certain work items are repeated twice or trice. The work items consist of a 8 digit number.Example 8876098.

Is there a formula to calculate the total numner of work items in the data range and at the same time the formula should calculate repeated work items as only one ? Thanks.