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!

Post your answer or comment

comments powered by Disqus
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.

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

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;


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?

Many thanks in advance.

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

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?

Evening !
I want a formula 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) .



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.

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!

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!

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.
Your help is greatly appreciated.

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.



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.


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":

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

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:

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


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() :

 'vaAddress_Array is the array that keeps addresses
For Each rNext_Cell In rSearch_Rng 
    If InStr(1, CStr(rNext_Cell.Value), sAnalysis_String) Then 
        vaAddress_Array(lCounter - 1, 0) = rNext_Cell.Address 
        Exit For 
    End If 

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?


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


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!

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.


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?

Thanks in advanced.

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.


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!

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:

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!

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.

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


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:
if n=3:
if n=4:

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?



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.

Thanks in advance

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.

No luck finding an answer? You could always try Google.