Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

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


Post your answer or comment

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

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.

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?

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

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

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?

Thanks in advance!

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

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

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

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

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.

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.

Thanks in advance!

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 
        firstAddress = C.Address 
        Do 
            Set C = myrange.FindNext(C) 
            Set MoveRange = Union(MoveRange, C) 
        Loop While firstAddress  C.Address 
    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!

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?

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

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

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
E-NET-CRADLE-KIT-03 23844-00-00 1
E-NET-CRADLE-KIT-03 4S-PW-CR-BRKT-02 1
E-NET-CRADLE-KIT-03 50-14001-004R 1
E-NET-CRADLE-KIT-03 50-16002-029 1
E-NET-CRADLE-KIT-03 CRD9000-4001E 1
SPCCE-SLDRT-01 200360-101 1
SPCCE-SLDRT-01 210156-001 1
SPCCE-SLDRT-01 25-62167-01R 1
SPCCE-SLDRT-01 ADP9000-100 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.

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

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.

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,

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.

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

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

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.