# 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: 423
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: 377
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: 710
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.