Trying to find max, min other statistics of dataset with 1300 rows

B

brandnew22

New Member
Joined
Aug 19, 2013
Messages
2
Essential context: I am comparing my company to others in this massive dataset. I have a worksheet of all locations that I am in, all the codes of my company's products, type (1,2,3), product NAME, total cost. That way, each row displays my company in a given market and each market has 15-30 products, i.e., with product names, product codes, types, etc.

Layout I want looks like:

Location | Code | Product Name | Product type| Total Cost | MAXIMUM COMPETITOR PRICE | NAME OF COMPETITOR WITH HIGHEST PRICE | NUMBER of competitors within 20% of Our price

I am trying to find the maximum and minimum that match a specific set of criteria, e.g., location (column A), Code (Column B), Product name (Column C), Type (Column D), so for Las Vegas, Code 19200, Type 3, there might be 20 people that match this, but I want to find the maximum price (e.g., column F). I have tried max(if(and(Sheet1 A:A = Location, Sheet1 B:B = Code, etc etc ))))) but continue to either get wrong values (When I check, there are actually larger values in the desired range) or I get #VALUE!.

Please help me find a function that can do this.

I am also hoping to essentially "loop" through each row and return a count of how many people are within our price range, e.g., NUMBER of competitors within 20% of Our price should use our price for that given record/row as a baseline.

Sample table if it helps:


