Free Microsoft Excel 2013 Quick Reference

# Calculate median based on criteria

Hi
I have a table of numbers lets say, Customers in column A, Industry types in Row 1 and data is Sales. A Customer can belong to only one industry type. I would like to find the Median Sales value in a particular Industry.
Problem is that in column B, for example, it contains numbers greater than zero and numbers equal to zero.
I was hoping to write a formula that that could determine the Median of only those numbers with a value greater than zero.
My work around so far is to sort the table by individual Industry columns apply the Median formula to the numbers > 0 and copy, paste special the result and then move on to the next column and repeat the task.
My brain says there must be an easier way but I am currently unaware of one.

Regards
GregH

## Related Results

### Calculating Average based on criteria

i want to calculate averge based on given range of Dates wihin the month

e.g

Date Product A Product B
30-Jul-09 , 2 ,56
31-Jul-09 , 5 ,456
1-Aug-09 , 6 ,8
2-Aug-09 , 8 ,7
3-Aug-09 , 7 ,7
4-Aug-09 , 69 ,8
5-Aug-09 , 2 ,66
6-Aug-09 , 1 ,44
7-Aug-09 , 6 ,55
8-Aug-09 , 6 ,66
9-Aug-09 , 13 ,66
10-Aug-09 , 14 ,55
11-Aug-09 , 14 ,10

Start Date 30-Jul-09 Formula to return average within these dates ?
End Date 30-Aug-09

### Median based on criteria

Hi there,

Im having a few issues trying to calculate the median in a document.

First up - Im trying to calculate the median on a set of parameters. I need for my median to be based by operations centre based on an audit score. For example the BIF median, the MIN median etc. This can be seen in the attached document in the tab GRAPHS. I have set up a basic formula but not sure what else i need to put in.

Secondly - Im having issues with the median formula when there is an N/A in the cell (as there is no input as yet). You can see in the main tabe AUDIT SCHEDULE AND DATA that i have set up column O to automatically assign a number based on column N. However if there is no score then an N/A is in cell O. This is fine - im just having problems when im trying to calculate the median.

Any help that can be provided will be GREATLY appreciated. I have attahced a sample document with some detail.

### Calculate Percentage Based On Criteria

I am trying to make a formula that will give me a Percentages of people that are current in certifications. Some of the personnel are not required to have the certifications though, so I have been putting N/A into their respective field for that cert. I have a formula that will give me a percentage based on whether or not its ">=" current date.

=SUM(IF(F3:F15>=N1,1,0))/ROWS(F3:F15)

N1 is my =date() block

Thats gives me a percentage, but also counts the N/A fields.

This one I made to remove them from the calculation, but I think I am trying to outsmart myself....

=SUM(IF(D3:D15>=N1,1,0))-(COUNTIF(D3:D15,"N/A"))/(ROWS(D3:D15)-(COUNTIF(D3:D15,"N/A")))

What am I doing wrong?

### Calculate formulas based on top 20%

Hi Guys,

I've got a report that I churn out every month that calculates total spend, average daily spend, median, number of people etc and as well as that I have to calculate formulas based on the top 20% and bottom 80% of the data. The attached sheet shows the number of people under different business areas, and costs in descending order.

At the moment I do it manually so I filter for each business area and work out the top 20% of people in range and then work out the number of employees, mean, median and SUM of the top 20% and bottom 80%. Below I have 20 people and I take the top 20% which is 4 and then I work out the Total number of people, SUM and median of the top 20 and I do the same for the bottom 80.

EXAMPLE:

TOP 20%
£1,330.00
£1,070.36
£997.50
£840.00

Employees - 4
SUM - £4,237.86
Median - £1,033.93

BOTTOM 80%
£825.00
£794.50
£785.00
£750.00
£748.00
£743.00
£728.68
£714.00
£700.00
£678.63
£655.00
£643.29
£622.50
£584.00
£577.50
£14.00

Employees - 16
SUM - £10,563.10
Median - £707.00

I'm just wondering if I can do this automatically using VBA. In my attached workbook I have the data on sheet 1 and the statistics in sheet 2, so I want to be able to run a macro and for the figures to come up in sheet 2, for the different business areas.

Is this possible.

At the moment it's taking up a lot of time but IfI could automate this, it would be great.

Thanks very much for your help.

Regards
Harry

### Copy formula in cell based on criteria in other worksheet

Hi guys,

