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

View 9 Replies


ADVERTISEMENT

Finding Row On Matrix Matching 3 Criteria And Get A Result?

Dec 11, 2012

The matrix:

Col A Col B Col C
2012-11-08 07:26:16Check-InJohn
2012-11-08 13:23:58Check-OutJohn
2012-11-08 13:24:01Check-InJohn

The goal:

Since i have a given:

1. Name(which exists in column C)

2. Date (which exists in column A - formated has (AAAA-MM-SS HH:MM:SS)

3. Event (which exists in column B)

4. Hour*

I want to know the nearest hour that matches all criterias.

In the example on top, if i give:
John, 2012-11-08, Check-In, 14:00:00
I want the result: 13:24:01

If, for example, the date doesn't exist, the result could be a simple error message.

View 3 Replies View Related

Result Based On Existance: If Coulmn Contains A Anywhere Then The Result Should Be A

Dec 4, 2009

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.

View 4 Replies View Related

Value Exceeding Target To Be Negative?

Dec 24, 2012

I'm working on a spreadsheet to measure performance.

Target is 10% and below

If actual is below or equal to 10%, then 100% achievement

If actual is above 10%, From 11% to 20%, then achievement is 90% and so on down until 0%

If actual is beyond 20%, then achievement should not exceed 0%

View 5 Replies View Related

Trying To Sum 4 Cells Where 1 Is Multiplied Without Exceeding A Value

Jan 17, 2010

im trying to sum the cells C20:F20 while having cell E20 multiplied by cell C4 without having the product exceed 50.

View 9 Replies View Related

Delete Only Zero Value From A Cell Which Is Exceeding 15 Digit

Apr 8, 2013

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.

Like: AJSPP00000125467 change to AJSPP0000125467.

View 5 Replies View Related

Scheduled Work Exceeding Available Time

Jan 20, 2013

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.

Attached File: GKirbywork overload.xlsx

View 4 Replies View Related

Populate Blanks In Column Without Exceeding Last Row Of Data?

May 15, 2014

I need to populate blanks in a column without exceeding the last row of data

View 5 Replies View Related

Exceeding 3 Criteria Limit In Conditional Formatting

Jun 6, 2008

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

View 3 Replies View Related

Change Background Color When Exceeding A Certain Number In Cells?

Apr 24, 2009

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

View 4 Replies View Related

Sum Col B Based On Col A Value With Result In C?

Nov 30, 2013

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.

AB
553
477
5515
428
4710
476
4713
488
420
1812
5514
4815

View 3 Replies View Related

Get A Result Based On Several Different Conditions

May 20, 2009

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

View 6 Replies View Related

Get Another Value Of Row Based On DMIN Result?

Sep 9, 2012

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?

View 7 Replies View Related

Unhiding Text Based On Result?

May 8, 2014

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.

View 6 Replies View Related

Return 1,2 Or 3 Based On Result Of Formula

Mar 3, 2007

Any other day but today (I've been starring at numbers too long) I could do this.

I need a simple formula that will return:

a. 3 if resut is >= $2.5M
b. 2 if over >= $0.5M
c. 1 if

View 9 Replies View Related

Result Based On ComboBox Choice

Jul 25, 2006

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)

View 9 Replies View Related

Return Result Based On Criteria

Oct 27, 2006

if Cell F42 contains 50%, then my fomula returns "wrong"

=IF(AND(0%<F42,F42<50%),"F",IF(AND(50%<F42,F42<60%),"D",IF(AND(60%<F42,F42<70%),"C",IF(AND(70%<F42,F42<80%),"B",IF(AND(80%<F42,F42<100%),"A","wrong")))))

how do I get the formula calculate correctly?

View 7 Replies View Related

Run Macro Based On SUM Function Result

Jan 27, 2008

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

View 4 Replies View Related

Result Based On Two Criteria Lookup

May 28, 2008

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?

View 4 Replies View Related

Finding MAX Value From Row Based On Value From Another Row

Jul 16, 2014

I want a formula to find the Max value in a row based on the condition from a different row.

Example:

I want to get the highest value from row 2 that also has the value A from row 1.

A
B
C
A
A
B

1
3
5
2
5
6

This would result with 5.

I have tried using MAX and IF, INDEX and MATCH but cannot get it to work.

View 5 Replies View Related

Formula To Calculate Result Based On Range

Dec 13, 2012

I have a scorecard that looks something like this:

90% or greater=3
80%-89%=2
70%-79%=1
0%-69%=0

I need the "cell" to react accordingly and I'm lost.

View 2 Replies View Related

Return Result Based On Multiple Criteria

Sep 19, 2009

I have been trying this for hours but to no avail.

I have a table with 4 columns headers
Name, Amount, Loc and Code

The name may look like ABC 1, ABC 2....
The Loc may be in US, GB...
and the Code may be AA, BB

I need to return a result "Y" if the sum of the amount is > 100
and "N" if the sum of the amount < 100 based on the conditions
of the following :if

1) Name is the same entity, such as ABC 1 and ABC 2 and
2) Loc is the same, US..and
3) Code is the same

I have attached a sample to illustrates the result

View 10 Replies View Related

Drop Down List Based On The Result Of A Vlookup

Mar 5, 2010

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.

View 9 Replies View Related

Create Autoshape Based On Formula Result

Aug 1, 2006

how to have a cell equate to an autoshape based on the results of an "if" statement?

View 4 Replies View Related

Return Result Based On Lookup Value Returned

Oct 17, 2006

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.

View 5 Replies View Related

Color Formula Result Based On Criteria

Jan 4, 2007

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.

View 2 Replies View Related

Return Result Based On Time In Cell

Oct 2, 2007

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.

View 9 Replies View Related

Return Result Based On Text In Cell

Oct 26, 2007

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:

Warranty Replacement = 20
Insurance Claim = 20
Billing Issues = 15
Retention Opportunities = 20

View 5 Replies View Related

Return Text Based On Calculation Result

Dec 18, 2007

I need an excel function for this...

Say,
Cell A2= 8
Cell A3= 1

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?

View 2 Replies View Related

Hide Cells Based On Another Cell Result

Jan 12, 2008

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.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved