Find Even Number

[dah]

Hi,

I am trying to get Excel to find a even number is a list of counts.

For example: I have a formula that works out to be
160/7 = 22.85714286. I multiple this number by 8000 for cycle 1 and
16000 for cycle two and 24000 for cycle 3 and so on. I need for Excel
to determine how many cycles it will take to achieve an even number and
then tell me what row that particular number is in. Is this possible?


# of Cycles Encoder Counts
1 182857.143
2 365714.286
3 548571.429
4 731428.571
5 914285.714
6 1097142.857
7 1280000.000

Row 7 has the first even number. I have no trouble using Excel to find
this number but since there are usually hundreds of numbers. . .I need
a way for Excel to tell me where there is an even number and what row
that number is located. in. Any way to write a formula so Excel will
tell me this and what row that number is located?

Thanks

 

[AlfD]

Hi!

The key to recognising an even number is that there is no remainde
(fractional bit) when you divide by 2.

The MOD function tells you the remainder.

MOD(X,2) which tells you the remainder when X is divided by 2. If thi
is zero, then X is even.

You have a number in cell A1
In cell A2 you enter =if(MOD(A1,2)=0,"Even","Odd")

Try some numbers in A1.

Al

 

[CLR]

I read your post to mean that you want to identify the first "Whole"
number..........if so, you can go ahead with your normal formulas to get the
multiples you want and format the column with Conditional Formatting set to
"Formula is" "=$A1-INT(A1))>0" and set the formatting and copy down the
column.............this will clearly point out not only the first, but all
the whole numbers in your list...........

HTH
Vaya con Dios,
Chuck, CABGx3

 

[Jerry W. Lewis]

=MIN(IF(MOD(B1:B7,2)=0,ROW(B1:B7),""))
array entered (Ctrl-Shift-Enter) returns the first row containing an
even number.

=INDEX(B1:B7,row)
(where row is the row number returned by the first formula) returns the
first even number.

Jerry

 

[Jerry W. Lewis]

=MIN(IF(MOD(B1:B7,2)=0,ROW(B1:B7),""))
array entered (Ctrl-Shift-Enter) returns the first row containing an
even number.

=INDEX(B1:B7,row)
(where row is the row number returned by the first formula) returns the
first even number.

Jerry

 

[Dana DeLouis]

I believe your number (22.8571...) is an irrational number. You may need to
round your number after multiplying by 8000 to test for an whole number.
If you think the number might be good to 6 digits, perhaps another option
could be the following. This idea returns 7, instead of searching for a
whole number down a column. This would only work if the 8000 (or any other
number you give) is a whole number.

Sub FindCycle()
Dim n As Double
Dim ans As Double

n = 22.85714286

ans = Right$(WorksheetFunction.Text(Round(n, 6), "00000/00000"), 5)
' Or...
ans = Split(WorksheetFunction.Text(Round(n, 6), "#####/#####"), "/")(1)
End Sub

 

[AlfD]

Hi!

I totally misread the original posting. Let's hope I haven't still go
the wrong stick, let alone the wrong end of it!

The number 120/7 is a very rational number, and fully divided ou
yields those lovely recurring digits 142857 which characterise al
fractions 1/7, 2/7 etc.

The computer might have more trouble with this than you would, becaus
it will be rounding all manner of binary numbers.

Don't use the 22.85714286, which is only an approximation: go back t
160/7 if that is the exact number you are using.

Multiply it by any multiple of 7 (7,14,21....) and it will yield a
integer. No other multipliers will do that exactly (though roundin
might throw up some imposters).

So multiplying by 56000 (8000*7) and 112000 (8000*14) and so on wil
give you true whole number results. Thus rows 7, 14, 21, 28 etc wil
have whole numbers in them.

Does this help?

Al

 

[dah]

Hi,

Thanks to all who posted a response. All of you are good! I trie
everyone' s suggestions and they all worked to different extents.

I used =if(MOD(A1,2)=0,"Even","") to find all the numbers that are eve
( no need to print the Odd numbers). Then in another column, I use
=(IF(MOD(B1:B7,2)=0,ROW(B1:B7),"")) to find what the row number was fo
the even numbers. I could not get the formula to work with =MIN so
took that part out. I didn't realize you could use ROW in a formula.


Next, I will need to put the row number that Excel finds into anothe
formula, which I haven't figured yet but am working on. I will kee
everyone's posts handy as I may need them again.

Thanks again to everyone

 

[Jerry W. Lewis]

The formula I provided with MIN() is an array formula. You have to
array enter it (Ctrl-Shift-Enter) or it will not work. A quick check of
whether you array entered it is to look at the Formula Bar: you type
=MIN(IF(MOD(B1:B7,2)=0,ROW(B1:B7),""))
and if you array enter it, then Excel will display it in curly brackets
{=MIN(IF(MOD(B1:B7,2)=0,ROW(B1:B7),""))}

Jerry

 

[dah]

I'll try the formula again Jerry.

Thank