I need to calculate averages based on certain criteria. This is not a problem, I figured out the formula, but because my dataset is very large, it takes forever for excel to calculate everything. (I use excel 2003). I attached a simplied file of my dataset.
Worksheet data1 contains all the data, worksheet averages should contain all the averages per analyst (column A) per company (row 3).
I would like excel to first assess whether the combination analyst/company exists in "data1", and if it does, to paste the formula as in cell B4 for that combination.
My main concern is that this will take too long to computate, as my original dataset is much larger and also consists of more years.
I hope that someone can help me out.

Chantal

### Copy Range based on criteria

I am trying to copy a range of cells, based on a set of criteria that changes constantly.

In Column A, I have numerical values that are present every 10th cell. In columns B through D, I have various text data that corresponds to the number in Column A. So for example, in A50, I have the number 46975. In the range B50-D59, I have text data that goes with the number in A50.

Each day I run a few calculations and based on the number I get, I want to find the exact match in column A, then copy all the data in columns B through D that are related. So if my calculations result in 46975, I want to automatically copy over to another worksheet the range B50-D59.

Does any of that makes sense?

### Marking a cell adjacent to range to signify maximum based on criteria

I've searched through the "maximum based on criteria/conditions" threads and they have helped in my understanding, but I am missing a key part to help solve my excel issue.

What I have is a list of names in one column, a number in the next column, and then a blank column next to that (three in total). An example below:

Smith, Joe, XK ---------------- 12--------------BLANK
Parker, Ed, XZ -----------------15--------------BLANK
Yates, Doug, TY---------------- 2-------------BLANK
Chandler, David, XK -------------1------------BLANK
Hughes, Luke, TY ---------------3------------BLANK

What I would like to have is a function that would have the largest number corresponding to a name with "XK" be marked with an "x" in the blank column. To help clarify, I want to mark in the blank column the highest valued people corresponding to their specific abbreviation next to their name.

Something to the effect of:
Smith, Joe, XK ---------------- 12--------------X
Parker, Ed, XZ -----------------15--------------BLANK
Yates, Doug, TY---------------- 2-------------BLANK
Chandler, David, XK -------------1------------BLANK
Hughes, Luke, TY ---------------3------------X

Any help would be greatly appreciated. Please let me know if I need to clarify my issue.

### Move cell value to left column based on Criteria

Move cell value to left column based on Criteria

Hi,

I am trying to do the following and have tried Hlookup with If statement but can't get this to work. I Input 1 value (say 3000) in any cell between Column k37:V37, I would like this number to enter in same row to corresponding columns AA37:AL37 based on following Criteria in Column X37 if X37 =7 (then enter K37 to AA37) like for like - apr -apr if X37 =30 (then enter K37 to AB37) Apr(k37) to May(AB37) if X37 =60 (then enter K37 to AC37) Apr(k37 to Jun(AC37)

Any help would be much appreciated. I'm hoping to do this with a macro if possible.

Many thanks in advance

Haz

### Cut/Paste row based on criteria in cell to new sheet

Book1.xls
Hi,

I am pretty new to vba, and am looking for a code to cut and paste a row of information from sheet1 to sheet2 based on criteria from sheet1 cell showing "complete".
I have attached a sample of the sort of thing I am after, and am hoping for some much needed help.

Many Thanks in advance,

Tricia

### Delete multiple orws based on criteria

Hi again,

I've tried to find solution on the posts, however, none worked with my problem.

I need to delete multiple rows, based on criteria. Start deleting rows that begin with "Criteria1" (which is text value) in B column and ends with "Criteria2" (second text value) in E column.

Attached I am sending small sample od existing excel file.

Note. I have multiple worksheets with loads of data.

Thanks!

Tasha

### Copy data to workbook based on criteria

I am looking to copy data from one workbook into another based on criteria in one column. The data is in workbook "GAS" worksheet "INVOICING" ... Look for "1" in column F (starting in row 21 to row 500). The data should be copied to work book titled "Store" and the sheet is just "Sheet 1" ... there is a header row in Sheet 1, so data will need to start in row 2. Columns that should be copied over are B, C, D, G, H, I, L. There are forumlas in some of these cells, but I would only need the value in the cell copied over.

### Macro to retrieve records from one sheet based on criteria from another sheet

Hi,

I want to have a macro to retrieve records from one sheet based on criteria from another sheet and then paste the data in a new sheet for every criteria mentioned.

I've attached a workbook with sample data for reference.

