I am really new to VBA programming and I have to automate an excel sheet which has the columns Closed Qtr, Apps Bucket and Res Time.
Closed Qtr has the following entries: Q107,Q207,Q307,Q407,Q108,Q208,Q308,Q408.
Apps Bucket has the following entries:
BU Apps,Global Apps,Quality,others
I have to find the median of the Res Time data based on grouping by Closed Qtr and Apps Bucket, i.e., for BU Apps in Q107, BU Apps in Q207 and so on.
how would I find the median of a group of numbers? I have a database with a group of numbers associated with a part number. I need to find the median of each part. Here is what I have tried, but it doesnt work =MEDIAN(IF(A:A=A2,B:B))
View 5 Replies View RelatedWe are trying to find the median of a large set of numbers to calculate the median income in 2010. For an example we have 8,379k people with $2500 average income, 9,783k with $7500 average income and so on. How can I calculate the median average income of such a large amount of entries?
View 8 Replies View Relatedhow to find the median for a list of numbers using =median(). But what if I have a list of prices for different types of say cars.
Car Type Price
A $10000
B $15000
A $20000
C $30000
Is there a macro that will return
Car Type Median
A $
B $
C $
What would be the code to look for the last row with data in it, the going through it cell for cell and setting 0 in all the empty cells ?
View 12 Replies View RelatedIf you open the file sets.xls, you will see that I have listed 5 sets of 10 numbers.
A21:J21
A22:J22
A23:J23
A24:J24
A25:J25
These sets are such that all 10 numbers matches with sets of 20 numbers listed vertically in the range K1:S20 atleast twice (T21:T25 -twice)
I know there are 50+ sets like these and I have listed just 5 of them.
How can I search the other sets?
I have uploaded a zip file which has an excel file. Run the module "DoIt" and you will get all 50+ sets like these.
The problem is that the module takes 42 minutes on my Celeron 800 Mhz 256 MB SDRAM. I want to reduce this time.
I am trying to find an item within an array and then VBA could execute code.
Here is an example:
[Code]....
LookupItems =("text", "value", "book") or should I acutally be using Split("text,value,book",",")
For Each sht in ThisWorkbook
If sht.name = array(LookupItems) Then.....execute code
[Code] ....
So basically in this example I want to loop through all the sheet names in the workbook and if any of the names in the arrary are found it will execute the code for those particular sheet names.
I know alternatives are the select case or write an if statement for each value I am looking up or even use an OR for each value to lookup; but I just wanted to see if this method was even possible as it would be less coding.
I have a really nice blue colour that I want to incorporate into all my spreadsheets, but I cannot suss out what the RGB code is, is there some code that could do the trick?
View 2 Replies View RelatedNeed a code that sends emails to the intended receipients based on hold codes if Columnd J reads as YES Email formats are listed on "Email Format" tab, code should pick the required field value from the table (Hold Report).
Analyst name, Phone number are entered in the text boxes (data validation for these is desired)
option boxes are used to send email based on the selection as below
if "send initial email without attachment" is selected then emails should be sent without the attachments
if "send initial email with attachment" is selected then emails should be sent with the attachments
Attachments are usually .PDF files and are stored on users desktop with file name as .pdf
if the follow up otion is selected the subject line on the mail should be appended as *** 1st Follow-Up*** along with standard subject line
Rest of the requirement remains same for followup option (same email format is used) based on the option selected, code should be able to send emails with or without attachments. I have provided three command buttons that are intended to work as follows;
Validate data: need to validate the data to ensure that each row has atleast one email address in "TO" column and there are no spaces in the email address.
Clear Data: Should clear the existing data from the table
Send Emails: Should send emails
Standard subject line is a combination as below
Invoice on Hold for ; Invoice number: ; PO Number: ; Invoice Amount:
Note: Column H and I apply only for hold codes Qty Ord and Max Ship Amount
I use Microsoft 2007.
I have a database of employees with 7 different variables [Year, JobFunction, PreviousIndustry, PreviousRegion, Degree, Experience, CollegeMajor] of which I would like to be able to select any number of and find their median/max/min/etc salary.
For example, I may need to know the median salary of all employees who started in Year 2006, who work in accounting, and have an MBA.
------------------------------------------------------------------
Rather than copy and paste my 7 variable AVERAGEIFS function, I'm just going to use a two variable example here to keep things simple.
I had no trouble doing what I need with this 2 variable AVERAGEIFS function like so;
{=AVERAGEIFS(Salary,File,$A$3,JobFunction,$A$6)}
...where Salary, File and JobFunction are the names of a cells in another worksheet and $A$3 is a drop down menu that I can select the File Year and $A$6 is a drop down menu where I can select the JobFuction.
Now I would also like to have the same kind of conditional "IFS" filtering with the MEDIAN function, but I have been unable to figure it out since Excel doesn't have a MEDIANIFS function. This is what I've tried so far;
{=MEDIAN(IF(AND(File=$A$3,JobFunction=$A$6),Salary))}
but this just returns "0" which obviously means I'm doing something wrong, can anyone help me out here?
Note: I was able to get it to work with just a single variable just fine like so;
{=MEDIAN(IF(File=$A$3,Salary))}
but trying to add the second [and eventually 3rd, 4th, 5th, 6th, and 7th] conditions is the problem.
Is there a good/easy formula to use when trying to find the mean, median and mode for a column? If so, can someone post it? I'd like to make it as easy as possible (obviously!).
View 6 Replies View RelatedI have 5 numbers (can be with decimals) in column A (A1:A5). For these
numbers, I want all combinations as well as the median of these
combinations.
I'm pretty far already. However, the median seems to be calculated
wrong.
I have a list of hotel IDs in one column of my spreadsheet. In the next few columns, I have ratings that customers gave the hotel for cleanliness, location, room, etc.
What I need to do is calculate the median of all the ratings for each specific hotel, in a separate column.
An example of what I have: ...
Is there any way to do a weighted median in Excel?
Consider this example: ....
I have two columns of data, one is the price per unit, the other is the quantity sold at that price. I'm trying to find the median selling price, but MEDIAN doesn't work correctly and I'm not sure how to tell the function to count each price the actual number of times it was sold.
I actually believe I've found a work-around for this problem - I sort by price and use a SUM function to figure out where the middle is...I just feel like there should be an easier way (for the future).
Is there a way to get median for a subtotal?
median(SUBTOTAL(1,G7:G1747)) is not working.
I have a list of ages 10-65 and then different number of participants associated with each age, ie: 10 - 5,071, 11 - 6,069, 12 - 8,465, etc. to age 65. I am try to calculate the median age of all participants.
View 9 Replies View RelatedThe following is a sample data. Col A has years, Col B has values. I want in Col C, median for each of 1 year, 2 year, 3 year. Currently I am sorting the data on Col A and then enter the Median formula in Col C for each range (1 year, 2 year etc..). What I would is to have a formula in Col C that automatically calculates the median without having to sort Col A. The formula should work if the data in Col A changes ...
View 9 Replies View RelatedI have a sheet of many rows a small sample attached. Column A list 3 races at times4.05,4.40 and 5.10...col b contains the names and col c a number for each name.
In col d from d2 to d11 in each cell I need the median number of col c for the race at 4.05...in d12 to d24 I need the median number of the race at 4.40 etc...My sheet has thousands of races..
Hi, Im using a array in Excel VBA, i got a method which looks at a element no of the array as as a return for that array, i have got it to sort the array, i just need some help to find the middle number (median) of the array.
View 6 Replies View RelatedPicture 2 rows and 4 columns.
The top row is just 0, 1, 2, 3
The bottom row is something like: 850, 790, 200, 250
Here's the problem: I want to find the midpoint of the data set in row 2, based on the values in row 1. So, the sum of the data in row 2 in this example is 2090, meaning the midpoint is 1045.
If we were traversing along the top row as a line, we'd find that midpoint somewhere in the 0.3 range... but how do we get that value?
I am trying to calculate the median difference between two dates but I am running into trouble. Here is sample data:
A B
1/1/13 2/1/13
1/2/13 2/5/13
1/4/13 1/25/13
1/7/13
1/10/13 1/18/13
I want to be able to type notes into column B such as "closed" or simply "X" (so i can keep track):
A B
1/1/13 2/1/13
1/2/13 2/5/13
1/4/13 1/25/13
1/7/13 X
1/10/13 1/18/13
I tried this:
in column c, use "datedif" function then calculate median from column C values.
problem: if left blank, value in row 4 comes up as #NUM!. if text is entered, value comes up as #VALUE!
I also tried this array: =median(abs(B5:B1-A5:A1))
Should i be trying the medianif function?
I have a nifty formula that conditionally tots lots of figures up, and conditionally tots up another set of figures up and divides them to create a percentage
=SUMIFS(INDEX(MATRIX!$S$1:$BC$5933,0,MATCH($D$2&$D$3,MATRIX!$S$1:$BC$1&
MATRIX!$S$2:$BC$2,0)),MATRIX!$N$1:$N$5933,$H12,MATRIX!$L$1:$L$5933,$D$5)
/SUMIFS(INDEX(MATRIX!$S$1:$BC$5933,0,MATCH($D$2&$D$3,MATRIX!$S$1:$BC$1&
MATRIX!$S$2:$BC$2,0)),MATRIX!$L$1:$L$5933,$D$5)
There are 4 conditions in the first part of the formula and 3 conditions in the second one. A division and a neat little percentage.
What I would like to do is add a median to this.....
Essentially I want the formula to 1. Calculate the same as above (as I need the median to be based on the percentages) and then there should be 18 figures that I would like to median
Is a nested sumif and a median possible?
I have a list of properties with dates of purchase and sold, i need to find the median of a range subset with it that have an sold value equal to S. I cannot see to get the date range and sold value S in the subset so I can calculate the median, this is what I have but I dont think it is right.
=MEDIAN(IF(Sheet1!$H:$H>=F165,Sheet1!$G:$G))
where H is the date column, G is the price, I have F column for the sold value, not sue how to put it all together.
I am looking to asign values of +/- above and below the median of a range of numbers, then calculate the results.
worksheet attached
excel macro and i would like to ask u if i want to calculate a median of column of data and then the result i need to put in a new column with row by row....i need how to write the coding?
View 3 Replies View RelatedIs it possible to submit the MEDIAN() function as an array (ctrl + shift + enter)?
I.e., I would like to submit a function similar to this SUM() function:
{=SUM(IF(("a"=$A$1:$A$7),$B$1:$B$7,))}
However, when I submit
{=MEDIAN(IF(("a"=$A$1:$A$7),$B$1:$B$7,))}
it does not give me the intended result.
Attached is a spreadsheet showing various stockcodes in various districts with their prices. My aim is to find the median stock price of each stockcode which might show me the district which has a huge variance in price with the same stock. Average can be used on a subtotal, but median is not there. The spreadsheet is an example and the real list is thousands of rows, so I can't manually go and change the range for each stockcode encountered to find the median for each stockcode.
Any suggestions, I am using excel 2003 and can't download add-ins due to my work's network security, though I could do it at home if someone found a good add-in etc.
I am wanting to base some calculations on a filtered list, specifically visible cells. Subtotal works great on average and other calcs, but not median. Any way to get a median calc on visible cells?
I can't use sumproduct or ctrl-shift arrays because I don't know of a way to identify if the cell is visible (or hidden).
I've been trying to get a formula that will calculate a median for base salary if it equals two criteria:
Grade: 15-8 (in column C)
Department: E or S (in column O)
the base salaries are in column I
I put the E's in Column A, S's in Column B, and the Grades in Column C of the worksheet where i need the median.
I tried the following: =MEDIAN(IF((data!$O$3:$O$200=$B10)+(data!$C$3:$C$200=$C10),data!$I$3:$I$200))
because it is such a large data set, I don't want to go in manually and calculate every median for the specific sets.
Is there a way?