Location CIP Standard CIP NameSchool NameSchool TypeSchool's Program NameAward LevelTotal Cost (Internal)Total Cost (Audited)Highest Competitor PriceCompetitor With Highest priceMedian Competitor PriceLowest Competitor PriceCompetitor With Lowest PriceNumber of Competitors within 20% of Delta CostNumber of Competitors above 20% Delta's CostNumber of Competitors Lower than 20% of Delta's Cost
Kansas City11.0901Computer Systems Networking and TelecommunicationsBryan College - Kansas CityUsNetwork Administrator and SecutiryAssociate $32,462.13
Kansas City11.1001Network and System Administration/AdministratorBryan - Kansas City (Internal Delta Data)UsNetwork Administration and SecurityAssociate $31,885.49
Kansas City43.0199Corrections and Criminal Justice, OtherBryan - Kansas City (Internal Delta Data)UsCriminal Justice ProgramAssociate $34,535.43
Kansas City43.0199Corrections and Criminal Justice, OtherBryan College - Kansas CityUsCriminal JusticeAssociate $35,967.81
Kansas City44Human Services, GeneralBryan - Kansas City (Internal Delta Data)UsHuman Services AssistantAssociate $32,810.82
Kansas City47.0303Industrial Mechanics and MaintenanceBryan - Kansas City (Internal Delta Data)UsCommercial & Industrial Maintenance TechnicianAssociate $35,061.23
Kansas City51.0707Health Information/Medical Records Technology/TechnicianBryan - Kansas City (Internal Delta Data)UsElectronic Health RecordsAssociate $32,678.01
Kansas City51.0707Health Information/Medical Records Technology/TechnicianBryan College - Kansas CityUs $-
Kansas City51.0713Medical Insurance Coding Specialist/CoderBryan College - Kansas CityUs $-
Kansas City51.0801Medical/Clinical AssistantBryan - Kansas City (Internal Delta Data)UsMedical AssistingAssociate $32,270.49
Kansas City51.0801Medical/Clinical AssistantBryan College - Kansas CityUsMedical AssistingAssociate $33,686.96
Kansas City51.0909Surgical Technology/TechnologistBryan - Kansas City (Internal Delta Data)UsSurgical TechnologyAssociate $29,410.66
Kansas City51.0909Surgical Technology/TechnologistBryan College - Kansas CityCompetitorSurgical TechnologyAssociate $29,954.00
Kansas City51.1004Clinical/Medical Laboratory TechnicianBryan - Kansas City (Internal Delta Data)UsMedical Laboratory TechnicianAssociate $33,118.72
Kansas City51.1009Phlebotomy Technician/PhlebotomistBryan - Kansas City (Internal Delta Data)CompetitorPhlebotomy ProgramsDiploma $15,295.70
Kansas City51.1009Phlebotomy Technician/PhlebotomistBryan College - Kansas CityCompetitorPhlebotomistCertificate $15,150.99
Kansas City51.3501Massage Therapy/Therapeutic MassageBryan - Kansas City (Internal Delta Data)CompetitorMassage TherapyAssociate $21,648.00
Kansas City51.3501Massage Therapy/Therapeutic MassageBryan - Kansas City (Internal Delta Data)CompetitorMassage TherapyDiploma $12,500.00
Kansas City51.3501Massage Therapy/Therapeutic MassageBryan - Kansas City (Internal Delta Data)CompetitorMassage TherapyDiploma $12,500.00
Kansas City51.3501Massage Therapy/Therapeutic MassageBryan College - Kansas CityCompetitorMassage TherapyCertificate $12,500.00
Kansas City52.0301AccountingBryan College - Kansas CityCompetitorAccountingAssociate $34,851.90
Kansas City52.0401Administrative Assistant and Secretarial Science, GeneralBryan College - Kansas CityCompetitor $-
Kansas City52.0408General Office Occupations and Clerical ServicesBryan - Kansas City (Internal Delta Data)CompetitorBusiness AdministrationAssociate $35,959.59
Kansas City52.0901Business Administration Hospitality ManagementBryan College - Kansas CityCompetitorBusiness Administration- Hospitality ManagementAssociate $37,006.87
Kansas City52.1401Marketing/Marketing Management, GeneralBryan College - Kansas CityCompetitorBusiness Administration-Marketing OptionAssociate $37,006.87
Vegas11.1001Network and System Administration/AdministratorBryan - Vegas (Internal Delta Data)CompetitorNetwork Administration and SecurityAssociate $31,885.49
Vegas11.1001Network and System Administration/AdministratorBryan School of Bus. And Tech. - VegasCompetitorNetwork Administration and SecurityAssociate $26,768.50
Vegas13.121Early Childhood Education and TeachingBryan - Vegas (Internal Delta Data)CompetitorEarly Childhood EducationAssociate $31,721.21
Vegas13.121Early Childhood Education and TeachingBryan School of Bus. And Tech. - VegasCompetitorEarly Childhood EducationDiploma $25,072.50
Vegas22.0302Legal Assistant/ParalegalBryan - Vegas (Internal Delta Data)CompetitorParalegal ProgramAssociate $33,846.01
Vegas22.0302Legal Assistant/ParalegalBryan School of Bus. And Tech. - VegasCompetitorParalegalAssociate $27,856.50
Vegas43.0109Security and Loss Prevention ServicesBryan - Vegas (Internal Delta Data)CompetitorSecurity and InvestigationAssociate $33,964.64
Vegas43.0199Corrections and Criminal Justice, OtherBryan - Vegas (Internal Delta Data)CompetitorCriminal Justice ProgramAssociate $34,535.43
Vegas43.0199Corrections and Criminal Justice, OtherBryan School of Bus. And Tech. - VegasCompetitorCriminal JusticeAssociate $27,360.50
Vegas44Human Services, GeneralBryan - Vegas (Internal Delta Data)CompetitorHuman Services AssistantAssociate $32,402.82
Vegas44Human Services, GeneralBryan School of Bus. And Tech. - VegasCompetitorHuman Services AssistantDiploma $25,408.50
Vegas51.0707Health Information/Medical Records Technology/TechnicianBryan - Vegas (Internal Delta Data)CompetitorElectronic Health RecordsAssociate $32,678.01
Vegas51.0707Health Information/Medical Records Technology/TechnicianBryan - Vegas (Internal Delta Data)CompetitorMedical Billing and CodingDiploma $24,026.92
Vegas51.0707Health Information/Medical Records Technology/TechnicianBryan School of Bus. And Tech. - VegasCompetitorMedical Billing and CodingDiploma $24,122.36
Vegas51.071Medical Office Assistant/SpecialistBryan - Vegas (Internal Delta Data)CompetitorAdministrative ProgramsAssociate $32,090.82
Vegas51.071Medical Office Assistant/SpecialistBryan School of Bus. And Tech. - VegasCompetitorOffice Technology - MedicalAssociate $33,508.81
Vegas51.0801Medical/Clinical AssistantBryan - Vegas (Internal Delta Data)UsMedical AssistingAssociate $33,270.49
Vegas51.0801Medical/Clinical AssistantBryan School of Bus. And Tech. - VegasUsMedical AssistingAssociate $26,812.50
Vegas51.0909Surgical Technology/TechnologistBryan - Vegas (Internal Delta Data)UsSurgical TechnologyAssociate $31,313.94
Vegas51.0909Surgical Technology/TechnologistBryan - Vegas (Internal Delta Data)UsSurgical TechnologyAssociate $29,410.66
Vegas51.0909Surgical Technology/TechnologistBryan School of Bus. And Tech. - VegasUsSurgical TechnologyAssociate $26,054.00
Vegas51.1004Clinical/Medical Laboratory TechnicianBryan - Vegas (Internal Delta Data)UsMedical Laboratory TechnicianAssociate $33,118.72
Vegas51.1004Clinical/Medical Laboratory TechnicianBryan School of Bus. And Tech. - VegasUsMedical Laboratory TechnicianUnsure $26,812.50
Vegas51.3501Massage Therapy/Therapeutic MassageBryan - Vegas (Internal Delta Data)UsMassage TherapyAssociate $21,648.00
Vegas51.3501Massage Therapy/Therapeutic MassageBryan - Vegas (Internal Delta Data)UsMassage TherapyDiploma $12,500.00
Vegas51.3501Massage Therapy/Therapeutic MassageBryan - Vegas (Internal Delta Data)UsMassage TherapyDiploma $12,500.00
Vegas51.3501Massage Therapy/Therapeutic MassageBryan School of Bus. And Tech. - VegasUsMassage TherapyDiploma $10,600.00
Vegas52.0302Accounting Technology/Technician and BookkeepingBryan - Vegas (Internal Delta Data)UsAccountingAssociate $33,489.02
Vegas52.0302Accounting Technology/Technician and BookkeepingBryan School of Bus. And Tech. - VegasUsAccountingAssociate $34,851.90
Vegas52.0408General Office Occupations and Clerical ServicesBryan - Vegas (Internal Delta Data)UsAdministrative ProgramsAssociate $32,198.05
Vegas52.0408General Office Occupations and Clerical ServicesBryan - Vegas (Internal Delta Data)UsBusiness AdministrationAssociate $35,959.59
Vegas52.0408General Office Occupations and Clerical ServicesBryan School of Bus. And Tech. - VegasUsBusiness AdministrationAssociate $26,994.50
Vegas52.0901Hospitality Administration/Management, GeneralBryan - Vegas (Internal Delta Data)UsBusiness Administration Hospitality ManagementAssociate $36,392.96
Vegas52.0901Hospitality Administration/Management, GeneralBryan School of Bus. And Tech. - VegasUsBusiness Administration - Hospitality ManagementAssociate $26,944.50
Vegas52.1401Marketing/Marketing Management, GeneralBryan - Vegas (Internal Delta Data)UsBusiness Administration-Marketing OptionAssociate $35,671.88
Vegas52.1401Marketing/Marketing Management, GeneralBryan School of Bus. And Tech. - VegasUsBusiness Administration - Marketing OptionAssociate $26,944.50
Charleston12.0409Aesthetician/Esthetician and Skin Care SpecialistBusch-Moat Technical College-North CharlestonUsEsthetics TechnologyCertificate $10,960.00
Charleston12.0409Aesthetician/Esthetician and Skin Care SpecialistJJWT - Charleston (Internal Delta Data)UsEsthetics Technology ProgramsCertificate $10,970.00
Charleston12.0499Cosmetology and Related Personal Grooming Arts, OtherJJWT - Charleston (Internal Delta Data)UsCosmetology ProgramsCertificate $21,858.00
Charleston12.0499Cosmetology and Related Personal Grooming Arts, OtherJJWT - Charleston (Internal Delta Data)UsCosmetology ProgramsCertificate $21,878.00
Charleston12.0499Cosmetology and Related Personal Grooming Arts, OtherJJWT - Charleston (Internal Delta Data)UsCosmetology ProgramsCertificate $21,858.00
Charleston22.0302Legal Assistant/ParalegalBusch-Moat Technical College-North CharlestonUs $-
Charleston22.0302Legal Assistant/ParalegalJJWT - Charleston (Internal Delta Data)UsParalegal ProgramAssociate $35,633.76
Charleston43.0199Corrections and Criminal Justice, OtherBusch-Moat Technical College-North CharlestonUsParalegalAssociate $33,917.39
Charleston43.0199Corrections and Criminal Justice, OtherJJWT - Charleston (Internal Delta Data)UsCriminal Justice ProgramAssociate $31,139.28
Charleston51.0707Health Information/Medical Records Technology/TechnicianBusch-Moat Technical College-North CharlestonUs $-
Charleston51.0707Health Information/Medical Records Technology/TechnicianJJWT - Charleston (Internal Delta Data)UsElectronic Health RecordsAssociate $33,725.79
Charleston51.0707Health Information/Medical Records Technology/TechnicianJJWT - Charleston (Internal Delta Data)UsMedical Billing and CodingDiploma $27,728.91
Charleston51.071Medical Office Assistant/SpecialistJJWT - Charleston (Internal Delta Data)UsMedical Office AssistantDiploma $25,209.85
Charleston51.0801Medical/Clinical AssistantBusch-Moat Technical College-North CharlestonUsMedical Clinical AssistantCertificate $32,476.36
Charleston51.0801Medical/Clinical AssistantBusch-Moat Technical College-North CharlestonUs $-
Charleston51.0801Medical/Clinical AssistantJJWT - Charleston (Internal Delta Data)UsMedical AssistingAssociate $33,964.16
Charleston51.0801Medical/Clinical AssistantJJWT - Charleston (Internal Delta Data)UsClinical Office AssistantDiploma $25,950.07
Charleston51.0909Surgical Technology/TechnologistBusch-Moat Technical College-North CharlestonUsSurgical TechnologyAssociate $34,207.00
Charleston51.0909Surgical Technology/TechnologistJJWT - Charleston (Internal Delta Data)UsSurgical TechnologyAssociate $31,372.25
Charleston51.1009Phlebotomy Technician/PhlebotomistBusch-Moat Technical College-North CharlestonUs $-
Charleston51.3501Massage Therapy/Therapeutic MassageBusch-Moat Technical College-North CharlestonUsMassage Therapy $-
Charleston51.3501Massage Therapy/Therapeutic MassageJJWT - Charleston (Internal Delta Data)UsMassage TherapyCertificate $12,500.00
Charleston51.3501Massage Therapy/Therapeutic MassageJJWT - Charleston (Internal Delta Data)UsMassage TherapyCertificate $12,500.00
Charleston52.1403International MarketingBusch-Moat Technical College-North CharlestonUs $-
Charleston52.1403International MarketingJJWT - Charleston (Internal Delta Data)UsBusiness ManagementAssociate $32,873.04

<colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
No need to put up such a big chunk, just that much which illustrates one's problem.

Given that you want: "I am trying to find the maximum and minimum that match a specific set of criteria, e.g., location (column A), Code (Column B), Product name (Column C), Type (Column D), so for Las Vegas, Code 19200, Type 3, there might be 20 people that match this, but I want to find the maximum price (e.g., column F).", try...

Control+shift+enter, not just enter:

=MAX(IF(A2:A100="Las Vegas",IF(B2:B100=19200,IF(C2:C100=Product,IF(D2:D100="Type 3",F2:F100)))))

Adjust to suit (for e.g. Product).
 
Upvote 0
Aladin Akyurek said:
No need to put up such a big chunk, just that much which illustrates one's problem.

Given that you want: "I am trying to find the maximum and minimum that match a specific set of criteria, e.g., location (column A), Code (Column B), Product name (Column C), Type (Column D), so for Las Vegas, Code 19200, Type 3, there might be 20 people that match this, but I want to find the maximum price (e.g., column F).", try...

Control+shift+enter, not just enter:

=MAX(IF(A2:A100="Las Vegas",IF(B2:B100=19200,IF(C2:C100=Product,IF(D2:D100="Type 3",F2:F100)))))

Adjust to suit (for e.g. Product).
Click to expand...

