Finding Best 5 Based On Result Value Without Exceeding 100
Feb 13, 2007
Each car has a value, and each car has odds.
I must choose 5 cars, and the value cannot exceed 100.
I typed in all my data, can Excel choose the best possible lineup for me?
In the end, I need the lowest result possible while only using 100 pts in
value.
I am using Excel 2003
Here is a sample of my data.
Car # Value Odds (#to1) (Result=Value x Odds)
123.5494.00
223.45117.00
322.85.75131.10
422.66.75152.55
522.212.5277.50
62315345.00
71523.5352.50
82217.5385.00
921.820436.00
1019.523.5458.25
1121.623.5507.60
122125525.00
1321.725542.50
141930570.00
1522.130663.00
1620.335710.50
1721.934744.60
182040800.00
1918.845846.00
2018.647.5883.50
I have 4 categories A, B, C & D. These are in desending importance, means A is most important and D is least important. Now there could be many A, B, C & Ds listed in a column. The challange is if coulmn contains A anywhere then the result should be A. If A is absent, then search for B, if present anywhere then display the result as B. It doesn't matter how many times A or any character is listed in column. I am attaching a sheet for better explanation.
I have some alphanumeric mixed data which is from 15 digits to 18 digits. In each data there have 5 zeros in the middle. I want to keep all the alphanumeric to 15 digits only. It will delete the zeros not any other value which length is greater than 15 digits. If a length of a character is 17 digit it will delete 2 zeros & if it is 15 digit then keep it constant.
I have attached a portion of a schedule I have been asked to update to show when more hours are scheduled for a time period than are available on the schedule.
Each Dept.(Man. and DCC) has 16 hours available per day, M-F.
How can I chart or show somehow when a conflict arises? I need to outsource the excess time to another facility in a timely fashion.
The code segment from "Get Around Excels 3 Criteria Limit in Conditional Formatting" works perfectly if I type the variable directly into the cell, but not in a range of cells. That is to say, if I copy the source cell to a range of cells I receive a "Run-time error 13: Type mismatch" error". Is there a way to get around this? This is the code segment I've tried in the worksheet.
Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("b2:q53")) Is Nothing Then Select Case Target Case "IBBCH", "ibbch" icolor = 36 'light yellow Case "OBBCH", "obbch" icolor = 34 'light turqoise Case "OBBRDG", "obbrdg" icolor = 35 'light green Case "LNCH", "lnch".........................
I got an excel sheet were in column A i have listed a few names and in column B I got their respective results (numbers) with a background color.
I want to insert a function in such a way that when their numbers exceed a certain number the background color changes from for example blue to green. so >1200 = blue background color 1200-1350 = red < 1350 = green
What would be the formula in C to sum the numbers adjacent to A where the 47's in A = 36 from all the adjacent numbers in column B, all the 55's = 32 from all the adjacent numbers in B, and 18 = 12 as it is the only number adjacent to 18 in B.
When code is run the numbers will change in both columns (random numbers) so the next time there may not be any 47's or 55's in A. And new numbers in B to sum.
As I ponder this, I'm thinking it may require code, which is okay.
New to the forum and in need of a bit of help. Friend of mine is in here regularly looking for assistance and tells me this is the best excel forum around. Thought I would put it to the test with a problem that is doing my head in.
The following is a table which shows a salesmans sales. He or she has sold to 8 customers.
What I need to do is show in the commission column how much they should get paid for the additional products based on this criteria.
1. If the penetration is equal to or over 41%, and he has sold the additional product for max profit of 250 then he should get �40
2. If the penetration is less than 41%, and he has sold the additional product for max profit of 250 then he should get �25
3. If he hasnt sold the product for full amount then he gets 10% of the profit, regardless of the penetration ...
I've got a database with 8 columns and many records. The task is to find the value in column 3 for the smallest number in column 7 with additional criterias for columns 4 & 5.
I've used the DMIN function for finding the smallest number, but I don't know how to show the corresponding value in column 3. I think I should only use DB functions. (I've checked and there is exactly one line with the set of criterias above, so there should be no errors.)
Is there any way I can specify to DGET that I want to get the minimum of a column but based on the other criterias? Or embed DB functions into one another?
see the sheet attached. To put into context, I am a teacher and want students to work on tasks, and have the answers to those tasks available to them but hidden, until a give them a password that unhides it.
On the attached document, Lesson1! K35:Q41 i have some answers to the task beside it in B35:H40. I want to hide these answers in some way (without affecting the columns as there is data above) and then when I want students to see the answer I give it to them and they enter it into L43 in this example. At this point the answers are revealed and stay revealed for revision purposes.
This will be repeated using different passwords for different tasks throughout the workbook e.g. L52 will be a different password revealing the answer in K48.
I'm looking to set up a combo box with different year options and add functionality so the data that can be seen on the sheet at a given time is driven by the combo box year value. (functionality somewhat like a webpage, where once you choose a certain value from a drop down box, you see data corresponding to the value)
i have the formula "= sum(A1:A10)" in cell A4 & would like to call macro when the value changes. The code i'm using below work's if I manually type in a value, but isn't working with the formula.
Private Sub Worksheet_Change(ByVal Target As Range) ' Checks for cell value change If Intersect(Target, Range("A4")) Is Nothing Then Exit Sub ' If cell value change, calls msgbox based on criteria Else If Range("E4").Value = "C" And Range("A4").Value > "30" Then Call MsgBox1 End If If Range("E4").Value = "F" And Range("A4").Value > "38" Then Call MsgBox2 End If End If End Sub
See the attached file. I think index and match may help me having done a search several times in these forums, but having been trying to do this for several hours I am just struggling to understand each part of the formula unfortunately. I have a list of data (cellsA1 to C33) which I need to use as my source for the information that is displayed in cell I2 based on the two selections made in cells F2 and G2.
I can't use filter and data sort in this situation - I am creating a spreadsheet for someone who truly knows nothing about excel, so I need this to be as simple as pointing and clicking at cells F2 and G2 and the formula doing all other work. Is it possible to do what I am asking for (hopefully the fact that peoples' names are repeated and months are repeated won't cause a problem?
I am trying to make a drop down list based on the result of a vlookup.
What i want to do is look into a table that has country name, depot station, but i have more than one depot per country so when i look up with vlookup i only get one result back, the table looks like this.
Country Country nameDepot code Depot name 1 GBUnited Kingdom STN Stansted 2 GBUnited Kingdom EDI Edinburgh 3 GB United Kingdom EMA East midlands 4 FR France GNO Garanoa 5 FR France MRS Marseille
How can i look up GB or United Kingdom and get all the depots listed from that country.
I have four columns, A through D Column C is returning a simple vlookup of A I need Column D to return a value where C is TDMA return TDMA or when C is GSM lookup column B compare to tab2 (columns A through L) returning column 12.
I would like to use formulas to display different results eg UNDERBUDGET - OK - OVERLIMIT etc. Ideally they would be shown using different colours & text size. I can't seem to change the colour/text size inside the formula for the different words - only for the whole cell.
I have a table of repair jobs done over a period of time. I just need to determine during which shift was the repair job done. The time during which the job was initiated is called Notification Time.
If the job was done at, say, 0100 hrs, then it was done during Shift 1 (0000hrs to 0800 hrs) If done at 0830 hrs, Shift 2 (0801 to 1600hrs) If done at 2030 hrs, Shift 3 (1601 to 2359 hrs)
I need a formula to allow me to determine the Shift no. by just checking against the Notification Time column.
I have a spreadsheet where in cell E2 there is a drop down box with the following options to select: Warranty Replacement, Insurance Claim, Billing Issues, Retention Opportunities.
In cell F2 is where wait time minutes are generated depending on what is selected from the drop down box in E2. I am trying to create a nested IF formula for the following scenario:
If A2 < (9*A3), then Cell A4 will say "Plastic", otherwise it will look to the next function, which is If A2 < (10*A3), then A4 will say "Compact", otherwise, it will look to the next one which is If A2 < (15*A3), then A4 will say "Semi-Compact" otherwise If A2> (15*A2), it'll say "Slender". so at the end, Cell A4 can only be one of these: 'Plastic' or 'Compact' or 'Semi-compact' or 'Slender'. How can i combine all the functions together?
I would like to gray out or hide contents of a series or group of cells when a particular cell has a certain result. i.e. if cell A1 is <5 I want cell rows 5 thru 10 to be hidden or grayed out.
