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

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

- Calculating Average based on criteria
- Median based on criteria
- Calculate Percentage Based On Criteria
- Calculate formulas based on top 20%
- Copy formula in cell based on criteria in other worksheet
- Copy Range based on criteria
- Marking a cell adjacent to range to signify maximum based on criteria
- Move cell value to left column based on Criteria
- Cut/Paste row based on criteria in cell to new sheet
- Delete multiple orws based on criteria
- Copy data to workbook based on criteria
- Macro to retrieve records from one sheet based on criteria from another sheet
- Move Cells Based On Criteria
- Updating a worksheet based on criteria
- Calculate Increase Based On Criteria
- Copy Cells Based On Criteria & Paste Only Values
- Insert Rows Based On Criteria Match From Sheet2
- Finding row on 2nd worksheet based on criteria from 1st worksheet
- Count Based On Criteria In 2 Columns
- Copy cells in one row to another based on criteria in target row
- Get MAX value based on criteria
- Convert absolute values to negative values based on criteria
- How to find cell values based on criteria
- Minimum and Maximum based on criteria in a different column

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

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.

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

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

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

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!

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.

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

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

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

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 SubIf 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:Thanks! I really appreciate any advice!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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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

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.

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

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.

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,

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.

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

Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323

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

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