Putting up the big example was intended to illustrate that I am not sure your formula will suffice, since I have 35 cities I have to manually type (I'd have to do an if for Las Vegas, Chicago, Cincinnatti, Miami, etc etc).

This becomes even more problematic when I try to actually loop through and pull the values that are within X% of my company's price for a given location/code/product name/etc...
 
Upvote 0
brandnew22 said:
Putting up the big example was intended to illustrate that I am not sure your formula will suffice, since I have 35 cities I have to manually type (I'd have to do an if for Las Vegas, Chicago, Cincinnatti, Miami, etc etc).

This becomes even more problematic when I try to actually loop through and pull the values that are within X% of my company's price for a given location/code/product name/etc...
Click to expand...

You need to have the data separate from the area where youbdo processing. If you put the criteria in cells of their own, you can copy down the formula...

Let A:F house the data.

Let K2, L2, M2, etc. house a given set of criteria, K3, L3, M3,... another set.

Q2, control+shift+enter, not just enter, and copy down:
Code: 
=MAX(IF($A$2:$A$100=K2,IF($B$2:$B$100=L2,
    IF($C$2:$C$100=M2,IF($D$2:$D$100=N2,$F$2:$F$100)))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Conditional sum help?
Replies
5
Views
5K
stanleydgromjr
S

Forum statistics

Threads
1,216,101
Messages
6,128,845
Members
449,471
Latest member
lachbee

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top