| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count and return pair numbers
What function can I use to return the number of pair or impair numbers in a
row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
What is your definition for pair and impair numbers?
"Sly" wrote: What function can I use to return the number of pair or impair numbers in a row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
Perhaps odd and even numbers?
=SUM(MOD(A1:A10,2)) =SUM(IF(MOD(A1:A10,2)=0,1,0)) The top formula returns odd number, the other returns even Both are array formula so Ctrl+Shidt+enter Mike "Sly" wrote: What function can I use to return the number of pair or impair numbers in a row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
sorry
odd numbers = 2 Even numbers = 4 "bj" wrote: What is your definition for pair and impair numbers? "Sly" wrote: What function can I use to return the number of pair or impair numbers in a row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
Sorry I will reformulate
I'm looking to find out how many cell in a row contain an even number row1 = 2 4 8 11 15 16 Looking for a formula that will tell me (4) cells have even number "Mike H" wrote: Perhaps odd and even numbers? =SUM(MOD(A1:A10,2)) =SUM(IF(MOD(A1:A10,2)=0,1,0)) The top formula returns odd number, the other returns even Both are array formula so Ctrl+Shidt+enter Mike "Sly" wrote: What function can I use to return the number of pair or impair numbers in a row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
Which for a range with no blank cells is exacly what the 2 formula I have
given you will do. Mike "Sly" wrote: Sorry I will reformulate I'm looking to find out how many cell in a row contain an even number row1 = 2 4 8 11 15 16 Looking for a formula that will tell me (4) cells have even number "Mike H" wrote: Perhaps odd and even numbers? =SUM(MOD(A1:A10,2)) =SUM(IF(MOD(A1:A10,2)=0,1,0)) The top formula returns odd number, the other returns even Both are array formula so Ctrl+Shidt+enter Mike "Sly" wrote: What function can I use to return the number of pair or impair numbers in a row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
Sorry Mike
Don't want to waste your time but I try it does'nt work Thank you very much for your time Sly "Mike H" wrote: Which for a range with no blank cells is exacly what the 2 formula I have given you will do. Mike "Sly" wrote: Sorry I will reformulate I'm looking to find out how many cell in a row contain an even number row1 = 2 4 8 11 15 16 Looking for a formula that will tell me (4) cells have even number "Mike H" wrote: Perhaps odd and even numbers? =SUM(MOD(A1:A10,2)) =SUM(IF(MOD(A1:A10,2)=0,1,0)) The top formula returns odd number, the other returns even Both are array formula so Ctrl+Shidt+enter Mike "Sly" wrote: What function can I use to return the number of pair or impair numbers in a row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
If blanks are an issue then use this for even numbers
=SUMPRODUCT(--(A1:A10<""),--(MOD(A1:A10,2)=0)) or in fact a similar one for odd numbers but the previous isn't affected by blanks =SUMPRODUCT(--(A1:A10<""),--(MOD(A1:A10,2)=1)) Mike Mike "Sly" wrote: Sorry I will reformulate I'm looking to find out how many cell in a row contain an even number row1 = 2 4 8 11 15 16 Looking for a formula that will tell me (4) cells have even number "Mike H" wrote: Perhaps odd and even numbers? =SUM(MOD(A1:A10,2)) =SUM(IF(MOD(A1:A10,2)=0,1,0)) The top formula returns odd number, the other returns even Both are array formula so Ctrl+Shidt+enter Mike "Sly" wrote: What function can I use to return the number of pair or impair numbers in a row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
Try these formulas...
Odd: =SUMPRODUCT(--(MOD(A1:F1,2)=1)) Even: =SUMPRODUCT(--(MOD(A1:F1,2)=0)) Rick "Sly" wrote in message ... sorry odd numbers = 2 Even numbers = 4 "bj" wrote: What is your definition for pair and impair numbers? "Sly" wrote: What function can I use to return the number of pair or impair numbers in a row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
Thank you very much Mike
It works, Much appriciated Sly "Mike H" wrote: If blanks are an issue then use this for even numbers =SUMPRODUCT(--(A1:A10<""),--(MOD(A1:A10,2)=0)) or in fact a similar one for odd numbers but the previous isn't affected by blanks =SUMPRODUCT(--(A1:A10<""),--(MOD(A1:A10,2)=1)) Mike Mike "Sly" wrote: Sorry I will reformulate I'm looking to find out how many cell in a row contain an even number row1 = 2 4 8 11 15 16 Looking for a formula that will tell me (4) cells have even number "Mike H" wrote: Perhaps odd and even numbers? =SUM(MOD(A1:A10,2)) =SUM(IF(MOD(A1:A10,2)=0,1,0)) The top formula returns odd number, the other returns even Both are array formula so Ctrl+Shidt+enter Mike "Sly" wrote: What function can I use to return the number of pair or impair numbers in a row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
Try this:
For Even numbers: =SUMPRODUCT(--(MOD(A1:F1,2)=0)) For Odd numbers: =SUMPRODUCT(--(MOD(A1:F1,2)=1)) "Sly" wrote: What function can I use to return the number of pair or impair numbers in a row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
I think the problem is the OP asked for numbers in a row and your formulas
were for numbers in a column... I'm not sure he sees the difference. Rick "Mike H" wrote in message ... Which for a range with no blank cells is exacly what the 2 formula I have given you will do. Mike "Sly" wrote: Sorry I will reformulate I'm looking to find out how many cell in a row contain an even number row1 = 2 4 8 11 15 16 Looking for a formula that will tell me (4) cells have even number "Mike H" wrote: Perhaps odd and even numbers? =SUM(MOD(A1:A10,2)) =SUM(IF(MOD(A1:A10,2)=0,1,0)) The top formula returns odd number, the other returns even Both are array formula so Ctrl+Shidt+enter Mike "Sly" wrote: What function can I use to return the number of pair or impair numbers in a row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
Try these formulas...
Odd: =SUMPRODUCT(--(MOD(A1:F1,2)=1)) Even: =SUMPRODUCT(--(MOD(A1:F1,2)=0)) Mike raises a good point about blanks. Here are revisions to the above which will account for the blanks... Odd: =SUMPRODUCT((A1:F1<"")*(MOD(A1:F1,2)=1)) Even: =SUMPRODUCT((A1:F1<"")*(MOD(A1:F1,2)=0)) Rick |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
Rick,
Odd: =SUMPRODUCT(--(MOD(A1:F1,2)=1)) You don't need the =1, (--(MOD(A1:F1,2) returns 0 or 1 anyway -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Rick Rothstein (MVP - VB)" wrote in message ... Try these formulas... Odd: =SUMPRODUCT(--(MOD(A1:F1,2)=1)) Even: =SUMPRODUCT(--(MOD(A1:F1,2)=0)) Rick "Sly" wrote in message ... sorry odd numbers = 2 Even numbers = 4 "bj" wrote: What is your definition for pair and impair numbers? "Sly" wrote: What function can I use to return the number of pair or impair numbers in a row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count and return pair numbers
Good point! Thanks for noting that.
Rick "Sandy Mann" wrote in message ... Rick, Odd: =SUMPRODUCT(--(MOD(A1:F1,2)=1)) You don't need the =1, (--(MOD(A1:F1,2) returns 0 or 1 anyway -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Rick Rothstein (MVP - VB)" wrote in message ... Try these formulas... Odd: =SUMPRODUCT(--(MOD(A1:F1,2)=1)) Even: =SUMPRODUCT(--(MOD(A1:F1,2)=0)) Rick "Sly" wrote in message ... sorry odd numbers = 2 Even numbers = 4 "bj" wrote: What is your definition for pair and impair numbers? "Sly" wrote: What function can I use to return the number of pair or impair numbers in a row A B C D E F Row 1 2 6 9 11 16 20 Pair number = 4 Impair number = 2 Thanks Sly |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
| Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| in excel, how do I find which value doesn't have a pair? | Excel Discussion (Misc queries) | |||
Linear Mode
