Formula To Indicate Group Of Cell Criteria Has Been Met?
Mar 25, 2014I am trying to create a helper column to indicate if a set of criteria has been met.
see attached workbook for details.
ADVERTISEMENT
Finding The MAX In A Group Of Cell Depending On A Criteria
Jun 25, 2007I'm trying to find a way that I could get the MAX value in a group of cells depending on a critieria. The situation is that I have a list of numbers and a list of dates parallel to it. I would like to find the MAX for, say, the numbers corresponding to the month of June. I would like to be able to change the month and get the MAX for each month on request.
Eg.
18-MAY-2007 600
01-JUN-2007 500
02-JUN-2007 400
28-JUN-2007 600
12-JUL-2007 700
17-JUL-2007 200
I need formula that will give me "600" if the month I want is June. Ideally I would not want to have to filter the data, so SUBTOTAL would probably not work for me.
Sum Group From Different List If Two Criteria Met?
Aug 2, 2013I have a sheet below, where I want to sum the value on a set of data on another sheet. The formula will be where the "???????" are. Each row on the other set of data will have a cell that's either 100, 200, or 300.... the row will also have a number that's either 1010, 1020, 1030, 1040, 1050, 1060... And there are many rows of data for each combination, thus why I want it summed up in the "???????" cells.
Type
Code
Item
100
200
300
A
1010
Cash
??????
??????
??????
[code]....
Group Data With Changing Criteria
Jan 5, 2009I have created a worksheet with criteria for grouping the students. If the teacher selects 1 high, 1 med, 1 low in each group based on the conflict lesson plan (or test #2 or whatever assessment the teacher chooses), then I want the spreadsheet to create groups with the students names using the table corresponding with the assessment they choose. (I.e. the conflict table or the test #2 table)
So group one will list one high student, 1 med, 1 low etc....
Initially I was going to use lookup_occurrence to do this; however, I ran into a problem.
When a teacher selects 1 high, 1 med, 1 low, then cell B8 says high, cell B9 says med, cell B10 says low. And then I was going to put a lookup_occurrence in the corresponding c columns to find the first occurrence of those values for group one.
However the problem is this if a teacher selects all high, all med, all low in a group then cell B8 will say high still, but cell B9 will say HIGH not med now, which it does, but then the corresponding lookup formula will need to be the SECOND occurrence of the value in B9 instead of the first as before.
So you see this is a problem. I now have NO IDEA how to generate these groups since the lookup_occurrence formula idea will not work.
I also have a problem separating the periods. If the teacher chooses to group the students based on the first lesson plan, I have told it to look up the table 'E_term 1'!Card1. Problem is Card1 is a table with ALL 8 periods with a few lines separating each period. If I go in and rename each card by period as well, it is going to be a nightmare.
Attached is a sample of TWO pages from my workbook: the groupings page, and the e_term 1 page.
How do I create groups based on changing group types, periods, and assessments?
Finding Top 2 Values Per Group Based On Multiple Criteria
May 21, 2007I am trying to find the top two values per group based on multiple criteria. The list I'm working with is not sorted and would be better for it to not have to be sorted as on-the-fly sorts will likely often occur from the raw data and I wouldn't want that to mess up the results I'm looking for here.
As an Example, here's what I'm trying to do:
Make Model Rating
Ford Bronco 64
Chevy Corvette 94
Dodge Intrepid 83
Chevy Chevette 34
Dodge Viper 72
Ford Escape 21
Ford Expidition 53
Chevy Impala 67
Ford Fairmont 11
Dodge Dart 33
Generating Average From Group Of Numbers In List That Match Two Criteria?
Jan 29, 2013So we had a month long, company paid (woohoo!) "weight watchers" challenge. On 1 worksheet ("Stats"), I have the Name of every Employee (A Column), then their Start Weight (B Column), End Weight (C Column), Department (D Column) and finally Location (E Column).
I know how sensative some people can be about their weight, so I locked the page and created another worksheet named "UI", which will display the "Average End Weight" for each department. So Human Resources would be the department in cell A2 on the UI sheet, with City 1 being the Column Header in cell B1. There are 8 cities (offices) and 23 departments.
So, for cell B2, I want to scan through the "Stats" worksheet and locate all of the cells in the department and location columns that read "Human Resources" and "City 1" respectively. Then grab the "End Weight" for every row that meets these 2 criteria, add them up, and divide by the number of "End Weights" that were grabbed.
My solution would be to make a separate column for each department (and then each city, so essentially 8 columns to represent all the cities for each department), use a nested (maybe 4) IF statements to then list only the end weight if that particular row meets the criteria, then at the bottom of each separate column, add all the shown numbers up and divide, then draw the information from THAT number into the "Average" cell on the UI worksheet. But it isn't very dynamic and if i want to use this sheet next year, there will be more employee info to be added and it would be a mess..further more, it would be...what...23 Departments x 8 Cities x 155 employees = ~28,500 added cells.
Age Group Formula
Apr 6, 2008I need to find the age group from the date of birth using excel. Eg.
if some one born between 1st Sept 1998 and 31st of August 2000, they come under 12.
if some one born between 1st Sept 1996 and 31st of August 1998, they come under 14. and so on.
Group And Sum Formula
Dec 24, 2008Does anyone out there have a formula for grouping duplicates in COLUMN A than summing the corresponding dollar amounts in column B?
EXAMPLE:
I have (2) columns of data.
Column A = Lists of companies (includes duplicates)
Column B = the corresponding dollar totals
4vacations.com US
Access America
Adconian Media Inc. ....................$1,520.00
Adconian Media Inc......................$10,045.45
Advertising.com -NY- Remnant $3,091.16
Advertising.com -NY- Remnant..........$750
Advertising.com -NY- Remnant ......$2,678.82
Affinia Hotels .............................$5,510.05
Affinia Hotels .............................$3,019.50..........
Refernceing Cells: Formula That Looks At A Cell On A Front Sheet, And Then Returns The Contents Of That Cell As The Result If It Meets The Criteria
Apr 5, 2009I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'.
e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
Formula To Group And Sum Up Data?
Jul 26, 2014I've got a table containing different codes and their values:
AAA 1
AAA 1
AAA 5
XCC 2
XCC 1
WTY 7
WTY 2
...
I'd like to group them so the code shows up just once whith its' value summed up:
AAA 7
XCC 3
WTY 9
...
See the file attached.
Use A Formula To Group Same Numbers?
Jan 29, 2014I'm trying to find a way to sort or use a formula to group same numbers that are positive and negative together for reconciliation purposes. What part of my spreadsheet looks like is this:
TypeAmount
SA-10000
SA-500
DM10000
SA-20000
DM500
DM20000
What I am trying to make it look like is this: (each SA has a corresponding DM entry)
TypeAmount
SA-10000
DM10000
SA-500
DM500
SA-20000
DM20000
I know there is a VBA method but i'm not very familiar with VBA so am trying to find a formula if there is one.
VBA To Search Group Of Cells And Input Message If Value Is Missing From Group?
Mar 17, 2014I am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.
View 11 Replies View RelatedPivot Table Group :: Group By Integers And Not By Months, Years Etc?
Nov 12, 2009I have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
Formula To Identify Group For The Data?
Feb 12, 2014create a formula for my data. I want to pun binning as per criteria given.
Bin 1: IR < 150 & VR >62
Bin 2: 150<IR<300 & VR >62
Bin 3: IR >300 & 62< VR <30
Bin 4: IR >300 & VR <30
Example data
Data 1 : IR = 80 & VR =68 --> Bin 1
Data 2 : IR = 200 & VR =68 --> Bin 2
Data 3 : IR = 300 & VR =25 --> Bin 4
Data 4 : IR = 350 & VR =45 --> Bin 3
Formula For Adding Group Of Rows?
May 1, 2012 In columm (A) I have a row for each day of the year, that is, 1/1, 1/2.....12/31 which has a corresponding value in column (B). In column (K) I have a row for each week of the year, that is, 1, 2....52.
In column (L), what formula can I use so that each row will sum the values of a seven day period from column (B)?
Formula To Group Series Of Data
Feb 21, 2007adapting a formula which I was provided on the forum earlier in the week (I have attached a worksheet as an example). The formula is in column A in the following format ‘=IF(B4=B3,A3,IF(OR(AND(B3<=B2,B3<=B4),AND(B3>=B2,B3>=B4)),A3+1,A3))’.
It is used to number groups of data and it should increase by an increment of 1 for each group. The groups are defined by a changing angle (column B) which runs from high to low (group 1) then low to high (group 2) etc.
The formula in column A works fine until the angle column does not change for 1 or more data points (which sometimes occurs on the change over between groups). See row 430 and 331, the group 6 needs to either be included in group 5 or 7 and then the current group 7 needs to become group 6 and carry on as normal. Is there any way of adapting this formula or changing it to account for this problem?
I have a lot of data series like these and the repeated angles could occur between any groups not just at group 6 as it has done in this example.
Insert Group Header Based On Consistent Value Within The Group?
Feb 10, 2014I have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.
View 1 Replies View RelatedCountIfs Formula Where One Criteria Is String Within Cell
May 10, 2014I know this should be straightforward, but just can't "hack the logic", whether using "Countifs" oor "SumProduct" formulae.
Attached file 140509 String Criteria.xlsx file has database of three fields (Date, Route number and Part number)
I need the formula that totals number of rows meeting three criteria:
(a) A specific date from Col A
(b) A specific number from Col B
And the part I can't sort out:
(c) Any cell containing a "string" in Col C (so needs to count the "core" even if it has a prefix or suffix).
E.g. How many rows have "Date x" in Col A, "Route Y" in Col B and a cell containing "123" in Col C?
Delete Formula If Cell Value Meets Criteria?
Feb 18, 2014I'm using the following on the Worksheet_Activate event to update rows with the date, (Cell AD1 contains the current date):
Code:
Range("X2:X2000").Formula = "=IF(IF(ISBLANK(W2),,$AD$1)=0,"""",IF(ISBLANK(W2),,$AD$1))"
Is there a way I can amend it so it only updates those cells which currently have no data?
At the moment it is changing every cell with the date which overwrites existing dates - I want it to update the new stuff only.
Formula To Return Cell That First Meets Certain Criteria?
Mar 5, 2014Say I have a column of cells filled with numbers that are ascending. What I would like to do is to pick out the first cell that meets my condition. For instance, these cells contain irrational numbers that increase from 0 to 100. I would like a formula to pick out the first cell that exceeds 10.2, and to return the row number of that cell.
In the case of descending numbers, if I would like to pick out the first cell that goes below 10.2, would the formula be the same?
Formula- Sum Values In Col H Based On The Criteria Set In CELL B1
Oct 3, 2009i need a formula that will sum values in Col H based on the criteria set in CELL B1. The tricky part is that i need the summed values to be converted using the FX rates in Cell M:N, i have provided an answer on what the formula should return in CELL C2....
View 9 Replies View RelatedCalculate Formula Based On Cell Criteria
Aug 30, 2006I only want the formula to operate when there are values to calculate and then ONLY refer back to the last calculation. If there are gaps between the calculations I would like those to remain blank. Is that possible?
I have attached an example of what I am trying to do.
Array Formula (show Just Those Counties In That IPF Group )
Aug 28, 2007Rows 1 & 2 are on a single sheet - there are 26 counties in all, in alphabetical order and divided into IPF groups (as shown on row 1).
On a second sheet I have a data validation list showing IPF1, IPF2, IPF3... etc.
In the columns beside the validation list I would like to show just those counties in that IPF group (example in rows 4 & 6).
Use The Group Box To Group A Number Of Option Buttons Together?
Jan 17, 2013how to use the group box to group a number of option buttons together.
Question is, lets say, i have a total of 4 group box. if i want it to work in such a way where by once 2 option buttons are being selected (1 option selected from each group box), the rest of the option buttons in the 4 group box will be greyed out. is that possible?
Convert A Group Of Numbers To A Group Of Letters
Aug 9, 2006My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?
Excel 2007 :: Formula For Highlighting Cells Within Sub-Group
Jan 26, 2014I am using Excel 2007 with a WIN XP OS…. My objective is to color code each cell in Column F within each sub-group based on the following instructions:
1) Column F contains a percentage…. Each sub-group in Column F totals 100%.... Each sub-group is separated as shown on the enclosed sample.
2) Starting with the highest percentage, followed by the next highest, (etc.), I want to sum each subsequent cell until reaching a target threshold (total sum) of 62%.... All cells in this grouping to be highlighted “yellow”.
3) Once the 62% target threshold is reached, the very next highest subsequent cell percentage is highlighted “light olive”…. If there is a tie, the lower number shown in Column E serves as the tie-breaker.
4) All remaining cells within the sub-group are highlighted “light brown” including those denoted by 0%.
5) Finally, if the cell is blank and absent of a % call out, then no highlight is required.
I process a daily spreadsheet having typically in excess of 1000 rows and in turn, I’m looking for a method to automate the color coding….
PGM
HORSE
MLO
PRICE LINE
PL RANK
New PACE
[Code] ........
Create Formula To Group Insurances When There Are Multiple Rows Of Data
May 20, 2014I am trying to develop a formula that will tell me whether an account has both Medicare and Medicaid or Medicaid HMO insurance. An account can have one or more lines, and one or more insurances. I manually completed the sample file in column 'D' (see attached) to come up with the desired output. The criteria is below:
-If the account has Medicare and Medicaid financial class, then it is a '5'.
-If the account has Medicare and Medicaid HMO financial class, then it is a '6'.
-Otherwise, it is a '0'.
Formula To Count Unique Values Within A Group For Pivot - Paste Special
Jun 26, 2013I use the following formula to count unique values within a group for a pivot:
=IF(SUMPRODUCT(($CO$2:$CO55681=CO55681)*($E$2:$E55681=E55681))>1,0,1)
When I paste values over the formula it takes around 1 hours to do the 70k cells which is excessive. I want to know if there is a way to speed it up?
I tried doing an =cell and then paste special that and it works really fast but when I delete the actual formula's again the whole sheet locks up.
Formula To Accept / Delete Cell If Numbers In Other Cells Meet Defined Criteria
Apr 21, 2014I am looking for a formula that either accepts the number in a cell if the numbers on two other cells are "approved". In other words, I have on cell A1 my value to be tested. On cells A2 and A3 I have two numbers. On cell A4 I'm looking for a formula that copies the number on A1 if A2 is bigger then 0.25 and A3 is smaller then 0.35. In not, there's no copy or a N/A appears.
View 3 Replies View RelatedApply Row Count Of Sub Group In Each Row Of Sub Group
May 28, 2014In Col X of the attachment, I have manually entered the count of the rows within each sub group, as determined by the counter in Col W. How can I do this automatically? I need this because when I filter the spread sheet by Rank, I need to know how many selections, of the filter Rank query, were in a sub group of ? number.
View 2 Replies View Related
