Thing is, I have an excel sheet for a private competition in sports games. How can I do a function, that automatically calcutes points for every "bet" against the game result?
For example:
If game finishes 4-3 I'd like to compare it for every players guessed score and give points according to this:
1. If the bet is completely correct (4-3) -> Return 10 (points). In case of tie (like 3-3, -> Return 20 (points)
2. If the bet has a correct winner, with correct goals for eather team (4-x / x-3) -> Return 4 (points)
3. If the bet has a correct winner (home/visitor) -> Return 3 (points)
4. If the bet has incorrect winner but amount of goals for eather team correct -> Return 1 (point)
5. If the bet is completely wrong -> Return 0 (points)
At least for me, this sounds more like rocket science but just wanted to check if this is anyway doable.
I have column a,b,c. b and c both contain numbers. if cell c1 equal b1 then i want "new" to be displayed in a1. If it does not equal then i want "old". but if c1 is blank then i want a1 to remain blank.
I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.
So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.
I'm looking for a formula to enter within a particular cell which will return the largest number that appears within a range of cells, for example the range H:133 through L:136. If it's any easier, only one number will appear within the range, though it could appear in any cell within that range.
I have a report that includes data broken up by several headings. Instead of showing the heading and the data below it, I would prefer that the heading was included in every subsequent row that it related to (and the heading be removed).
All the headings are in italic non bold format. All the data is in standard format (no bold / italics / underline).
The only data in the sheet is as above, and there are no gaps in the data. A heading will relate to all the data directly below it until there is a new heading.
Why doesn't the IS operator return True when comparing Target to a range in VB when they are indeed the same? Why do we have to keep backing in via rng.Address = Target.Address or Not Intersect() Is Nothing?
Private Sub Worksheet_SelectionChange(ByVal Target As Range) **** ****'_____Works_____ ****If Target.Address = "$B$2" Then ********Application.StatusBar = "To Be" ****Else ********Application.StatusBar = "Not to be" ****End If **** ****'_____This fails_____ ****If Target Is Range("A1") Then ********Application.StatusBar = "A1 sauce anyone?" ******** ****'____But this works_____ ****ElseIf Not Intersect(Target, Range("A1")) Is Nothing Then ********Application.StatusBar = "¿Alguien quiere salsa A1?" ****End If
End Sub
Again - this is just me wondering why... Am I missing something terribly obvious here?
(Edit) I am guessing it has to do with the Target argument for SelectionChange() coming in ByVal instead of ByRef, but not sure... (End Edit)
I have a collum (A )with numbers and in other sheet have a collum (B)with numbers too.. and some numbers match. what i want is in the cells that the numbers match have other colum and i want to copy that number to another cell.
I have a range of say B1:Z2. In row B1:Z1 I have dates in the format dd-mmm-yy. In row B2:Z2 I indicate presence of a person by "P". This is at infrequent appearance i.e. p,p,p,blank,blank,blank,p,p,p,p,p,blank,blank,p,p,blank,p,p,p, etc. I am trying to find or workout a formula that would find the most repetitive Ps in row B2:Z2 and give me the sum thereof. I.e, from the above example it should be 5. It does not have to count specifically P. Something that counts the most non-blank sequence of cells in the range should also do.
I need a formula that returns the Nth number from a single column that contains a small number of values within a span of 1000 cells. E.g., within 1000 cells there are 36 with numbers and the remaining cells are blank. I need to obtain the first occurrence of a number in the column, then the second, third... to the thirty-sixth. The end result would be a column with 36 numbers with no blanks in between without loosing the correct order of the numbers.
I have excel 2003 and have been tooling around with the index and lookup functions with no success.
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
I am trying to do is I have column C and column G. For each row, I want to highlight or change the font color of the greater number. I can do it with conditional formatting, but I am only able to do it for 1 row at a time and it will take forever considering there are over 800 rows.
Is there an easy way to compare the two columns and highlight or change the font color of the higher number in that row?
For example:
C G 4 5 8 1 6 2 9 11
So it would highlight the 5 in the first row, the 8 in the second row, the 6 in the third row, and 11 in the fourth row.
I have our company telephone accounts and im trying to highlight any numbers that are not on our approved dialling list. What I would like to happen (ideally) would be for a formula to look at the cell next to it and compare it with the approved numbers list, if it doesn’t appear on the list then it would do something to let me know, like put a big red X in the cell (if it could make me a cup of Tea instead that would be better).
Need a excel macro which can compare 2 xls files and highlight cells which are different in the 2 files its like a slightly advanced one >a prompt which asks file 1 after selecting file a prompt whichasks for file 2 ..then on running it should highlight cells in file two which are different from file one Both file have same layout ie if file one has n columns in n worksheets file 2 will also have same . So the comparison should be done for each worksheet in both file and cells should be highlighted ..
I am trying to compare two colums. They both contain numbers mixed with letters. I am wanting to match only the numbers in both not the letters. Example:
column a = m454 column b = fsh454-1 m543 fst998-2 m998 fsm434-1
my match is m454 and fsh454-1, m998 and fst998-2. The items can be in any order in the column. The end result I want to indicate the match by putting an X by column a item that matches column b.
I have two spreadsheets I would like to compare against each other, last weeks inventory (Sheet 1) and this weeks (Sheet 2). All items are listed by unit number, is there a way to have all unit numbers on Sheet 1 colored red that are not on Sheet 2 and vice versa?
Is there a way to compare two cells and return true if they are the same, false if different? There is text and numerical data. As a side note, can anybody recommend a lightweight reference so I can sit for an evening or two and at least get an idea of what excel can do?
I need to compare a value to several cells and print the value into a new cell if and only if the value is not in any of the cells I am comparing to.
Say cells A1, A4, A8 and A12 contains different numbers. I want to compare a value, say 120, to the values of the above cells and if it is not contained in one of those cells I want to enter 120 into cell A16. If A1, A4, A8 or A12 contains the value 120 I want to leave A16 blank or write 0 to it.
Is there an easy way of doing this? Right now I am using a if-test for each of the cells I am comparing to, but this gets very messy as the number of cells increases.
how to compare two cells (say, today's date with the date an order was supposed to be sent out), with a third cell that either has a tick in it, or nothing. If there's an easier way to do this other than VB code, I haven't found it. I tried conditonal formatting but it wouldn't let me have a condition in which it just checked the third cell, it needed to compare it with the selected one.
I have two excel files. The data is arranged by column. I want to take 3 or more cells (from three different columns) from a particular row and match them up with three cells (in 3 different columns but in the same row). The three cells should be in the same row. However the matching row may appear in a different order in each of the two lists. When Excel finds a match, I want it to return true. How can I go about doing something like this?
I have attached a file explaining what I mean. I am trying to match the three cells in list one with three cells in one of the rows in list 2. However the number of cells in each list is different and there are some cells in list 2 which are not in list 1 and vice versa. There may also be extra columns, which I have not included for the sake of simplicity.
I would like to make a macro which compares the content of the same cells in two worksheets which are in the same workbook.
More clearer I have a workbook with two sheets (Sheet1 and Sheet2). What I would like the macro to do is to check cell A1 in Sheet1 and compare it to cell A1 in Sheet2 and so on till the last cell (IV65536). If there is a differnce between the two cells, then it should highlight the background of the cell in Sheet1.
I know that there has been similar requests on this forum (I searched and read them) but thew were different.
I was wondering if someone can at least point me in the right direction with respect to comparing two numbers within a value in two different cells. For example:
If one cell has the value "AB123456" And the cell below it has a value of "AB124658"
I want to be able to determine the difference (with VBA code) between both cells by just looking at the fourth and fifth characters ("23" and "24", respectively) in each cell value.
I have a spreadsheet that has long text sentences in each cell. I have a 2nd spreadsheet which is a slightly updated version of the first spreadsheet. The slight updates consisted of editing the odd word here or there out of the first spreadsheet.
Unfortunately, I didn't keep track of the changes I made, and I need to know what they are.
It's easy to tell if a change has been made, simply by comparing the cells. But I can't find an easy way to find out exactly what change was made. Comparing the cells a line at a time is very time consuming.
I have a list of authors for about 20 papers. These Papers are listed in sequential order from 1999 to 2005.
I want to track the number of similar authors from paper to paper. Thus I am attempting to create a macros that has the Papers listed sequentially when they were published and the authors for each paper, with the authors name in each cell. I want to create a macros that will compare the Author Cells associated to lets say the Fifth Paper with the Author Cells for the Fourth Paper (the Previous Paper). So that if certain authors come up in the Fifth Paper that were in the Fourth Paper, the # will be noted.
I also want this macros to compare all the Author Cells that have appeared in the past with that of the author cells of the (N) paper excluding the (N-1) Paper, and just note the # of occurences. So using the previous example. If we are talking about the Fifth Paper, it will look through Papers #1-#3 for any matching keywords and note the number.
I have attached my spreadsheet to make it more clearer. For some papers there are more than 20 authors as well just to note.
I am trying to find compare the value in one cell with an array of cells. My problem is when I try to carry the formula down to the next line - my array changes as well. For example:
Before After 01915
[Code].....
So, if Before is Column A and After is Column G, the formula I have right now is =INDEX(A2:A3349,MATCH(G2,A2:A3349,0)). When I try carry the formula down to G3, G4 and so on, the array changes as well.
