# Need some help with a formula in Excel Please!

D

#### Darren Durney

Hi All

I have put together a simple spreadsheet for monitoring Supplier Performance on a Quarterly basis, the problem i am havbing is i want to reference some cells but am unable to stretch my brain that far. I have most likely overlooked it but for some reason it is grinding me and i need to finsh this off as quick as possible so that I can get leeters sent out to our woprst suppliers.

Anyway I have attached the file and highlighted in different colours what I need.

I have in Column D (marked red) % figures of rejections against deliveries

I have in Column B (Marked blue) Figures for delivery

I have in Column A (marked green) Supplier Names

What i want to do is create a formula to take the % figure and look for deliveires greater than 25 then to calculate the top 4 out of this and allocate the supplier names in a seperate box

I hope this helps but I am lost myself i know what i want to do on it so if i have confused you let me know and i will try and explain again if i can

Cheers

Darren

#### Attachments

• 81 KB Views: 493
Last edited by a moderator:
T

#### True Position

I just spent about a half hour working on this, it seems you want something like =Offset(Large(D2 : D26, 1), 0, -3) but that doesn't work since large returns the value, not the address.

Perhaps just sorting the data using sorting function is easiest then taking the top 4 values and conditional formatting for more or less then 25%.

#### Tim Folkerts

Super Moderator
Have you considered just doing it "by hand"? You only seem to have a few dozen rows of data. It may be easier to look through the list and determine the ones you want than to write an equation.

Of course, the equation will remove the possibility of human error (mistakeproofing) and would become more and more helpful as the rows or data got longer and longer.

Tim F

T

#### True Position

Here's a terrible way to do it, I created a sheet which reads the column titles and percentage NC. You'd need to select each quarterly section(Suppliers and Percent NC), data/sort/percent NC/descending.

This will give you a descending list of each quarter's worst suppliers. Then I feed back the top 4 of each quarter to the 2009 page and conditional format anything from .25 - 1 with a red background.

Note, I changed a few data sets added a sample, etc.

#### Attachments

• 92 KB Views: 431
Q

#### qualitymanager

I have a solution, and have tested it using varying percentages in the 1st qtr percentage column.

Calculation done from D19:G22.

Results in B26:B29.

I have done the "hard work" - you can format, move around data and make it look pretty.

Thanks for an interesting (and learning) exercise which took me about 1 hr 15 mins on a Saturday afternoon (which I did instead of "paying work").

If you have questions, please go read Excel help on all the formulae used first. If that is not adequate, then PM me, or post and another Cove member may reply before I do.

#### Attachments

• 78.5 KB Views: 777
T

#### True Position

Wow very nice! I'm going to save that excel file in case I ever need to do a similar trick in the future.

D

#### Darren Durney

Wow

I dont know where to start Thank you very much all of you for your help it is much appreciated.

The formulas are great and work which is fantastic.

Trusted Information Resource
Excellent Excel example. I too will save for a later date. Thanks!

A Cpk Formula seems off, need help!? Capability, Accuracy and Stability - Processes, Machines, etc. 11
C I need help verifying formula's in a database for LCL UCL Cp Cpk PPk Statistical Analysis Tools, Techniques and SPC 14
Need Help With API Q2 Quality Manual Oil and Gas Industry Standards and Regulations 8
Need some Help on 8D fault tree analysis Problem Solving, Root Cause Fault and Failure Analysis 6
Screen printing ink and machine selection_ Need help Manufacturing and Related Processes 6
Need Help with FPY Data in Assembly Process Manufacturing and Related Processes 7
Need help on "Country of Origin" Medical Device and FDA Regulations and Standards News 0
Need Help With Information Security Asset Risk Register IEC 27001 - Information Security Management Systems (ISMS) 2
ISO/ IEC 17021 Resource requirement (need help) Document Control Systems, Procedures, Forms and Templates 5
AS9100 new certificate, but need QMS software, help Quality Assurance and Compliance Software Tools and Solutions 2
Need help with analysing a survey on minitab Using Minitab Software 1
A proposal for the model Quality Management - I need help for the project ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 3
Probability - Need a help to solve the below question Statistical Analysis Tools, Techniques and SPC 5
Need Help - Runout - Function Gage Inspection, Prints (Drawings), Testing, Sampling and Related Topics 7
New to FAIR, need help in filling it out AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 8
Pls help --- Need expert advice on Video Measurement Measurement Uncertainty (MU) 0
Need help to understand HIPAA requirements ISO 13485:2016 - Medical Device Quality Management Systems 17
Z I need help with getting a CFS in China Manufacturing and Related Processes 2
S Need help wrapping my head around confidence vs beta error Statistical Analysis Tools, Techniques and SPC 2
P Received a minor for not having good measureables/goals. Need help with KPIs. IATF 16949 - Automotive Quality Systems Standard 52
A Failed the AS9120B Exam Twice.....Need Help AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 11
Need help in Conditional Formatting for the entire row Excel .xls Spreadsheet Templates and Tools 3
Need help on Quality Improvement Plan (QIP) Quality Tools, Improvement and Analysis 5
Help: Need Control Self Assessment Questionnaire for HR Departmental Functions Process Audits and Layered Process Audits 0
Need help in Calibration of Directional Coupler General Measurement Device and Calibration Topics 2
Need HELP with Internal Audit Program ISO 13485.2003 Quality Management System (QMS) Manuals 3
J Supporting Processes - Internal Audits - Need help settling a debate IATF 16949 - Automotive Quality Systems Standard 4
A Need Help With AEA Auditor Related Situational Questions AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 1
Transformer Overload Test - Need help IEC 60601 - Medical Electrical Equipment Safety Standards Series 3
C I Need Help with Drafting Conventions!!! Design and Development of Products and Processes 4
M Need help on QMS Development in an AS9100C certified organization AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 10
Need Help on Analysis: How to know potential causality model from historical data Problem Solving, Root Cause Fault and Failure Analysis 5
B Need help tracking training for my business Quality Assurance and Compliance Software Tools and Solutions 3
B Need help with a Control Plan for Encapsulation Mold / Trimming / Forming Tooling FMEA and Control Plans 9
S I need help from all of you bros Oil and Gas Industry Standards and Regulations 1
Need help identifying this "thing" Coffee Break and Water Cooler Discussions 9
G Need help on Device Description in the Acceptance Checklist for Traditional 510(k) Other US Medical Device Regulations 2
K Need help finding a book on Documentation Book, Video, Blog and Web Site Reviews and Recommendations 8
J Need help/advice on video editing software etc..... Coffee Break and Water Cooler Discussions 8
L Need Help with Case Study Questions Manufacturing and Related Processes 16
W Help explaining the need for the Process Approach AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 4
Unusual problem with Excel - Need some help Excel .xls Spreadsheet Templates and Tools 7
Need help on new TS 16949 Rules IATF 16949 - Automotive Quality Systems Standard 5
Need help with Network Analyzers and which DUT can be Calibrated with it Calibration and Metrology Software and Hardware 2
R Need help on defining scope for Design Verification File for Class III IVD 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 8
J Help I need your opinion on a News Letter Misc. Quality Assurance and Business Systems Related Topics 15
N Need Help Preparing for Medical Device Quality Engineer Interview Career and Occupation Discussions 2
B Need help on an application we are working on Coffee Break and Water Cooler Discussions 4
S Need help with Project Charter for my Six Sigma Green Belt Project Six Sigma 1
Q Need help on MSA study on a torque analyzer Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3