Home |
Search |
Today's Posts |
#1
|
|||
|
|||
="Z" dual use?
I am using the following 2 formulas to pull occurrances of a letter in a
column. {=COUNT(IF(('Raw data'!L1:L65500<"A")*('Raw data'!B1:B65500="L"),'Raw data'!Q1:Q65500))} and {=COUNT(IF(('Raw data'!L1:L65500<"A")*('Raw data'!B1:B65500="Z"),'Raw data'!Q1:Q65500))} The first formula works fine, but the second comes back with the wrong total(should be 20 with my test data, giving 230). The only difference is changing the "L" to a "Z". Is ="Z" used for something else in excel, i.e. z-test or something? If so is there another way to get the occurrances of "Z" in column B when column L ="A"? -- Message posted via http://www.officekb.com |
#2
|
|||
|
|||
I cut the range down in my test, but worked fine. Sure you don't have 230
(in 65500 it may be hard to find). -- HTH Bob Phillips "Scott Calkins via OfficeKB.com" wrote in message ... I am using the following 2 formulas to pull occurrances of a letter in a column. {=COUNT(IF(('Raw data'!L1:L65500<"A")*('Raw data'!B1:B65500="L"),'Raw data'!Q1:Q65500))} and {=COUNT(IF(('Raw data'!L1:L65500<"A")*('Raw data'!B1:B65500="Z"),'Raw data'!Q1:Q65500))} The first formula works fine, but the second comes back with the wrong total(should be 20 with my test data, giving 230). The only difference is changing the "L" to a "Z". Is ="Z" used for something else in excel, i.e. z-test or something? If so is there another way to get the occurrances of "Z" in column B when column L ="A"? -- Message posted via http://www.officekb.com |
#3
|
|||
|
|||
Yep, the range may be 1-65500 but only 1-262 have data in them and of those
"Z" only is used 20 times. -- Message posted via http://www.officekb.com |
#4
|
|||
|
|||
Scott,
I don't have an answer to your question but when I test your formula with Column L empty and Column B with several Z's then, like Bob, I get a (correct) count of the number of Z's in Column B provided that Column Q is empty or has numbers. If Column Q has letters then the count decreases by 1 for every letter in Q in the same row as a Z in Column B. It may be therefore that a re-write of your formula may be in order. What data is contained in Column Q? If it is just Z'd with no corresponding A in Column L you want then {=COUNT(IF(('Raw data'!L1:L65500<"A")*('Raw data'!B1:B65500="Z"),))} works just as well for me. Try reducing the range sizes and then highlighting everything in your formula except the COUNT and press F9 to see what is actually being counted. Regards Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "Scott Calkins via OfficeKB.com" wrote in message ... Yep, the range may be 1-65500 but only 1-262 have data in them and of those "Z" only is used 20 times. -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
try selecting everything
<data<filter<auto filter Select custom not equal to "A" in column L and "Z" in column B . Check column Q what is there. This should let you see if there is something odd in your file. try also =sumproduct(--('Raw data'!L1:L65500<"A"),--('Raw data'!B1:B65500="Z"),--(isnumber('Raw data'!B1:B65500)) "Scott Calkins via OfficeKB.com" wrote: I am using the following 2 formulas to pull occurrances of a letter in a column. {=COUNT(IF(('Raw data'!L1:L65500<"A")*('Raw data'!B1:B65500="L"),'Raw data'!Q1:Q65500))} and {=COUNT(IF(('Raw data'!L1:L65500<"A")*('Raw data'!B1:B65500="Z"),'Raw data'!Q1:Q65500))} The first formula works fine, but the second comes back with the wrong total(should be 20 with my test data, giving 230). The only difference is changing the "L" to a "Z". Is ="Z" used for something else in excel, i.e. z-test or something? If so is there another way to get the occurrances of "Z" in column B when column L ="A"? -- Message posted via http://www.officekb.com |
#6
|
|||
|
|||
one additional comment your equations show when L < A but your Question is
for when column L = A. change my trys to =A ranter than < A if you really do want to have the = "Scott Calkins via OfficeKB.com" wrote: I am using the following 2 formulas to pull occurrances of a letter in a column. {=COUNT(IF(('Raw data'!L1:L65500<"A")*('Raw data'!B1:B65500="L"),'Raw data'!Q1:Q65500))} and {=COUNT(IF(('Raw data'!L1:L65500<"A")*('Raw data'!B1:B65500="Z"),'Raw data'!Q1:Q65500))} The first formula works fine, but the second comes back with the wrong total(should be 20 with my test data, giving 230). The only difference is changing the "L" to a "Z". Is ="Z" used for something else in excel, i.e. z-test or something? If so is there another way to get the occurrances of "Z" in column B when column L ="A"? -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dual Justifications in a cell? | Excel Discussion (Misc queries) | |||
Opening two excel worksheets with dual monitors. | Excel Worksheet Functions | |||
I have dual monitors. I need to view spreadsheets on both monito. | New Users to Excel | |||
How do you create dual drop down menus in excel 2000? | Excel Discussion (Misc queries) | |||
Does a dual processor configuration noticeably improve Excel spre. | Excel Discussion (Misc queries) |