Hi,
I have relatively basic knowledge of Excel, so bare with me. I am doing a project that involves picking a set of 3 items from a list. Basically, I have to try and pick the combination of 3 items that is under $18,000 that has the highest combined rating. I have attached a screenshot of a sample of the table of values that I am working with below. Is there any way to do this on Excel? Doing it manually would take forever. I really appreciate any help I can get.
Test.png
Thanks!
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Thanks for your response. I'm not 100% sure what you meant but I think you wanted me to upload a sample Excel file that includes a sheet with my data and a sheet with example results that I want. Basically I need to choose three items that amount to $18,000 or less that have the highest rating.Test.xlsx
Hi jack and welcome to the forum,
I love these types of problems because they are easy if you know how to program. Here is the scheme.
1. Add a helper column to the right of your table that is random numbers and will recalculate frequently.
2. Sort by this new column to get 3 random rows to the top of the table
3. Copy these 3 rows to the right of the table and add the dollars and values
4. Check to see if these three cost more than 18000 and if their value is greater than the best previous guess.
5. If this guess is better than the best last guess, copy the answer down to the lower table.
6. Build a loop so you can do this LOTS of times.
To use my example, click on the button and put in a number like 20. The above will try 20 random sorts of the data and give you the best answer after 20 tries. Want to get a better guess? Put in 1000 instead of 20 and wait a minute.
See the attached and look at the code in the module. Easy Peasy??
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi Marvin,
Thanks so much for your help. The only thing I am unclear about is when you say "click on the button and put in a number like 20." What button is this and how does it I make it do this automatically as manually doing it would take forever. Also, what do you mean by building a loop?
Thanks!
Last edited by jackbad; 08-05-2015 at 10:54 AM.
Hey Jack,
BTW, the answer you have on sheet 2 is wrong. You show the following but it adds to 25 not 26.9.
B 10.7 $7,500
I 6.8 $4,600
F 7.5 $5,500
26.9 $17,600
but it adds to 25 not 26.9.
Hi Jack,
I've gone down the approach of generating all possible combinations of 3 items from the list you have there using formulas (as you can see attached file, sheet OUTPUT)
Columns A:C generates all possible combinations of 3 items from a list of 18.
Note: This can be extended by finding all "18s" in the formula and replacing them with a different number. Then use the formula =COMBIN(NewNumber , 3) to figure out how many more rows you need to add to the bottom of the list.Formula:
A1 = 1, B1 = 2, C1 = 3
A2: =IF(B2=18-1,A2+1,A2)
B2: =IF(C2=18,IF(AND(C2=18,B2=18-1),A3+1,B2+1),B2)
C2: =IF(C2=18,B3+1,C2+1)
Columns E:G converts them into alphabets, as per your table using a simple INDEX. Same with columns I:K (prices) and N:P (ratings)
Formula:E2: =INDEX('Sample Data (Before)'!$A$2:$A$19,A2)
I2: =INDEX('Sample Data (Before)'!$C$2:$C$19,A2)
N2: =INDEX('Sample Data (Before)'!$B$2:$B$19,A2)
I then calculate find the largest rating that are below 18,000 using a MAX IF on column L (sum of prices) and column Q (sum of ratings)
Formula:=MAX(IF($L$2:$L$817<=T$2,$Q$2:$Q$817))
Note: This is an array function, and as such, as you type the formula, hit CTRL SHIFT ENTER to finalise the formula rather than just enter.
To address the possibility of multiple solutions, I've also prepared a table to give you 5 possible solutions using
Formula:U7 (row number of solution) =IF(T7<=$T$5,LARGE(INDEX(($L$2:$L$817<=$T$2)*($Q$2:$Q$817=$T$4)*ROW($Q$2:$Q$817),0),T7),0)
where T5 is a COUNTIFS of how many solutions there are
T5: =COUNTIFS($L$2:$L$817,"<=" & $T$2,$Q$2:$Q$817,$T$4)
and finally extracting out the solution
V7: =IF($U7=0,0,INDEX(E$2:E$817,$U7-1))
W7: =IF($U7=0,0,INDEX(F$2:F$817,$U7-1))
X7: =IF($U7=0,0,INDEX(G$2:G$817,$U7-1))
Or you can just see attached.
P/S: Clearly, I have too much free time to kill today.
Hi quekbc,
I'm so glad we both got the same answer. I didn't see how much that 3 players cost but an easy step to look up their prices now that you have their letters is trivial.
AND - yes you must have too much time on your hands today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks