Free Microsoft Excel 2013 Quick Reference

Odd And Even Array Constants

I have been doing some formulas that use array constants for generating
harmonic series for wave tables
The array is very large and so far have been using ROW(1:1000) etc. to give me
the constant array.
My question is there any way to make a constant sequence that
contains only odd values i.e. 1,3,5,7,9,...infinity?
OK maybe not infinity but up to 9999.
I don't want to have to type them in manually.

Thanks


Hi,
I am trying to do Conditional formatting for odd and even numbers.
If number is Even, the cell pattern colour should be Red
If number is Odd, the cell pattern colour should be Blue
The formula I given is =ISEVEN($A$1) and in format selected the red colour.
=ISODD($A$1) and in format selected the
blue colour.
This formula is not working….
But I am getting error message that you may not use references to other
worksheets or workbooks for conditional formatting criteria.
Please advice…

Can't get Excel to accept data in odd or even rows........from outside
database.

Access has one line (row of data) and that can be copied into Excel, however
I would like to "split" some of that data between odd and even rows.
I can create queries to separate the data on right or left from Access -

example:
Person has one name but has two arms legs and ears.
I want the first colums in every row to contain the same name, but future
colums in those two rows to contain either right(odd) or left(even side
information.
ie;
row 1 Jones right ear good bad undetermined
row 2 Jones left ear good bad undetermined

Can't get Excel to accept data in odd or even rows........from outside
database.

Access has one line (row of data) and that can be copied into Excel, however
I would like to "split" some of that data between odd and even rows.
I can create queries to separate the data on right or left from Access -

example:
Person has one name but has two arms legs and ears.
I want the first colums in every row to contain the same name, but future
colums in those two rows to contain either right(odd) or left(even side
information.
ie;
row 1 Jones right ear good bad undetermined
row 2 Jones left ear good bad undetermined

Hi i have a spreadsheet with a number of columns a,b,-aa,ab
at the foot of each column row 257 is a calculation giving a % score involving the data in that column.

I wish to copy the result of each column to another sheet but wish
a) column a,c,e,g ie odd column to be copied on one row and even results ie b,d,f,h in another row.

sheet1

column- a,b,c,d,e,f,g,h

row 257-62%,33%,27%,72%,34%,56%,81%,23%

sheet 2

A,b,c,d,e,f,g,h,
Class A,62%,27%,34%,81%
Class B,33%,72%,56%,23%

ie odd figs in row 1
and even figs in row 2

b) Also i would like a condition that only % of 60% or above are copied across

hope someone can help

Hi Everybody!

I'm halfway through a little project and have come to a halt...

The problem is:

I have a row containing 7 numbers below 50. (ie, 01, 09, 22, 28, 30, 34, 48)

In the cell to the right of these i want to use a 'COUNTIF' type function which will tell me how many of the previous 7 numbers are odd or even - is this possible in Excel?!

EG. In this example, if I wanted to know how many of the 7 numbers were odd numbers, I would expect the cell to read '2'.

Hope I've explained this easy-enough... any help would be excellent!

Thanks. Carl

Hello ,

I have a column with numbers .. I succed to sort them by descending .. but
now I want to know how I sort them separatly odd or even number !

Thank you,

Istvan.

Column1
0021D8B9B5FE
0021D8B9B494
001FCAE8A6B1
0021D8B9B6BF
001FCAE88DEE
0021D8B9AD96
001FCAE8A9C8
0021D8B9B5F7
0021D8B9B90E
0021D8B9B758

I want to sort on the last digit on each value:

for example:
0021D8B9B5FE
If last digit start with 1,3,5,7,9,a,c,e (consider this odd numbers)
if last digit start with 2,4,6,8,0, b,d (sondifer this even)

So how can I sort each raw based on the last character being odd or
even in excel.

=thanks in advance

I have a 10,000 record list in Excel.
I want to separate the list by odd and even rows. How can I do that?

Thank you,

Gabriela

******** ******************** ************************************************************************>Microsoft Excel - Βιβλίο2___Running: xl2000 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1ABCDEG211143738310101319274042811000415192122411100517213861110062101729800110713202530710101814193234501001Φύλλο1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

What u can see is that the column G has the result of the odd or even of the columns A to E. I use the formula mod() so the 0 represents the odd and the 1 the even.
What i want is to be able when i push a togglebutton to generate the string on column G
I use a code for this but i can only to find out if the string is odd or even not each part of it !!

-------------------------------------------------------------------------------
On Error GoTo Err_Handler
Set checkboxrange = [I1:DX1].SpecialCells(xlCellTypeConstants, 2)
On Error GoTo 0

For Each ThisCell In checkboxrange
s = s & "&" & ThisCell(4).Address(False, False)
Next ThisCell

'Turn off screen
Application.ScreenUpdating = False

rng.ClearContents
[DY4] = "=" & Mid(s, 2, Len(s) - 1)
[DY4].Copy rng

'changes the data type from value to string

Dim SourceArr() As Variant, TempVal
'Place data source into an array
SourceArr = Range("DY4:DY" & Range("A65536").End(xlUp).Row)