In actual, the sheet has huge amount of data, so it's not possible to filter the data and then copy paste it.

I would really appreciate if someone can help me out in this.

### Move Cells Based On Criteria

Hello! I have been pulling my hair out trying to get this to work.. I'm trying to search a column for some text, and if found in a certain cell, move that cell left 1 col and down 1 row.

First one.. I attempted modifying some code I found on here that Deleted Rows based on criteria.

```
VB:
MoveCellsOnCriteria()

Dim myrange As Range, MoveRange As Range, C As Range
Dim MatchString As String, SearchColumn As String, ActiveColumn As String
Dim MoveX As Integer, MoveY As Integer
Dim firstAddress As String, NullCheck As String
Dim AC
Dim cell As Range

'Extract active column as text
AC = Split(ActiveCell.EntireColumn.Address(, False), ":")
ActiveColumn = AC(0)

SearchColumn = InputBox("Enter Search Column", "Cell Move Code", ActiveColumn)

On Error Resume Next
Set myrange = Columns(SearchColumn)
On Error Goto 0

'If an invalid range is entered then exit
If myrange Is Nothing Then Exit Sub

MatchString = InputBox("Enter Search String", "Cell Move Code", ActiveCell.Value)

Application.ScreenUpdating = False

'to match the WHOLE text string
'Set C = MyRange.Find(what:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole)
'to match a PARTIAL text string use this line
Set C = myrange.Find(what:=MatchString, after:=myrange.Cells(1), LookIn:=xlValues, Lookat:=xlPart)
'to match the case and of a WHOLE text string
'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True)

If Not C Is Nothing Then
Set MoveRange = C
Do
Set C = myrange.FindNext(C)
Set MoveRange = Union(MoveRange, C)
End If

'If there are valid matches then move the matches
If Not MoveRange Is Nothing Then
For Each cell In MoveRange
cell.Copy
ActiveSheet.Range("A65536").End(xlUp).Offset(-1, -1).Select
ActiveSheet.Paste
Next cell
End If

Application.ScreenUpdating = True

End Sub

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

```

Second I wrote one from scratch, in an attempt to simplify things. I think I am using the offset parameter incorrectly..

```
VB:
MoveCellOnCriteria2()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("D1", Range("A65536").End(xlUp))
For Each cell In myrange
If cell.Value = "Date" Then
cell.Cut
Range("D1").Offset(-1, -1).Select
ActiveSheet.Paste
End If
Next cell
End Sub

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

```
Thanks! I really appreciate any advice!

### Updating a worksheet based on criteria

I have a worksheet where data is recorded from A31:T1030. Columns A:Q contain validation from drop-down lists for the user to enter. On a weekly basis I need to delete all the data entries for rows where a (date) value has been entered under column R, and then sort all the rows with data still retained from row 31 onwards. My problem is that if I use a delete rows based on criteria expression I lose all the validation entries, and the worksheet will cease to function. Any suggestions?

### Calculate Increase Based On Criteria

Hi, I am in the real estate business and need to create a formula based on the following logic/scenario:

We typically give out Free Rents (usually a few months) to our tenants, which is always up on the front. And then we would charge a rent increase (\$.50 per year in the example) starting 12 month after the rent free rent ends and every year (12 months) afterwards. The rent will be flat between each increase.

My questions have two parts:

1. Is this something array formula can handle in one formula?

