Mike A. emailed in a Learn Excel from MrExcel Podcast Question. Having six (6) Columns of Data, Mike wants to count how many values in each Row are Even and how many are Odd. So, today in Episode #1630, we follow along as Bill 'MrExcel' Jelen explores the IsEven and IsOdd Functionality before creating an Array Formula to Count Even and odd in Mike's Data.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
This Excel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1630: count even and odd.
Hey welcome back to the MrExcel netcast I'm Bill Jelen and today's question sent in by Mike.
Mike has a data set with six columns of data and he's totalling the data but then he wants to count how many of those six values are even and how many of those six values are odd.
Okay so we have two functions that I hardly ever use that I thought might be helpful here, equal is even returns true if the value is even or false if it's not, the other one I thought I've right away is just equal even.
Even is a rounding function that rounds the number to the even number so we could check to see if the even of B2 is equal to B2 and that will tell us if it's even so my first attempt here was to use the is even and when I did that equal SUM open parenthesis if is even B2 – G2 - this should return, should return, an array of 6 trues and falses and I wanted to convert those to ones and zeros but unfortunately when we use evaluate formula we’ll see that the is even does not return an array we don't get 6 trues and falses there just says no way no how not going to work so let's edit this instead of saying using is even let's use the even function.
If the even of B2 - G2 is equal to B2 - G2, then return 1 otherwise a 0 and SUM those 6 results up.
Now this is definitely an array formula which means we have to hold down CTRL + SHIFT while we press ENTER, so hold on control and shift and press Enter, and sure enough we get our answer there are 1, 2, 3, 4 evens there.
Double click to shoot that down.
The number of odds, well we could go through the whole same thing but why let's just do equal 6 minus the number of odds because there are six numbers and we're good to go there so interesting little array formula don't forget to hit control shift enter to select that and you can count the number of even values in a range.
Wait I want to thank for stopping by see you next time for another netcast with MrExcel.
Learn Excel from MrExcel podcast episode 1630: count even and odd.
Hey welcome back to the MrExcel netcast I'm Bill Jelen and today's question sent in by Mike.
Mike has a data set with six columns of data and he's totalling the data but then he wants to count how many of those six values are even and how many of those six values are odd.
Okay so we have two functions that I hardly ever use that I thought might be helpful here, equal is even returns true if the value is even or false if it's not, the other one I thought I've right away is just equal even.
Even is a rounding function that rounds the number to the even number so we could check to see if the even of B2 is equal to B2 and that will tell us if it's even so my first attempt here was to use the is even and when I did that equal SUM open parenthesis if is even B2 – G2 - this should return, should return, an array of 6 trues and falses and I wanted to convert those to ones and zeros but unfortunately when we use evaluate formula we’ll see that the is even does not return an array we don't get 6 trues and falses there just says no way no how not going to work so let's edit this instead of saying using is even let's use the even function.
If the even of B2 - G2 is equal to B2 - G2, then return 1 otherwise a 0 and SUM those 6 results up.
Now this is definitely an array formula which means we have to hold down CTRL + SHIFT while we press ENTER, so hold on control and shift and press Enter, and sure enough we get our answer there are 1, 2, 3, 4 evens there.
Double click to shoot that down.
The number of odds, well we could go through the whole same thing but why let's just do equal 6 minus the number of odds because there are six numbers and we're good to go there so interesting little array formula don't forget to hit control shift enter to select that and you can count the number of even values in a range.
Wait I want to thank for stopping by see you next time for another netcast with MrExcel.