For lngLoop = 1 To UBound(SourceArr, 1)
TempVal = SourceArr(lngLoop, 1)

With ThisWorkbook.Worksheets("A").ToggleButton1
If .Value = True Then 'The odd even choice
If TempVal "" Then
TempVal = TempVal Mod 2
End If
End If
End With

'Return the value as a string with the quote "'"
SourceArr(lngLoop, 1) = "'" & TempVal
Next lngLoop
---------------------------------------------------------------------------

Let us say I have array constant in a1 as follows:
{1;2;3;4;5}

And suppose I want to store the above in an array using VBA...so

dim my_Array() as integer
my_array=range("a1").arrayFormula
msgbox my_array(1)

No way ...Can anyone explain why and how to make work.

I believe this is a valid approach to get each record from an external text file to an array inside a VBA macro ...Do you agree ?

How to print odd pages and even pages seperately. For ex I have 100 pages
with page numbers customized to read "001" upto "100" and I want to print all
"Even pages" first then at the reverse side I want to print all "odd pages".

How to complete this task in two setp printing.

I am having a big Excel sheet with about 800 rows and 58 Columns.Where in I
have a Column Named IPNumbers. Basically as we might be knowing an IP numbe
is represented like //163.259.081.234 //.I am trying to filter out the Odd
and Even Numbered IP Numbered from the huge collection of list.I was
wondering If have to opt for the macro or a Custom Filter would be fine.
Any valid suggestions is greatly appreciated.
Thanks,
banu

How to print odd pages and even pages seperately. For ex I have 100 pages
with page numbers customized to read "001" upto "100" and I want to print all
"Even pages" first then at the reverse side I want to print all "odd pages".

How to complete this task in two setp printing.

Hi,

I have a list of numbers, each 17 digits in lenght, I want a formula to give me the sum of odd and even numbers in that string. Well 2 formulas, one odd and one even.

The number is displayed length ways, each digit in a seperate cell. So the array would be B2:R2 for example.

Any ideas?

Lets say I have range A1:A5 containing values 1,2,3,4,5 respectively. I would like to count how many of these cells are odd and place that value in A6. The same for even numbers as well (in A7).

Any help would be appreciated.

XL98: Some Worksheet Functions Do Not Allow Array Constants

This problem occurs because the SUMIF(), COUNTIF(), and COUNTBLANK() functions use the same criteria-matching algorithm as the database functions, such as DSUM(

I was using the following formula to determine if the value is an even or odd number, but it gives me # result....How can I fix this formula accordingly...
These are whole number values from 0through 9
=IF(ISEVEN(A11),"E","O")
example

Source data
A11=3

A12=0

Result
B11=O
B12=E

XL: Some Worksheet Functions Do Not Allow Array Constants

This behavior occurs because the SUMIF(), COUNTIF(), and COUNTBLANK() functions use the same criteria-matching algorithm as database functions, such as DSUM(

Hi

For illustration of my request please see my attached file.

I try to understand what the syntax is to create and use an array named constant which would be composed of other constant.

Example :
Constant called AvgZone1 which is =AVERAGE(zone1)
Constant called AvgZone2 which is =AVERAGE(zone2)

and then create a named array constant based on the names : =AvgZone1,AvgZone2
is not working. You cannot for example do a SUM().

How would you proceed ?
Thanks

Hello,

Is it possible to make an named array constant from named constants to use in a lookup?

Eg

I have defined the following constants in Names:

Text1 = "AAAAAAAAAA"
Text2 = "BBBBBBBBBBB"
Num1 = "1234567890"
Num2 = "0987654321"

And would like to make the following array constant in Names:

Arr = {Text1,Num1;Text2,Num2}

However, it does not work.

I am using Excel 2007 by the way and the above values are made up.

Thanks.

I'm looking for a way to list odd or even numbers from a number to another.For example from 4-20 to list or even numbers,and then seperate them with a comma into a single line.

I have values in range A1:K10
Also in range M1:M10 where some of values are 0 (zero)
I need a formula for A12 to count odd numbers in A1:A10 but only in rows matching "0" in M1:M10. Then fill it to K12.
And the same for even numbers.
The formula I have, counts all matching "0" rows. Maybe it could be modified for odd and even numbers only.

=SUM(INDEX(($M$1:$M$10=0)*(A1:A10""),0,1))

Thanks.

I have 5 numbers in cells A2-E2 (one number per cell)

In cell F2 I want the count of Odd and Even numbers displayed as #/#.

Example:

11, 97, 34, 43, 112 (A2:E2) F2 = "3/2" (3 odd / 2 even)

F2: =COUNTIF(A2:E2,ODD(?))&"/"&COUNTIF(A2:E2,EVEN(?)))

I'm not sure if I should use MOD or if I need to download the Analysis Pack or what....?

Any help is greatly appreciated.

I guess this is possible but it is beyond my limited skills!!

I want to write something like this..... =IF(A1=an odd number,A2,A3)

obviously I would be quite happy with a solution for even numbers as well!!

Thanks in advance

Matt