Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to compare 2 cells in range
Hi, sorry for my English :)
My data: plan | done 100 | 0 100 | 100 200 | 150 100 | 100 ..... .... hundreds equal cells Is any formula, which gives me result=2 ? I don't want (i can't) to add new column with, for example, =a2=b2 and then count false. I want only count of false. I hope my post is clear :) tomekgomek |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to compare 2 cells in range
=sumproduct(--(A1:A100<B1:B100))
will count the number of non equal pairs in rowns 1-100 in columns a and B note you cannot use A:A<B:B in sumproduct the--( chnages the logical true false to a numeric 1 0 "tomek gomek" wrote: Hi, sorry for my English :) My data: plan | done 100 | 0 100 | 100 200 | 150 100 | 100 ..... .... hundreds equal cells Is any formula, which gives me result=2 ? I don't want (i can't) to add new column with, for example, =a2=b2 and then count false. I want only count of false. I hope my post is clear :) tomekgomek |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to compare 2 cells in range
With your data in column A and B, with a macro:-
Sub strata() Dim i As Integer i = Range("A1").CurrentRegion.Rows.Count Set myrange = Range("A1:A" & i) For Each c In myrange If c.Value < c.Offset(0, 1).Value Then Count = Count + 1 Next MsgBox (Count & " difference between column A & B") Cells(1, 3).Value = Count End Sub Right click sheet tab view code and paste in Mike "tomek gomek" wrote: Hi, sorry for my English :) My data: plan | done 100 | 0 100 | 100 200 | 150 100 | 100 ..... .... hundreds equal cells Is any formula, which gives me result=2 ? I don't want (i can't) to add new column with, for example, =a2=b2 and then count false. I want only count of false. I hope my post is clear :) tomekgomek |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to compare 2 cells in range
Użytkownik "Mike H" napisał w wiadomości ... With your data in column A and B, with a macro:- Sub strata() Dim i As Integer i = Range("A1").CurrentRegion.Rows.Count Set myrange = Range("A1:A" & i) For Each c In myrange If c.Value < c.Offset(0, 1).Value Then Count = Count + 1 Next MsgBox (Count & " difference between column A & B") Cells(1, 3).Value = Count End Sub Right click sheet tab view code and paste in Mike thanks, but VBA isn't for my boss :) anyway, thanks tomekgomek |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to compare 2 cells in range
Użytkownik "bj" napisał w wiadomości ... =sumproduct(--(A1:A100<B1:B100)) will count the number of non equal pairs in rowns 1-100 in columns a and B note you cannot use A:A<B:B in sumproduct the--( chnages the logical true false to a numeric 1 0 great, super, wow :) thanks tomek |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare the contents of one range of cells with the contents of a. | Excel Discussion (Misc queries) | |||
Look-up and Compare Table Range Then | Excel Worksheet Functions | |||
compare cell with a range | Excel Discussion (Misc queries) | |||
How do I compare cells and if FALSE compare to next cell in EXCEL | Excel Worksheet Functions | |||
Compare range with another range in other workbook | Excel Worksheet Functions |