Interesting Function/Formula Question

CAW

Board Regular
Joined
Jul 1, 2004
Messages
173
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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