1. The most amount of wins
2. if two or more people tie the # of wins then the person with the lowest tiebreaker guess wins and the other person becomes the next place.
3. If two people tie the number of wins and the tiebreaker for say 1st place the next highest win and lowest tiebreaker should place 3rd since there are two people above him.
Attached is a spreadsheet of the problem. The solution must only use the info in the "Given" section. You may reorder the data (rows moving up or down, not columns). Solution required is in Yellow.
I could figure out the logic IF there is a way to do a multi conditional hlookup. So if you know of a way to use an "And" in the condition and pull up one row as a result, i can probably figure out the rest.
I have attached a document paralleling a document I am working on. The dollar amount in each spreadsheet represent sales. I have entered in values into the candy, soda, and chips spreadsheet. I have also linked values for candy into the total spreadsheet. My question is can I somehow type something or drag the formula down to populate the other cells in the total spreadsheet?
The idea I am thinking but which I don't know how to implement is to list all the items (as in column G) and list all of the relevant cells (e.g. B1 in the Candy spreadsheet) as in columns H and I (Note that all items will have the same cells but the cells will have different values...e.g. all three items have a cell B1 and B2 in their spreadsheet but these cells contain different values). I then try and fail to create a formula in cell B3 of the Total spreadsheet. I am trying to create a formula of the following nature:
='(Spreadsheet Name From Column G)'!(Cell Name From Columns H and I)
The Second half of the formula doesn't really concern me (i.e. the cell name from column H and I). However I am perplexed as to how to achieve the goal in the first parentheses above.
I run a web query to populate a table in my spreadsheet. The data is dynamic - and on some days, I will get a couple of extra rows in the table (its weather data, so e.g. when its raining, they will forecast an extra row of how many mm, etc...)
To give a simple example, right now I use Hlookup to locate the value of F3 (the date) in my dynamic range of B26:G26 and step down 3 rows, where my needed temperature values are for that corresponding day. This works great like this already: =HLOOKUP(F3,B26:G29,3,FALSE)
The big issue is...what happens if my target values are not always 3 rows down? If the query refreshes with any extra rows, this reference is shifted. (Well, my starting "Anchor" is shifted)
The issue I have is that in sheet2 the HLOOKUP range i want to lookup will not always be in R60:R61 these rows vary depending on the amount of data above this range... is there a way of incorporating some sort of
How to do a ranking for productivity based on goals in six categories for my employees. I want to be able to rank them on these key categories and rank them based on the best average. For instance:
Employee #001 Ranked 1st in Sales per Hour, 3rd in Avg Transaction Amount, 2nd in Multiple Sales, 5th in Accessory Percentage Employee #002 Ranked 2nd in Sales per Hour, 1st in Avg Transaction Amount, 1st in Multiple Sales, 4th in Accessory Percentage
I want to factor against the goals to determine each individual rank for each category and then rank based on the combination of them all.
I haven't gotten to all the additional conditional formatting but recall how to do this with ease, but the sheet in its basic format is present in the attachment below.
2nd File Location: U:My Work FileName: 333.xls Worksheet Names: Pro _Ser1 Ser1 (A1) value= db Ser1 (B1) value=1
What I want is a master spreadsheet to 'suck up' data from all the Ser sheets within all Workbooks and populate the master spreadsheet with Column A =A1 Values from all the Workbook, Worksheets Column B=B1 Values from all the Workbook, Worksheets
i'm trying to use HLookup to find an adjusted midterm grade that's given. but i have some conditions:
If student missed exam and has a zero – keep zero. If student has a grade of 1-119 points, increase their grade 40 points. If student has a grade of 120-125 points, increase their grade 35 points. If student has a grade of 126-131 points, increase their grade 31 points. If student has a grade of 132-139 points, increase their grade 27 points.
with these conditions, if my midterms grade is 120, how would i calculate it using HLookup? i worked on it but i keep getting the #NA! error. =H4+HLOOKUP(H4,B24:D25,2).
I make budgets that have line items that have standard price based on 2 factors: a description, and a number. I want to keep these items in 2 different cells. I also have different clients, and the costs are different. I could simplify so that I can use a VLOOKUP with HLOOKUP and separate the clients but I would prefer not to.
Is there a quick way to check if a variable is an array or multi-dimensional? I am trying to send variables to a sub to print to excel but I am sending mixed variables - some are one dimension, some are two dimensional.
Here is what I am using:
Private Sub Send(Item As Variant, Top As Integer, ToSheet As String, Row As Integer, Column As Integer)
'Sends values to Excel
' Loop counter Dim i As Integer
For i = 0 To Top - 1 ActiveWorkbook. Sheets(ToSheet). Cells(i + Row, Column).Value = Item(i) Next
End Sub
I suppose I could set up a second routine to handle one dimensional variables, but I thought I would ask the pros -
I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.
Ranking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.
I am facing some problem with ranking formulae. Attached is a file illustrating them.
Problem 1: Ranking without zero I have a set of data for products. They have a number next to it. Some of the products have a ZERO value next to them. I want to rank the data in an ascending order while ignoring the products with ZERO value next to them.
Problem 2: Ranking on multiple factors I have a set of data for 50 products. There are 5 factors based on which I want to pick the best and the worst. Each factor has a value which can be a negative number, zero or positive number.
This is what I am doing presently: Ranking the products on each factor in a separate column. Points are assigned based on the ranking, i.e. if the ranking of "product A" on Factor 1 is 32, then it gets 32 points It is assumed that equal importance [weight] is given to each factor, thus average points is calculated based on ranking of each product on each factor. Average points are again ranked in descending order The top & bottom 10 are picked from this ranking.
Number of products and number of factors can be different for each analysis. Presently I am giving equal importance to the rankings for each of the factors, what if I have to give varied importance [weight] to the factors.
I have a listbox with 8 columns. Multiselect is enabled, and it must stay this way. As part of my program, after the user presses a command button, I need to use the row indexes of the selected rows in order to copy the selected information into an array which is then placed in a different listbox, and then delete the items from the original list. Pseudocode of what I want to do:
[Code] .....
But my understanding is that .ListIndex does not work this way with multiselect listboxes. I've tried searching for a solution for a while, but I cannot find one.
I have a dropdown box containing text strings. How can I use the selection from a combo box as part of a formula?
So for example I have a spreadsheet that is set up using a search term 'media', I want to select something else from the list e.g 'arts' and I want all of the references to 'media' to turn into 'arts' in the formulas. Kind of like a mass find and replace.
I am acquiring multiple spreadsheets that do not always match row number, due to additional information on some sheets. By this I mean that the information may be in row 31 on on sheet and row 39 on another, the column location is the same each time. I have been trying Vlookups, indexing and matching plus combination formulas - with no luck. How to get the information I need with a moving cell reference?
I can provide a small copy of a workbook, if needed.
I have a spreadsheet that I have variable data in and I want to transfer specific parts of the workbook into a Word doc.
Not a problem with the Excel part, I can select each and every cell that I want, copy them and then...
Recording a macro hasn't done the trick as the code I have only copies and doesn't paste. I know I can edit the code to open Word but the problem is that when in Word, the data needs to go to very specific places, some of it goes in the header, most goes in the main body, I can't see how this could be done as there are no cell refs as in Excel!
is it possible to paste to say halfway in a sentence, in a macro?
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.
I have a series of packages that are denoted by text phrases. Examples:
4/6/12 2/12/12 6/4/12 18/12 24/12
For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).
I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.
I am facing a problem using Hlookup function. I am using Hlookup to do a control check of consolidation i am doing here, however it only returns the first found value instead of all correct values (or range).
I am attaching a file with an example. In the file formula is used in Supply(S) sheet on row 73.
I have various 6 x 6 blocks which contain just numbers, in another part of the spread sheet I have a 1 x 6 block of numbers. What I need to do is to check whether any of the numbers in the 1 x 6 block appear in any of the 6 x 6 blocks.If that does occur then I want to make a specific cell increase by the times that the match happens.using HLookup but just got errors and the If Statement, although did work, went on and on and on.
Im using the following HLOOKUP formula : =HLOOKUP($B$14,$DA$15:$EH$380,2,FALSE) and I need to drag it down 365 times, but the row index num, ie ‘2’ does not increment with the drag. Is this correct – or is there a way of making the 2 increment?
I have some experience with programming, so I understand the theory behing things like loops, arrays and functions etc...Only problem is, I need to make a program at work to ease workloads and I'm afraid I have no idea where to begin.
Basically, I need something similar to a lookup, but thanks to the limit of IF statements available in the formula bar, I can't write what I need.
The program I need to write wouldbe similar to an autofill function. On Sheet1 of my spreadsheet, there are fields: Name, Address 1, Address 2, Address 3, Postcode, Area, Rep.... (The last three are the most important) On sheet two, Postcode, Area, Rep already have data entered in thier respective columns. (D3, E3, F3).
What I need done on Sheet1 is when the user enters the first two characters of a postcode, the 'program' will lookup the entire row respective to the postcode that is entered. when enter is pressed, the Postcode will copy itself from the other sheet as well as the Area number and the Rep into the empty fields in Sheet1. The other way to do this is look through a long list until the right postcode for an Area is found then assign the Rep the Contact details (Add. 1, Add. 2, Add. 3...).
I have a huge data and I need to find values through HLookup and than have to find the sum of that whole column.But when I tried to to so It is showing error as Formula broken.
