Comparing Cells And Return Number?

Apr 23, 2013

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.

View 9 Replies


ADVERTISEMENT

Comparing 2 Cells To Return A Value

Nov 17, 2009

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.

View 3 Replies View Related

Comparing Two Columns And Returning Number Of Cells In One Column With Higher Values?

Apr 8, 2014

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.

View 3 Replies View Related

Return Largest Number Within A Range Of Cells

Aug 26, 2009

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.

View 3 Replies View Related

Return 1 Of 2 Cells After Checking Cell For Number

Aug 2, 2008

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.

View 8 Replies View Related

Return True When Comparing Target To A Range In VB

Nov 12, 2003

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)

View 9 Replies View Related

Return Matching Numbers By Comparing 2 Ranges

Apr 30, 2008

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.

View 2 Replies View Related

Return Number Of Cells With The Most Consecutive Specific Values?

Aug 13, 2014

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.

View 8 Replies View Related

Excel 2003 :: Return Nth Number From Column Of 1000+ Cells

Mar 12, 2014

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.

View 3 Replies View Related

Pivot Table Chaos: Blank Cells Return 0, Empty Cells Return Nothing

Jun 25, 2009

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

View 9 Replies View Related

Comparing 2 Columns, Highlighting The Higher Number

Sep 1, 2009

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.

View 3 Replies View Related

Comparing A Phone Number To An Allowed List

Feb 20, 2007

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

View 9 Replies View Related

Excel Macro For Comparing 2 XLS Files With N Number Of Worksheets And Columns

Feb 24, 2014

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

View 1 Replies View Related

Comparing Two Column For Matching Number But The Items Compared Also Contains Letters

May 2, 2006

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.

View 4 Replies View Related

Comparing The Cells

Apr 6, 2009

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?

View 5 Replies View Related

Comparing Two Cells

Nov 16, 2009

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?

View 3 Replies View Related

VBA: Comparing A Value To Several Different Cells

Jun 8, 2006

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.

View 5 Replies View Related

Comparing Two Cells With Another

Mar 16, 2007

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.

View 9 Replies View Related

Comparing Multiple Cells

Feb 11, 2009

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.

View 2 Replies View Related

Comparing Sheets (cells)

Apr 6, 2009

If u dont mind can i have example xls file please.

i hope u guys will help me to improve my knowledge by uploading attachments ...

View 9 Replies View Related

Comparing The Same Cells In Two Worksheets

Aug 22, 2008

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.

View 14 Replies View Related

Comparing Certain Numbers In Two Different Cells

Nov 7, 2008

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.

View 6 Replies View Related

Comparing Text In Cells..?

Jan 26, 2005

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.

View 9 Replies View Related

MACRO Comparing Cells ...

Dec 21, 2006

I have a Workbook with two Sheets MAIN and STATS

Col A in both sheets contain text values (names)

I need a macro which will:-

Look at each name in Col A MAIN sheet.

See if there is a match in Col A of STATS sheet.

If there is a match then enter "YES" in Col E

e.g Smith Eddie is is in the Cell A20 MAIN sheet, Smith Eddie is in Cell A55 STATS sheet, so Enter "YES" in Cell E55 STATS sheet.

View 9 Replies View Related

Comparing Two Cells With Dates In VBA

Dec 12, 2009

I have trouble trying to get the right sintax of comparing two dates locates in two different sheets.

Example:
Sheet 1, cell A1 = 12/11/2009
Sheet 2, cell B1 = 12/11/2009

If I need to do something based on condition that Sheet1 date = sheet 2 date, how will the code be written? I have

If Sheets("Sheet1").range("A1") = Sheets("Sheet2").range("B1") Then
**** my code here

View 9 Replies View Related

Comparing Text If Some Cells Can Be Empty

Aug 13, 2009

I need to compare some cells, but sometimes one or two cells can be empty (but sometimes none of them)

e.g. A1 = January
B1 =
C1 = January
D1 = January
E1 =

and I need to compare this cells A1:E1 and if text in not empty cells is the same then write TRUE to F1 and if not write FALSE.

View 6 Replies View Related

Comparing List Of Cells To Each Other Using Macros

Jan 28, 2010

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.

View 9 Replies View Related

Comparing Multiple Cells To The Same Array?

Feb 26, 2014

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.

View 4 Replies View Related

Sorting Rows By Comparing Cells ...

Mar 13, 2007

the system works by whoever has the most points wins.. i already have my formulas and everything done..

i want the excel program to move the entire row, if possible, by whoever has the most points to the top..

(i.e If ryan d. has the most points he will be is vince v.'s spot)

can this be done automatically in excel or do i have to do it by hand?

View 9 Replies View Related

Comparing 3 Cells As Equal Or Unequal

Mar 27, 2007

Columns E and F can have a 1 or a Zero
If e2, d3, e4 = all 0's or all 1's, count = 3 in col F
If either e2, d3 or e4 is not equal the count is 2 in F

Example

D E F
0
0
0 3
0 1 2
1 1 2
1 1 3

Also, why would this formula work with numbers but not letters?

=IF(B2="x",0,IF(C2="x",1,""))

View 9 Replies View Related







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