# Interesting Function/Formula Question

#### CAW

##### Board Regular
My supervisor has asked me to do something with an Excel spreadsheet he has that shows part numbers and ranking based on certain info. Since I have no idea what he’s really looking for, I asked him to explain it in as simple of a format as possible. I know understand what he’s looking for, but I have no idea how to do it in Excel, or if it’s even possible in Excel.

Any ideas would be greatly appreciated. Here we go:

Column A -- Independent Number on Each Candy - there at 15 candies numbered 1 - 15 all with different Candy Sizes

Column B -- Size of Candy - also independent - measured after the candies are numbered

Column C -- Candies ranked by size - 1 is biggest, 15 is smallest - dependent on Candy Size ( he’s using =RANK(B3,\$B\$3:\$B\$17) )

Column D -- Independent Childrens' Names

Column E -- The rank, by Candy Size, this child should get - the nicest kid gets the largest candy (rank = 1 but not Candy Number) and Mike is nicest. Amy is meanest so she gets rank = 15 - again this is independent

Column F -- This is what I (my supervisor) cannot figure out how to do in Excel. I can figure it out in my head - Mike gets candy number 5, Tom gets candy number 11, Deb gets candy number 10, etc. Can something be used here where Excel will figure it out anytime any new numbers get entered into this chart?

Column G -- I know how to do this, it tells me the size of the candy to give the kid but not the Candy Number ( he’s using =LARGE(\$B\$3:\$B\$17,E3 )

We’re using Excel 2003 here, btw.

Again, thanks a lot.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

##### Well-known Member
Hello,

as there are only 15 rows and 7 columns, could you supply all the data, even if you just type it out.

I'm struggling with how Mike is the nicest.

#### yee388

##### Well-known Member
I'm not sure how you're getting a "nice" factor for the kids... are you entering the candy size rank manually in column E? If so, I think you'll have to change all the numbers when you add new candy to the chart.

A solution (if I understand what you're asking) is to have a "nice" table somewhere else, so you are using a lookup to determine which candy number (based on size rank) each kid gets. Perhaps if you post an example sheet, I can help more.

#### CAW

##### Board Regular
Hello,

as there are only 15 rows and 7 columns, could you supply all the data, even if you just type it out.

I'm struggling with how Mike is the nicest.
You're correct. Here's the data...

1 5.00 12 Mike 1 22.00
2 1.00 15 Tom 10 8.00
3 15.00 4 Deb 6 12.00
4 4.00 13 Ann 13 4.00
5 22.00 1 Julie 9 9.00
6 7.00 11 Ashley 2 20.00
7 9.00 9 Dan 11 7.00
8 11.00 7 Scott 4 15.00
9 16.00 3 Jen 14 2.00
10 12.00 6 Barb 7 11.00
11 8.00 10 Terri 3 16.00
12 2.00 14 Don 12 5.00
13 10.00 8 Jess 5 13.00
14 13.00 5 Amy 15 1.00
15 20.00 2 Cory 8 10.00

#### yee388

##### Well-known Member

Like this?
Book1
ABCDEFG
1CandyIDCandySizeRankChildNiceFactorCandySizeforChildCandyIDforchild
21512Mike1225
32115Tom10811
43154Deb61210
54413Ann1344
65221Julie997
76711Ashley22015
8799Dan1176
98117Scott4153
109163Jen14212
1110126Barb7118
1211810Terri3169
1312214Don1251
1413108Jess51314
1514135Amy1512
1615202Cory81013
Sheet1

Still not exactly sure what you mean by "new numbers into the chart"... are you updating/changing values, or are you adding more rows?

#### CAW

##### Board Regular
Still not exactly sure what you mean by "new numbers into the chart"... are you updating/changing values, or are you adding more rows?
Sorry...updating/changing values.

Edit: Yes, your example looks like what I have, except that my column F is blank.

#### yee388

##### Well-known Member

So do you have what you need?

#### CAW

##### Board Regular
So do you have what you need?
Oh jeez, I didn't even notice that you had something in column F.

Let me ask and post back in a bit.

Thank you.

#### CAW

##### Board Regular
So do you have what you need?
My super just gave me the thumbs up.

I'll save this for future reference.

Thanks a lot!

Replies
1
Views
186
Replies
5
Views
83
Replies
9
Views
276
Replies
0
Views
412
Replies
7
Views
290

1,141,871
Messages
5,709,097
Members
421,614
Latest member
RAB29

### 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.

### Which adblocker are you using?

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

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