2. Since I am not an expert on array formula, what I ended up doing was to create a line for "Rent Bump Date" which is defined as the Free Rent + 13 for the 1st rent increase (meaning if you dont get free rent you would start paying increase in month 13 and every 12 months after. And I put in what the rent would be for each rent bump date. My solution is if the rent for each month (1 - 240) would be determined based on the ranges of those bumps.

For example,

Month 1-15 Rent \$20
16-27 20.50
28-39 21
40-51 21.5
52-63 22
64-75 23
......
......
......
232-243 30

Please note that both and month and rent are not fixed but logical values.

I guess this would be a typical multiple IFs if not for its limitation (I need 20 bumps which would be 20 arguments). I was told I may be able to do this with Name Formula or Concatenate. And I have tried the Concatenate which looks like would be a MONSTER formula with 20 OR statements in it.

Is there a way the array formula can be applied here as well? I have searched this website and found a thread containing a similar situation where a LOOKUP was used beatifully. The difference is my ranges and results are all logical values instead of fixed values.

I am looking for answers to both of my questions. If anyone can shred any light on it, that would be great. I attached the sample here. Ask if you are not clear.

Thanks a million!!!!

### Copy Cells Based On Criteria & Paste Only Values

Hi,

I am very new to Macro programming. I want to set up a macro which will copy data from few cells in a sheet and paste them as "Values" to certain specific locations in another sheet based on criteria. This copy paste needs to be done for about 40 different cells.

Can anyone help me with the macro code for this ?
I want to fix this with a button on the source sheet, so that this process happens when the button is clicked.

I am attaching a sample file showing what I need.

Thanks for your help!

Regards,

Novice in Excel

### Insert Rows Based On Criteria Match From Sheet2

Hello,

I have searched topic' Insert Rows based on Criteria' in this forum and got some very good hits. But, unfortunately, my case is little bit different then those all and that makes the unique posting.

Here is my issue:

There are many columns in Sheet1 and more will be added for titles of the columns remains unchanged. One of the Col. in Sheet1 is 'Part Number'. Part Numbers are defined as 'Configurations', and each Configurations has additional parts and peices which are broken down in Sheet2. Back in Sheet1, I also have a Col. for QTY. So, if Sheeet1 has a part number that matches in Sheet2, then I would like Excel to copy all 'parts and pieces' and QTY from Sheet2 to Sheet1. Please make sure that I want the QTY to be multiplied if applies. Please see below for an example:

Sheet1 looks like this:

Ordered Date PO Part Number QTY
22-Mar-06 9000262550 TOASTER BTRY KIT-02 3
17-Dec-06 9000388225 E-NET-CRADLE-KIT-03 1
17-Dec-06 9000388225 SPCCE-SLDRT-01 1

Sheet2 Looks like this:

Configuration Type Part Number Total
SPCCE-SLDRT-01 200360-101 1
SPCCE-SLDRT-01 210156-001 1
SPCCE-SLDRT-01 25-62167-01R 1
SPCCE-SLDRT-01 CCESP-MC9060-SK0H9AEA715 1
SPCCE-SLDRT-01 SDSDJ-128 1
SPCCE-SLDRT-01 SLH-001 1
SPCCE-SLDRT-01 SPGBD_KITTING_01 1
SPCCE-SLDRT-01 SPGBD_SD_INST_01 1
SPCCE-SLDRT-01 SPGBD_STAGING_01 1
SPCCE-SLDRT-01 STX-SCGD-01 1
SPCCE-SLDRT-01 STX-STL-01 1
SPCCE-SLDRT-01 YCTM-001 1
TOASTER BTRY KIT-02 23844-00-00 1
TOASTER BTRY KIT-02 50-14001-004 1
TOASTER BTRY KIT-02 50-16002-029 1
TOASTER BTRY KIT-02 BAP9000-100 1
TOASTER BTRY KIT-02 SAC9000-4000 1
TOASTER BTRY KIT-02 SPGBD_KITTING_01 1

So, after I run the code, it should look like this in Sheet1.

Ordered Date PO Part Number QTY
22-Mar-06 9000262550 23844-00-00 3
22-Mar-06 9000262550 50-14001-004 3
22-Mar-06 9000262550 50-16002-029 3
22-Mar-06 9000262550 BAP9000-100 3
22-Mar-06 9000262550 SAC9000-4000 3
22-Mar-06 9000262550 SPGBD_KITTING_01 3
17-Dec-06 9000388225 23844-00-00 1
17-Dec-06 9000388225 4S-PW-CR-BRKT-02 1
17-Dec-06 9000388225 50-14001-004R 1
17-Dec-06 9000388225 50-16002-029 1
17-Dec-06 9000388225 CRD9000-4001E 1
17-Dec-06 9000388225 200360-101 1
17-Dec-06 9000388225 210156-001 1
17-Dec-06 9000388225 25-62167-01R 1
17-Dec-06 9000388225 ADP9000-100 1
17-Dec-06 9000388225 CCESP-MC9060-SK0H9AEA715 1
17-Dec-06 9000388225 SDSDJ-128 1
17-Dec-06 9000388225 SLH-001 1
17-Dec-06 9000388225 SPGBD_KITTING_01 1
17-Dec-06 9000388225 SPGBD_SD_INST_01 1
17-Dec-06 9000388225 SPGBD_STAGING_01 1
17-Dec-06 9000388225 STX-SCGD-01 1
17-Dec-06 9000388225 STX-STL-01 1
17-Dec-06 9000388225 YCTM-001 1

I have also attached a sample file for your review. If you can help me with this, that would be awesome and very helpful.

### Finding row on 2nd worksheet based on criteria from 1st worksheet

While I am familiar with VB, VBA for Excel has me a bit stumped.

I am trying to find a row on a second worksheet based on criteria from the first work sheet, then copy data from cells on the first work sheet to specific cells on the second.

In a nutshell

take the criteria entered in cell A2 on worksheet 1. Say 50

then find which row on a second work sheet with varying data such as
48
50
49
92
52

then once that row is found, copy data from specific cells on the first worksheet to specific cells in the row that was found containing the original criteria on the second worksheet.

Sure would appreciate a little gentle nudging in the right direction.

Thank you in advance
Jay

### Count Based On Criteria In 2 Columns

Need to count from a multiple column table based on criteria from two columns. Getting close with COUNTIF but struggling to properly insert the second criteria (nesting?).

First COUNTIF is based on column C (\$C\$7:\$C\$250) and references text value in "XXXX", which works fine. Need to also include second criteria, in same COUNTIF, from column I (\$I\$7:\$I:\$250) which would exclude counting if "CANCEL" was found in column I. Many thanks.

### Copy cells in one row to another based on criteria in target row

I have a spreadsheet wherein I need to copy data from specific cells in one row to another based on criteria in the target row. For example:
Cells A1 thru E1 from the row to be copied (e.g., Row 3) have the customer# in cell A3.
Other data is in rows 4 thru 7.
The target row (e.g., row 8) contains customer# followed by the word "Total" in column A, and a quantity in cell G9.

This repeats but with varying number of rows of data. However, each new "group" begins with a different customer#.

If anyone can provide a macro that will help it is much appreciated.
Thanks,

### Get MAX value based on criteria

I have two columns where one column contains amount and another column contain Group.
Now I want to get the maximum value from the amount column based on criteria from column group.
What will be formula for the same?
******** ******************** ************************************************************************>Microsoft Excel - week.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD13E13=
ABCDE1Sl No.Date AmountDonorGroup2118-Aug-081200DIP CHKInsider3219-Aug-086000AMB MATHRInsider4319-Aug-081200SRIJ MOOKInsider5419-Aug-081200A PDDRInsider658-Aug-0821600DRM TRUSTOutsider7612-Aug-081200INDRA BOOutsider8712-Aug-08200000ORIENT INDUSTRIESOutsider9813-Aug-0825200GG PROOutsider10 11 12Sl No.PerticularsGroupDonorAmount131Top contribitorAllORIENT INDUSTRIES200000142Top contribitorInsiderAMB MATHR6000153Top contribitorOutsiderORIENT INDUSTRIES200000Sheet3
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Sl no 2 & 3 of Top Contributor has been done manually
Want to do the same with formula.

### Convert absolute values to negative values based on criteria

Hi, is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery"

Below is a small sample of data, real data is around 100 rows

Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323

### How to find cell values based on criteria

Hi,

I have an Excel workbook with several rows.

Column A is called Employee_ID
Column B is called First_Name
Column C is called Second_Name
Column D is called Active and has an Active X controller checkbox.

I want to write a VBA script which writes all my active employees to a text file. I know how to write the textfile, but I need help with the loop/selection/criteria issue.

I imagine that this can be done with a loop based on criteria Active="1" and current A row > "". Using one or three variables for the output is probably a matter of taste.

Any help with the code is appriciated.

-Walt

### Minimum and Maximum based on criteria in a different column

Hi,

I'm trying to find the minimum value in a range (C16:C20), but I only want to consider values based on criteria in range (D16:D20). For example:

C16=10, c17=22, c18=25,c19=4,C20=76.
D16="x", D17, D18, and D20 are blank, and D19 = "x".

In cell D1, I want to put the least value from column C based on where I have an "x" in column D. In this example, because I have an "x" in D16 and D19, I only want to consider the values in C16 and C19. So in this example, D1 would equal 4.

Same thing in D2 except I would want the maximum. Considering only C16 and C19, D2 would equal 10.

I'm hoping to use a formula in cells D1 and D2 rather than VBA. I can sum them using "sumif" with the formula =sumif(D16:D20,"x",C16:C20), but can't figure out how to take the least and maximum.

Any help would be greatly appreciated.

Thank you.
Steve

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