Excel 2007

[Erdinç E. Karaçam]

Hi everyone,

I like this part of the board; it's a cute attemption. So i want my question using English to give some help to this section.

My question about Excel 2007. I have already two different version of Excel. (2002 and 2003) I am wondering what is new in Excel 2007? (Specially about technical features). I tried test-drive of Microsoft but i wasn't satisfied enaugh via that method. Should i buy it instead of older versions? Any ideas?
I would be happy if you give me a basic list some of the new features in Excel 2007.

Thanks in advance.

[birol_mumcu]

hi my friend ;
this side is very great in our site , thanks for idea and who gave to take pains with
ı set office 2007 on my computer in my office, ı am using excel 2007. it is very useful i think, they put the all formulas on the menübar, they did big menü items, some people, some times using this menü they dont look any word, they are looking the icon on the menü bar. i dont know am i right.
another of these, how am ı say, lets use excel 2007 and lets dicover it, of course we will share what we are learn about "EXCEL2007"
http://www.excel.web.tr/showthread.php?t=22500
i will write in Turkish, what am i find any kind of somethig in that link
i am sory about mistake of my english.
thanks,

[Erdinç E. Karaçam]

hi my friend ;
this side is very great in our site , thanks for idea and who gave to take pains with
ı set office 2007 on my computer in my office, ı am using excel 2007. it is very useful i think, they put the all formulas on the menübar, they did big menü items, some people, some times using this menü they dont look any word, they are looking the icon on the menü bar. i dont know am i right.
another of these, how am ı say, lets use excel 2007 and lets dicover it, of course we will share what we are learn about "EXCEL2007"
http://www.excel.web.tr/showthread.php?t=22500
i will write in Turkish, what am i find any kind of somethig in that link
i am sory about mistake of my english.
thanks,

Hi Birol,

Thank you very much to your friendly approach to help me via restricted English knowledge. Please do not worry. Your act is very nice. Thanks again.

Actually, i have some knowledges about user interface of the new edition. Advanced row-column, page, filtering etc options.

But i want to have some knowledge about newest functions. ( Cube, Engineering, External, Math and Trigonometry and Statistical functions of the new edition. )

And also, taht will be very nice if i can learn some new limist of items of new Edition. (For example: How much is the number of levels of nesting that Excel allows in formulas in new edition? | Maximum number of arguments to a function? something like this... )

Thanks in advance.

[birol_mumcu]

But i want to have some knowledge about newest functions. ( Cube, Engineering, External, Math and Trigonometry and Statistical functions of the new edition. ).
ı send the page in word 2007 , it has some pages for excel2007 on the picture. ı hope it could be explanation.



[/QUOTE]And also, taht will be very nice if i can learn some new limist of items of new Edition. (For example: How much is the number of levels of nesting that Excel allows in formulas in new edition? | Maximum number of arguments to a function? something like this... )

Thanks in advance.[/QUOTE]

they did increase the rows and columns. there is 1.048.576 rows and 16.384 columns. you know that in excel 2003 and precieding version has 65.656 etc. etc.

[birol_mumcu]

ı send the files
first paragraph of the answer

[birol_mumcu]

the second file

[birol_mumcu]

and the last file there are formulas appearange on the menü bar

[mehmett]

It's wonderful.

Thank you very much for screenshots Mr Mumcu.

I wait Excel 2007 impatiently.

[birol_mumcu]

hi;
i thank you for give me an opportunity,
if i find an opportunity again i will make another picture for our site.
thanks again
may it be easy

[Erdinç E. Karaçam]

Hi,

Mr Birol Mumcu, i thank you again. Of course these are useful helps, but i think there is a little missunderstanding between my request and your figuring. As i said; i don't have some trouble about user interface of Excel 2007. :)

Anyway, it is not a problem, because i researched on Internet after i sent my last message and i found these out:

I want share with you, i hope it helps to all members and all Excel users. :hey:

About "Spreadsheet Size":
Microsoft has been listening, the last cell in Excel has gone from IV65536 to XFD1048576. That is going from 256 columns to 16,385 columns and from 65,536 rows to 1,048,576 rows. The new spreadsheet capacity is the same as an Excel 2003 workbook with 1,024 spreadsheets. (To get to the last cell (the bottom right corner) open an empty sheet press Ctrl Down Arrow and then Ctrl Right Arrow.)

And the other detailed descriptions are below:

Item: The total number of available columns in Excel. Old Limit : 256 (2^8) New Limit : 16k (2^14)
Item: The total number of available rows in Excel. Old Limit : 64k (2^16) New Limit : 1M (2^20)
Item: Total amount of PC memory that Excel can use. Old Limit : 1GB New Limit : Maximum allowed by Windows
Item: Number of unique colours allowed a single workbook. Old Limit : 56 (indexed colour) New Limit : 4.3 billion (32-bit colour)
Item: Number of conditional format conditions on a cell. Old Limit : 3 conditions New Limit : Limited by available memory
Item: Number of levels of sorting on a range or table. Old Limit : 3 New Limit : 64
Item: Number of items shown in the Auto-Filter dropdown. Old Limit : 1 New Limit : 10
Item: The total number of characters that can display in a cell. Old Limit : 1k (when the text is formatted) New Limit : 32k or as many as will fit in the cell (regardless of formatting)
Item: The number of characters per cell that Excel can print. Old Limit : 1k New Limit : 32k
Item: The total number of unique cell styles in a workbook (combinations of all cell formatting). Old Limit : 4000 New Limit : 64k
Item: The maximum length of formulas (in characters). Old Limit : 1k characters New Limit : 8k characters
Item: The number of levels of nesting that Excel allows in formulas. Old Limit : 7 New Limit : 64
Item: Maximum number of arguments to a function. Old Limit : 30 New Limit : 255
Item: The number of characters that can be stored and displayed in a cell formatted as Text. Old Limit : 255 New Limit : 32k
Item: Maximum number of items found by “Find All”. Old Limit : ~64k (65472) New Limit : ~2 Billion
Item: Number of rows allowed in a Pivot Table. Old Limit : 64k New Limit : 1M
Item: Number of columns allowed in a Pivot Table. Old Limit : 255 New Limit : 16k
Item: Maximum number of unique items within a single Pivot Field. Old Limit : 32k New Limit : 1M
Item: Length of the MDX name for a Pivot Table item; also the string length for a relational Pivot Table. Old Limit : 255 characters New Limit : 32k
Item: The length at which fields’ labels are truncated when added to PivotTable; this also includes caption length limitations. Old Limit : 255 New Limit : 32k
Item: The number of fields (as seen in the field list) that a single PivotTable can have. Old Limit : 255 New Limit : 16k
Item: The number of cells that may depend on a single area before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations). Old Limit : 8k New Limit : Limited by available memory
Item: The number of different areas in a sheet that may have dependencies before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations). Old Limit : 64k New Limit : Limited by available memory
Item: The number of array formulas in a worksheet that can refer to another (given) worksheet. Old Limit : 65k New Limit : Limited by available memory
Item: The number of categories that custom functions can be bucketed into. Old Limit : 32 New Limit : 255
Item: The number of characters that may be updated in a non-resident external workbook reference. Old Limit : 255 New Limit : 32k
Item: Number of rows of a column or columns that can be referred to in an array formula. Old Limit : 65,335 New Limit : Limitation removed (full-column references allowed)

About "Sorting":
Excel 2007 has gone from a choice of three columns to sort by to a choice of sixty four columns and now has the ability to sort by colour. Use AutoFilter to simplify filtering. The AutoFilter's drop-down menu allows you to select multiple items by checking them off.

About: "Views":
It is now possible to work in Page Layout View or Page Break View. In Page Layout View you can edit the headers and footers.

A resizeable formula bar that prevents long formulae from spilling over onto the spreadsheet.

About "Status Bar":
The status bar includes statistical information about the selected cells. (average, count and sum)

About "Name Manager":
The Name Manager helps organize, update and manage multiple name ranges from a central location.

About "Conditional Formatting":
Conditional formatting has been expanded from three conditions to some larger number, and they are no longer mutually exclusive. Conditional formatting now includes rich data visualizations like the insertion of bars, colour gradients or icons within a cell. Once applied the formulas associated with the conditional formatting can be adjusted using the Conditional Formatting Manager.

Microsoft has enhanced how tables are handled. By right clicking within the table and choosing Create Table, Excel 2007 will automatically label columns, create AutoFilters and display other relevant tools.

Excel 2007 has improved support for tables allowing you to create, format, expand, and refer to tables within formula. When analyzing data contained in a large table, Excel 2007 keeps table headings in view while you scroll.

About "Functions":
There are more functions. There are 343 functions with 51 new functions. New categories of Engineering and Cube have been added. The following are the new functions. The Engineering functions are now native to Excel, but most or all used to be part of the Analysis Toolpak.

The seven new CUBE functions are used to fetch data from OLAP cubes and place that data anywhere on an Excel spreadsheet.

In addition there are double byte versions of FIND, LEFT, LEN, MID, REPLACE, RIGHT and SEARCH (FINDB, LEFTB, LENB, MIDB, REPLACEB, RIGHTB and SEARCHB)

Cube: CUBEKPIMEMBER: Returns a key performance indicator (KPI) name,
property, and measure, and displays the name and property in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, used to monitor an organization's performance.

Cube: CUBEMEMBER: Returns a member or tuple in a cube hierarchy. Use to validate that the member or tuple exists in the cube.

Cube: CUBEMEMBERPROPERTY: Returns the value of a member property in the cube. Use to validate that a member name exists within the cube and to return the specified property for this member.

Cube: CUBERANKEDMEMBER: Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or top 10 students.

Cube: CUBESET: Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Office Excel.

Cube: CUBESETCOUNT: Returns the number of items in a set.

Cube: CUBEVALUE: Returns an aggregated value from a cube.

Engineering: BESSELI: Returns the modified Bessel function In(x)

Engineering: BESSELJ: Returns the Bessel function Jn(x)

Engineering: BESSELK: Returns the modified Bessel function Kn(x)

Engineering: BESSELY: Returns the Bessel function Yn(x)

Engineering: BIN2DEC: Converts a binary number to decimal

Engineering: BIN2HEX: Converts a binary number to hexadecimal

Engineering: BIN2OCT: Converts a binary number to octal

Engineering: COMPLEX: Converts real and imaginary coefficients into a complex number

Engineering: CONVERT: Converts a number from one measurement system to another

Engineering: DEC2BIN: Converts a decimal number to binary

Engineering: DEC2HEX: Converts a decimal number to hexadecimal

Engineering: DEC2OCT: Converts a decimal number to octal

Engineering: DELTA: Tests whether two values are equal

Engineering: ERF: Returns the error function

Engineering: ERFC: Returns the complementary error function

Engineering: GESTEP: Tests whether a number is greater than a threshold value

Engineering: HEX2BIN: Converts a hexadecimal number to binary

Engineering: HEX2DEC: Converts a hexadecimal number to decimal

Engineering: HEX2OCT: Converts a hexadecimal number to octal

Engineering: IMABS: Returns the absolute value (modulus) of a complex number

Engineering: IMAGINARY: Returns the imaginary coefficient of a complex number

Engineering: IMARGUMENT: Returns the argument theta, an angle expressed in radians

Engineering: IMCONJUGATE: Returns the complex conjugate of a complex number

Engineering: IMCOS: Returns the cosine of a complex number

Engineering: IMDIV: Returns the quotient of two complex numbers

Engineering: IMEXP: Returns the exponential of a complex number

Engineering: IMLN: Returns the natural logarithm of a complex number

Engineering: IMLOG10: Returns the base-10 logarithm of a complex number

Engineering: IMLOG2: Returns the base-2 logarithm of a complex number

Engineering: IMPOWER: Returns a complex number raised to an integer power

Engineering: IMPRODUCT: Returns the product of from 2 to 29 complex numbers

Engineering: IMREAL: Returns the real coefficient of a complex number

Engineering: IMSIN: Returns the sine of a complex number

Engineering: IMSQRT: Returns the square root of a complex number

Engineering: IMSUB: Returns the difference between two complex numbers

Engineering: IMSUM: Returns the sum of complex numbers

Engineering: OCT2BIN: Converts an octal number to binary

Engineering: OCT2DEC: Converts an octal number to decimal

Engineering: OCT2HEX: Converts an octal number to hexadecimal

External: EUROCONVERT: Converts a number to euros, converts a number from euros to a euro member currency, or converts a number from one euro member currency to another by using the euro as an intermediary (triangulation)

External: SQL.REQUEST: Connects with an external data source and runs a query from a worksheet, then returns the result as an array without the need for macro programming

Math and Trigonometry: SUMIFS: Adds the cells in a range that meet multiple criteria

Statistical: AVERAGEIF: Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria

Statistical: AVERAGEIFS: Returns the average (arithmetic mean) of all cells that meet multiple criteria.

GETPIVOTDATA was moved from the"Database and List Management" category to the "Lookup and Reference" category.

About "Charts":
The charts have been improved. There are dramatic visual effects such as 3-D, soft shadowing, anti-aliasing and glow. The same chart engine is used in Word 2007 and PowerPoint 2007.

About "Pivot Tables and PivotCharts":
PivotTable views allow you to quickly reorient your data to help you answer multiple questions. Find the answers you need faster because Office Excel 2007 will help you to create and use PivotTable views more easily. They use data fields to reorient data quickly. PivotCharts allow for a more graphical representation of a PivotTable.

About "SmartArt Graphics":
Smart Art allows you to add more complex graphics to the spreadsheet.

About "Business Dashboard":
Business dashboards can be easily created from spreadsheets to track key performance indicators (KPIs) and then they can be shared through a Web browser.

About "Data Connection Library":
The library allows you to import external data into a spreadsheet by using preconfigured external sources of informtion.

*** Excel 4 macros will still working on Excel 2007.

About "File Formats":
To address users concern over having their information in a proprietary format, Microsoft has created XPS (XML Paper Specification), an XML based file format that is easily readable. The downside of XML format is that it is not an efficient storage format. To overcome this issue Microsoft compresses the information using the Zip format. Additionally, because the file format is XML-based, with an open, royalty-free license, developers can more easily build solutions that utilize Office Excel 2007 document contents and metadata.

The Excel XML Format is compatible with Microsoft Office 2003, Office XP, and Office 2000 with the addition of a file format converter patch, available from Microsoft Office Online and Microsoft Update. Users of Office 2003, Office XP, and Office 2000 can open, edit, and save files using the new Excel XML Format.

It is a full-fidelity file format just like the Microsoft Office Open XML Formats. It is based on the same technologies as the Office Open XML Formats.

About "Acrobat files":
Excel 2007 spreadsheets will also be able to export to PDF. A special PDF writer will no longer be required.

Everything that i found is this for at the moment. I going to share again if i can find some new informations.

So, my comment is; Excel 2007 is the most optional, user-friendly and flexible version of all.

The first attemption for me before get a one: Learning newest Functions! :)

If anyone can find some samples about newest Functions please share here with us. And i promise i am going to do like that.

Best regards. :hey:

[birol_mumcu]

[QUOTE=If anyone can find some samples about newest Functions please share here with us. And i promise i am going to do like that.[/QUOTE]


hi
The seven new CUBE functions are used to;
CUBEKPIMEMBER Returns a key performance indicator (KPI) property and displays the KPI name in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, that is used to monitor an organization's performance.
details...
Returns a key performance indicator (KPI) property and displays the KPI name in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, that is used to monitor an organization's performance.

Note The CUBEKPIMEMBER function is supported only when the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or later data source.

Syntax

CUBEKPIMEMBER(connection,kpi_name,kpi_property,cap tion)

Connection is a text string of the name of the connection to the cube.

Kpi_name is a text string of the name of the KPI in the cube.

Kpi_property is the KPI component returned and can be one of the following:

Integer Enumerated constant Description
1 KPIValue The actual value
2 KPIGoal A target value
3 KPIStatus The state of the KPI at a specific moment in time
4 KPITrend A measure of the value over time
5 KPIWeight A relative importance assigned to the KPI
6 KPICurrentTimeMember A temporal context for the KPI


If you specify KPIValue for kpi_property, only kpi_name is displayed in the cell.

Caption is an alternative text string that is displayed in the cell instead of kpi_name and kpi_property.

Remarks

When the CUBEKPIMEMBER function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
To use the KPI in a calculation, specify the CUBEKPIMEMBER function as a member_expression argument in the CUBEVALUE function.
If the connection name is not a valid workbook connection that is stored in the workbook, CUBEKPIMEMBER returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBEKPIMEMBER returns a #NAME? error value.
CUBEKPIMEMBER returns a #N/A error value when kpi_name or kpi_property is invalid.
CUBEKPIMEMBER may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)
Examples

=CUBEKPIMEMBER("Sales","MySalesKPI",1)

=CUBEKPIMEMBER("Sales","MySalesKPI", KPIGoal,"Sales KPI Goal")

[birol_mumcu]

the second one;
CUBEMEMBER Returns a member or tuple from the cube. Use to validate that the member or tuple exists in the cube
Syntax

CUBEMEMBER(connection,member_expression,caption)

Connection is a text string of the name of the connection to the cube.

Member_expression is a text string of a multidimensional expression (MDX) that evaluates to a unique member in the cube. Alternatively, member_expression can be a tuple, specified as a cell range or an array constant.

Caption is a text string displayed in the cell instead of the caption, if one is defined, from the cube. When a tuple is returned, the caption used is the one for the last member in the tuple.

Remarks

When the CUBEMEMBER function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
When you use CUBEMEMBER as an argument to another CUBE function, the MDX expression that identifies the member or tuple is used by that CUBE function, not the displayed value in the cell of the CUBEMEMBER function.
If the connection name is not a valid workbook connection stored in the workbook, CUBEMEMBER returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBEMEMBER returns a #NAME? error value.
If at least one element within the tuple is invalid, CUBEMEMBER returns a #VALUE! error value.
If member_expression is longer than 255 characters, which is the limit for an argument to a function, CUBEMEMBER returns a #VALUE! error value. To use text strings longer than 255 characters, enter the text string in a cell (for which the limit is 32,767 characters), and then use a cell reference as the argument.
CUBEMEMBER returns a #N/A error value when:
The member_expression syntax is incorrect.
The member specified by the MDX text string doesn't exist in the cube.
The tuple is invalid because there is no intersection for the specified values. (This can occur with multiple elements from the same hierarchy.)
The set contains at least one member with a different dimension than the other members.
CUBEMEMBER may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)
Examples

=CUBEMEMBER("Sales","[Time].[Fiscal].[2004]")

=CUBEMEMBER($A$1,D$12)

=CUBEMEMBER("Sales",(B4, C6, D5),"SalesFor2004")

=CUBEMEMBER("xlextdat8 FoodMart 2000 Sales","([Product].[Food],[Time].[1997])")

=CUBEMEMBER($A$1,C$12:D$12)

[birol_mumcu]

the third one;
CUBEMEMBERPROPERTY Returns the value of a member property from the cube. Use to validate that a member name exists within the cube and to return the specified property for this member
Syntax

CUBEMEMBERPROPERTY(connection,member_expression,pr operty)

Connection is a text string of the name of the connection to the cube.

Member_expression is a text string of a multidimensional expression (MDX) of a member within the cube.

Property is a text string of the name of the property returned or a reference to a cell that contains the name of the property.

Remarks

When the CUBEMEMBERPROPERTY function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
If the connection name is not a valid workbook connection stored in the workbook, CUBEMEMBERPROPERTY returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBEMEMBERPROPERTY returns a #NAME? error value.
If the member_expression syntax is incorrect or if the member specified by member_expression doesn't exist in the cube, CUBEMEMBERPROPERTY returns a #N/A error value.
CUBEMEMBERPROPERTY may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)
Examples

=CUBEMEMBERPROPERTY("Sales","[Time].[Fiscal].[2004]",$A$3)

=CUBEMEMBERPROPERTY("Sales","[Store].[MyFavoriteStore]","[Store].[Store Name].[Store Sqft]")

Syntax

CUBEMEMBERPROPERTY(connection,member_expression,pr operty)

Connection is a text string of the name of the connection to the cube.

Member_expression is a text string of a multidimensional expression (MDX) of a member within the cube.

Property is a text string of the name of the property returned or a reference to a cell that contains the name of the property.

Remarks

When the CUBEMEMBERPROPERTY function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
If the connection name is not a valid workbook connection stored in the workbook, CUBEMEMBERPROPERTY returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBEMEMBERPROPERTY returns a #NAME? error value.
If the member_expression syntax is incorrect or if the member specified by member_expression doesn't exist in the cube, CUBEMEMBERPROPERTY returns a #N/A error value.
CUBEMEMBERPROPERTY may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)
Examples

=CUBEMEMBERPROPERTY("Sales","[Time].[Fiscal].[2004]",$A$3)

=CUBEMEMBERPROPERTY("Sales","[Store].[MyFavoriteStore]","[Store].[Store Name].[Store Sqft]")

[birol_mumcu]

the fourth one;
CUBERANKEDMEMBER Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or the top 10 students.
Syntax

CUBERANKEDMEMBER(connection,set_expression,rank,ca ption)

Connection is a text string of the name of the connection to the cube.

Set_expression is a text string of a set expression, such as "{[Item1].children}". Set_expression can also be the CUBESET function, or a reference to a cell that contains the CUBESET function.

Rank is an integer value specifying the top value to return. If rank is a value of 1, it returns the top value, if rank is a value of 2, it returns the second most top value, and so on. To return the top 5 values, use CUBERANKEDMEMBER five times, specifying a different rank, 1 through 5, each time.

Caption is a text string displayed in the cell instead of the caption, if one is defined, from the cube.

Remarks

When the CUBERANKEDMEMBER function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
If the connection name is not a valid workbook connection stored in the workbook, CUBERANKEDMEMBER returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBERANKEDMEMBER returns a #NAME? error value.
CUBERANKEDMEMBER returns a #N/A error value when the syntax of set_expression is incorrect or when the set contains at least one member with a different dimension than the other members.
Examples

=CUBERANKEDMEMBER("Sales",$D$4,1,"Top Month")

=CUBERANKEDMEMBER("Sales",CUBESET("Sales","Summer","[2004].[June]","[2004].[July]","[2004].[August]"),3,"Top Month")

Tip To return the bottom n values, use the sort_order and sort_by arguments of the CUBESET function to reverse the order of the set so that the top values in the sorted set are the bottom values. For example, CUBERANKEDMEMBER ("Sales", $D$4,1) returns the last member, CUBERANKEDMEMBER ("Sales", $D$4, 2) returns the next to last member, and so on.

[birol_mumcu]

the fifth one
CUBESET Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Office Excel.
Syntax

CUBESET(connection,set_expression,caption,sort_ord er,sort_by)

Connection is a text string of the name of the connection to the cube.

Set_expression is a text string of a set expression that results in a set of members or tuples. Set_expression can also be a cell reference to an Excel range that contains one or more members, tuples, or sets included in the set.

Caption is a text string that is displayed in the cell instead of the caption, if one is defined, from the cube.

Sort_order is the type of sort, if any, to perform and can be one of the following:

Integer Enumerated
constant Description Sort_by argument
0 SortNone Leaves the set in existing order. Ignored
1 SortAscending Sorts set in ascending order by sort_by. Required
2 SortDescending Sorts set in descending order by sort_by. Required
3 SortAlphaAscending Sorts set in alpha ascending order. Ignored
4 Sort_Alpha_Descending Sorts set in alpha descending order. Ignored
5 Sort_Natural_Ascending Sorts set in natural ascending order. Ignored
6 Sort_Natural_Descending Sorts set in natural descending order. Ignored

The default value is 0. An alpha sort for a set of tuples sorts on the last element in each tuple. For more information on these different sort orders, see the Microsoft Office SQL Analysis Services help system.

Sort_by is a text string of the value by which to sort. For example, to get the city with the highest sales, set_expression would be a set of cities, and sort_by would be the sales measure. Or, to get the city with the highest population, set_expression would be a set of cities, and sort_by would be the population measure. If sort_order requires sort_by, and sort_by is omitted, CUBESET returns the #VALUE! error message.

Remarks

When the CUBESET function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
If the connection name is not a valid workbook connection stored in the workbook, CUBESET returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBESET returns a #NAME? error value.
If the set_expression syntax is incorrect or the set contains at least one member with a different dimension than the other members, CUBESET returns a #N/A error value.
If set_expression is longer than 255 characters, which is the limit for an argument to a function, CUBESET returns a #VALUE! error value. To use text strings longer than 255 characters, enter the text string in a cell (for which the limit is 32,767 characters), and then use a cell reference as the argument.
CUBESET may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)
Examples

=CUBESET("Finance","Order([Product].[Product].[Product Category].Members,[Measures].[Unit Sales],ASC)","Products")

=CUBESET("Sales","[Product].[All Products].Children","Products",1,"[Measures].[Sales Amount]")

[birol_mumcu]

the sixth one
Returns the number of items in a set.

Syntax

CUBESETCOUNT(set)

Set is a text string of a Microsoft Office Excel expression that evaluates to a set defined by the CUBESET function. Set can also be the CUBESET function, or a reference to a cell that contains the CUBESET function.

Remark

When the CUBESETCOUNT function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.

Examples

=CUBESETCOUNT(A3)

=CUBESETCOUNT(CUBESET("Sales","[Product].[All Products].Children","Products",1,"[Measures].[Sales Amount]"))

[birol_mumcu]

the seventh one
CUBEVALUE Returns an aggregated value from the cube.
Returns an aggregated value from the cube.

Syntax

CUBEVALUE(connection,member_expression1,member_exp ression2…)

Connection is a text string of the name of the connection to the cube.

Member_expression is a text string of a multidimensional expression (MDX) that evaluates to a member or tuple within the cube. Alternatively, member_expression can be a set defined with the CUBESET function. Use member_expression as a slicer to define the portion of the cube for which the aggregated value is returned. If no measure is specified in member_expression, the default measure for that cube is used.

Remarks

When the CUBEVALUE function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
If a cell reference is used for member_expression, and that cell reference contains a CUBE function, then member_expression uses the MDX expression for the item in the referenced cell, and not the value displayed in that referenced cell.
If the connection name is not a valid workbook connection stored in the workbook, CUBEVALUE returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBEVALUE returns a #NAME? error value.
If at least one element within the tuple is invalid, CUBEVALUE returns a #VALUE! error value.
CUBEVALUE returns a #N/A error value when:
The member_expression syntax is incorrect.
The member specified by member_expression doesn't exist in the cube.
The tuple is invalid because there is no intersection for the specified values. (This can occur with multiple elements from the same hierarchy.)
The set contains at least one member with a different dimension than the other members.
CUBEVALUE may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)
Issue: Null values are converted to zero-length strings

In Microsoft Office Excel 2007, if a cell has no data, because you never changed it or you deleted the contents, the cell contains an empty value. In many database systems, an empty value is called a Null value. An empty or Null value literally means "No value". However, a formula can never return an empty string or Null value. A formula always returns one of three values: a number value; a text value, which may be a zero-length string, or an error value, such as #NUM! or #VALUE.
If a formula contains a CUBEVALUE function connected to an Online Analytical Processing (OLAP) database and a query to this database results in a Null value, Excel converts this Null value to a zero-length string, even if the formula would otherwise return a number value. This can lead to a situation where a range of cells contain a combination of numeric and zero-length string values, and this situation can affect the results of other formulas that reference that range of cells. For example, if A1 and A3 contain numbers, and A2 contains a formula with a CUBEVALUE function that returns a zero-length string, the following formula would return a #VALUE! error:

=A1+A2+A3
To prevent this, you can test for a zero-length string by using the ISTEXT function and by using the IF function to replace the zero-length with a 0 (zero) as the following example shows:

=IF(ISTEXT(A1),0,A1)+IF(ISTEXT(A2),0,A2)+IF(ISTEXT (A3),0,A3)
Alternatively, you can nest the CUBEVALUE function in an IF condition that returns a 0 value if the CUBEVALUE function evaluates to a zero-length string as the following example shows:

=IF (CUBEVALUE("Sales","[Measures].[Profit]","[Time].[2004]","[All Product].[Beverages]")="", 0, CUBEVALUE("Sales","[Measures].[Profit]","[Time].[2004]","[All Product].[Beverages]"))
Note that the SUM function does not require this test for a zero-length string because it automatically ignores zero-length strings when calculating its return value.

Examples

=CUBEVALUE("Sales","[Measures].[Profit]","[Time].[2004]","[All Product].[Beverages]")

=CUBEVALUE($A$1,"[Measures].[Profit]",D$12,$A23)

=CUBEVALUE("Sales",$B$7,D$12,$A23)

[birol_mumcu]

Hi,

About "Functions":
There are more functions. There are 343 functions with 51 new functions. New categories of Engineering and Cube have been added. The following are the new functions. The Engineering functions are now native to Excel, but most or all used to be part of the Analysis Toolpak.


Engineering: BESSELI: Returns the modified Bessel function In(x)

Engineering: BESSELJ: Returns the Bessel function Jn(x)

Engineering: BESSELK: Returns the modified Bessel function Kn(x)

Engineering: BESSELY: Returns the Bessel function Yn(x)

Engineering: BIN2DEC: Converts a binary number to decimal

Engineering: BIN2HEX: Converts a binary number to hexadecimal

Engineering: BIN2OCT: Converts a binary number to octal

Engineering: COMPLEX: Converts real and imaginary coefficients into a complex number

Engineering: CONVERT: Converts a number from one measurement system to another

Engineering: DEC2BIN: Converts a decimal number to binary

Engineering: DEC2HEX: Converts a decimal number to hexadecimal

Engineering: DEC2OCT: Converts a decimal number to octal

Engineering: DELTA: Tests whether two values are equal

Engineering: ERF: Returns the error function

Engineering: ERFC: Returns the complementary error function

Engineering: GESTEP: Tests whether a number is greater than a threshold value

Engineering: HEX2BIN: Converts a hexadecimal number to binary

Engineering: HEX2DEC: Converts a hexadecimal number to decimal

Engineering: HEX2OCT: Converts a hexadecimal number to octal

Engineering: IMABS: Returns the absolute value (modulus) of a complex number

Engineering: IMAGINARY: Returns the imaginary coefficient of a complex number

Engineering: IMARGUMENT: Returns the argument theta, an angle expressed in radians

Engineering: IMCONJUGATE: Returns the complex conjugate of a complex number

Engineering: IMCOS: Returns the cosine of a complex number

Engineering: IMDIV: Returns the quotient of two complex numbers

Engineering: IMEXP: Returns the exponential of a complex number

Engineering: IMLN: Returns the natural logarithm of a complex number

Engineering: IMLOG10: Returns the base-10 logarithm of a complex number

Engineering: IMLOG2: Returns the base-2 logarithm of a complex number

Engineering: IMPOWER: Returns a complex number raised to an integer power

Engineering: IMPRODUCT: Returns the product of from 2 to 29 complex numbers

Engineering: IMREAL: Returns the real coefficient of a complex number

Engineering: IMSIN: Returns the sine of a complex number

Engineering: IMSQRT: Returns the square root of a complex number

Engineering: IMSUB: Returns the difference between two complex numbers

Engineering: IMSUM: Returns the sum of complex numbers

Engineering: OCT2BIN: Converts an octal number to binary

Engineering: OCT2DEC: Converts an octal number to decimal

Engineering: OCT2HEX: Converts an octal number to hexadecimal


The first attemption for me before get a one: Learning newest Functions! :)

If anyone can find some samples about newest Functions please share here with us. And i promise i am going to do like that.

Best regards. :hey:


hi again;
there is some exaamples for the new function of "Engineering Functions" in excel2007. and the examples are ;
1. BESSELI

Returns the modified Bessel function, which is equivalent to the Bessel function evaluated for purely imaginary arguments.

Syntax

BESSELI(x,n)

X is the value at which to evaluate the function.

N is the order of the Bessel function. If n is not an integer, it is truncated.

Remarks

If x is nonnumeric, BESSELI returns the #VALUE! error value.
If n is nonnumeric, BESSELI returns the #VALUE! error value.
If n < 0, BESSELI returns the #NUM! error value.
The n-th order modified Bessel function of the variable x is:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.



Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

=BESSELI(1.5, 1) Modified Bessel function at 1.5 with an order of 1 (0.981666)







2. BESSELJ(x,n)

X is the value at which to evaluate the function.

N is the order of the Bessel function. If n is not an integer, it is truncated.

Remarks

If x is nonnumeric, BESSELJ returns the #VALUE! error value.
If n is nonnumeric, BESSELJ returns the #VALUE! error value.
If n < 0, BESSELJ returns the #NUM! error value.
The n-th order Bessel function of the variable x is:

Example

=BESSELJ(1.9, 2) Bessel function at 1.9 with an order of 2 (0.329926)

[birol_mumcu]

3.BESSELK
Returns the modified Bessel function, which is equivalent to the Bessel functions evaluated for purely imaginary arguments.

Syntax

BESSELK(x,n)

X is the value at which to evaluate the function.

N is the order of the function. If n is not an integer, it is truncated.

Remarks

If x is nonnumeric, BESSELK returns the #VALUE! error value.
If n is nonnumeric, BESSELK returns the #VALUE! error value.
If n < 0, BESSELK returns the #NUM! error value.
The n-th order modified Bessel function of the variable x is:


where Jn and Yn are the J and Y Bessel functions, respectively.
Example


Formula Description (Result)
=BESSELK(1.5, 1) Modified Bessel function at 1.5 with an order of 1 (0.277388)

[birol_mumcu]

BESSELY
Returns the Bessel function, which is also called the Weber function or the Neumann function.

Syntax

BESSELY(x,n)

X is the value at which to evaluate the function.

N is the order of the function. If n is not an integer, it is truncated.

Remarks

If x is nonnumeric, BESSELY returns the #VALUE! error value.
If n is nonnumeric, BESSELY returns the #VALUE! error value.
If n < 0, BESSELY returns the #NUM! error value.
The n-th order Bessel function of the variable x is:

Example


Formula Description (Result)
=BESSELY(2.5, 1) Weber's Bessel function at 2.5 and an order of 1 (0.145918)

[birol_mumcu]

5.BIN2DEC
Converts a binary number to decimal.

Syntax

BIN2DEC(number)

Number is the binary number you want to convert. Number cannot contain more than 10 characters (10 bits). The most significant bit of number is the sign bit. The remaining 9 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Remark

If number is not a valid binary number, or if number contains more than 10 characters (10 bits), BIN2DEC returns the #NUM! error value.

Example


=BIN2DEC(1100100) Converts binary 1100100 to decimal (100)
=BIN2DEC(1111111111) Converts binary 1111111111 to decimal (-1)

[birol_mumcu]

6. BIN2HEX
Converts a binary number to hexadecimal.

Syntax

BIN2HEX(number,places)

Number is the binary number you want to convert. Number cannot contain more than 10 characters (10 bits). The most significant bit of number is the sign bit. The remaining 9 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Places is the number of characters to use. If places is omitted, BIN2HEX uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number is not a valid binary number, or if number contains more than 10 characters (10 bits), BIN2HEX returns the #NUM! error value.
If number is negative, BIN2HEX ignores places and returns a 10-character hexadecimal number.
If BIN2HEX requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, BIN2HEX returns the #VALUE! error value.
If places is negative, BIN2HEX returns the #NUM! error value.
Example

=BIN2HEX(11111011, 4) Converts binary 11111011 to hexadecimal with 4 characters (00FB)
=BIN2HEX(1110) Converts binary 1110 to hexadecimal (E)
=BIN2HEX(1111111111) Converts binary 1111111111 to hexadecimal (FFFFFFFFFF)

[birol_mumcu]

7.BIN2OCT
Converts a binary number to octal.

Syntax

BIN2OCT(number,places)

Number is the binary number you want to convert. Number cannot contain more than 10 characters (10 bits). The most significant bit of number is the sign bit. The remaining 9 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Places is the number of characters to use. If places is omitted, BIN2OCT uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number is not a valid binary number, or if number contains more than 10 characters (10 bits), BIN2OCT returns the #NUM! error value.
If number is negative, BIN2OCT ignores places and returns a 10-character octal number.
If BIN2OCT requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, BIN2OCT returns the #VALUE! error value.
If places is negative, BIN2OCT returns the #NUM! error value.
Example

=BIN2OCT(1001, 3) Converts binary 1001 to octal with 3 characters (011)
=BIN2OCT(1100100) Converts binary 1100100 to octal (144)
=BIN2OCT(1111111111) Converts binary 1111111111 to octal (7777777777)

[birol_mumcu]

8.COMPLEX
Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj.

Syntax

COMPLEX(real_num,i_num,suffix)

Real_num is the real coefficient of the complex number.

I_num is the imaginary coefficient of the complex number.

Suffix is the suffix for the imaginary component of the complex number. If omitted, suffix is assumed to be "i".

Note All complex number functions accept "i" and "j" for suffix, but neither "I" nor "J". Using uppercase results in the #VALUE! error value. All functions that accept two or more complex numbers require that all suffixes match.

Remarks

If real_num is nonnumeric, COMPLEX returns the #VALUE! error value.
If i_num is nonnumeric, COMPLEX returns the #VALUE! error value.
If suffix is neither "i" nor "j", COMPLEX returns the #VALUE! error value.
Example

=COMPLEX(3,4) Complex number with 3 and 4 as the real and imaginary coefficients (3 + 4i)
=COMPLEX(3,4,"j") Complex number with 3 and 4 as the real and imaginary coefficients, and j as the suffix (3 + 4j)
=COMPLEX(0,1) Complex number with 0 and 1 as the real and imaginary coefficients (i)
=COMPLEX(1,0) Complex number with 1 and 0 as the real and imaginary coefficients (1)

[birol_mumcu]

9.CONVERT
Converts a number from one measurement system to another. For example, CONVERT can translate a table of distances in miles to a table of distances in kilometers.

Syntax

CONVERT(number,from_unit,to_unit)

Number is the value in from_units to convert.

From_unit is the units for number.

To_unit is the units for the result. CONVERT accepts the following text values (in quotation marks) for from_unit and to_unit.

Weight and mass From_unit or to_unit
Gram "g"
Slug "sg"
Pound mass (avoirdupois) "lbm"
U (atomic mass unit) "u"
Ounce mass (avoirdupois) "ozm"

Distance From_unit or to_unit
Meter "m"
Statute mile "mi"
Nautical mile "Nmi"
Inch "in"
Foot "ft"
Yard "yd"
Angstrom "ang"
Pica (1/72 in.) "Pica"

Time From_unit or to_unit
Year "yr"
Day "day"
Hour "hr"
Minute "mn"
Second "sec"

Pressure From_unit or to_unit
Pascal "Pa" (or "p")
Atmosphere "atm" (or "at")
mm of Mercury "mmHg"

Force From_unit or to_unit
Newton "N"
Dyne "dyn" (or "dy")
Pound force "lbf"

Energy From_unit or to_unit
Joule "J"
Erg "e"
Thermodynamic calorie "c"
IT calorie "cal"
Electron volt "eV" (or "ev")
Horsepower-hour "HPh" (or "hh")
Watt-hour "Wh" (or "wh")
Foot-pound "flb"
BTU "BTU" (or "btu")

Power From_unit or to_unit
Horsepower "HP" (or "h")
Watt "W" (or "w")

Magnetism From_unit or to_unit
Tesla "T"
Gauss "ga"

Temperature From_unit or to_unit
Degree Celsius "C" (or "cel")
Degree Fahrenheit "F" (or "fah")
Kelvin "K" (or "kel")

Liquid measure From_unit or to_unit
Teaspoon "tsp"
Tablespoon "tbs"
Fluid ounce "oz"
Cup "cup"
U.S. pint "pt" (or "us_pt")
U.K. pint "uk_pt"
Quart "qt"
Gallon "gal"
Liter "l" (or "lt")


The following abbreviated unit prefixes can be prepended to any metric from_unit or to_unit.

Prefix Multiplier Abbreviation
exa 1E+18 "E"
peta 1E+15 "P"
tera 1E+12 "T"
giga 1E+09 "G"
mega 1E+06 "M"
kilo 1E+03 "k"
hecto 1E+02 "h"
dekao 1E+01 "e"
deci 1E-01 "d"
centi 1E-02 "c"
milli 1E-03 "m"
micro 1E-06 "u"
nano 1E-09 "n"
pico 1E-12 "p"
femto 1E-15 "f"
atto 1E-18 "a"


Remarks

If the input data types are incorrect, CONVERT returns the #VALUE! error value.
If the unit does not exist, CONVERT returns the #N/A error value.
If the unit does not support an abbreviated unit prefix, CONVERT returns the #N/A error value.
If the units are in different groups, CONVERT returns the #N/A error value.
Unit names and prefixes are case-sensitive.
Example


=CONVERT(1.0, "lbm", "kg") Converts 1 pound mass to kilograms (0.453592)
=CONVERT(68, "F", "C") Converts 68 degrees Fahrenheit to Celsius (20)
=CONVERT(2.5, "ft", "sec") Data types are not the same so an error is returned (#N/A)
=CONVERT(CONVERT(100,"ft","m"),"ft","m") Converts 100 square feet into square meters (9.290304).

[birol_mumcu]

10.DEC2BIN
Converts a decimal number to binary.

Syntax

DEC2BIN(number,places)

Number is the decimal integer you want to convert. If number is negative, valid place values are ignored and DEC2BIN returns a 10-character (10-bit) binary number in which the most significant bit is the sign bit. The remaining 9 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Places is the number of characters to use. If places is omitted, DEC2BIN uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number < -512 or if number > 511, DEC2BIN returns the #NUM! error value.
If number is nonnumeric, DEC2BIN returns the #VALUE! error value.
If DEC2BIN requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, DEC2BIN returns the #VALUE! error value.
If places is zero or negative, DEC2BIN returns the #NUM! error value.
Example


=DEC2BIN(9, 4) Converts decimal 9 to binary with 4 characters (1001)
=DEC2BIN(-100) Converts decimal -100 to binary (1110011100)

[birol_mumcu]

11.DEC2HEX
Converts a decimal number to hexadecimal.

Syntax

DEC2HEX(number,places)

Number is the decimal integer you want to convert. If number is negative, places is ignored and DEC2HEX returns a 10-character (40-bit) hexadecimal number in which the most significant bit is the sign bit. The remaining 39 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Places is the number of characters to use. If places is omitted, DEC2HEX uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number < -549,755,813,888 or if number > 549,755,813,887, DEC2HEX returns the #NUM! error value.
If number is nonnumeric, DEC2HEX returns the #VALUE! error value.
If DEC2HEX requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, DEC2HEX returns the #VALUE! error value.
If places is negative, DEC2HEX returns the #NUM! error value.
Example

=DEC2HEX(100, 4) Converts decimal 100 to hexadecimal with 4 characters (0064)
=DEC2HEX(-54) Converts decimal -54 to hexadecimal (FFFFFFFFCA)

[birol_mumcu]

12.DEC2OCT
Converts a decimal number to octal.

Syntax

DEC2OCT(number, places)

Number is the decimal integer you want to convert. If number is negative, places is ignored and DEC2OCT returns a 10-character (30-bit) octal number in which the most significant bit is the sign bit. The remaining 29 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Places is the number of characters to use. If places is omitted, DEC2OCT uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number < -536,870,912 or if number > 536,870,911, DEC2OCT returns the #NUM! error value.
If number is nonnumeric, DEC2OCT returns the #VALUE! error value.
If DEC2OCT requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, DEC2OCT returns the #VALUE! error value.
If places is negative, DEC2OCT returns the #NUM! error value.
Example

=DEC2OCT(58, 3) Converts decimal 58 to octal (072)
=DEC2OCT(-100) Converts decimal to octal (7777777634)

[birol_mumcu]

13.DELTA
Tests whether two values are equal. Returns 1 if number1 = number2; returns 0 otherwise. Use this function to filter a set of values. For example, by summing several DELTA functions you calculate the count of equal pairs. This function is also known as the Kronecker Delta function.

Syntax

DELTA(number1,number2)

Number1 is the first number.

Number2 is the second number. If omitted, number2 is assumed to be zero.

Remarks

If number1 is nonnumeric, DELTA returns the #VALUE! error value.
If number2 is nonnumeric, DELTA returns the #VALUE! error value.
Example

=DELTA(5, 4) Checks whether 5 equals 4 (0)
=DELTA(5, 5) Checks whether 5 equals 5 (1)
=DELTA(0.5, 0) Checks whether 0.5 equals 0 (0)

[birol_mumcu]

14.ERF
Returns the error function integrated between lower_limit and upper_limit.

Syntax

ERF(lower_limit,upper_limit)

Lower_limit is the lower bound for integrating ERF.

Upper_limit is the upper bound for integrating ERF. If omitted, ERF integrates between zero and lower_limit.

Remarks

If lower_limit is nonnumeric, ERF returns the #VALUE! error value.
If lower_limit is negative, ERF returns the #NUM! error value.
If upper_limit is nonnumeric, ERF returns the #VALUE! error value.
If upper_limit is negative, ERF returns the #NUM! error value.



Example

=ERF(0.74500) Error function integrated between 0 and 0.74500 (0.707929)
=ERF(1) Error function integrated between 0 and 1 (0.842701)

[birol_mumcu]

15.ERFC
Returns the complementary ERF function integrated between x and infinity.

Syntax

ERFC(x)

X is the lower bound for integrating ERF.

Remarks

If x is nonnumeric, ERFC returns the #VALUE! error value.
If x is negative, ERFC returns the #NUM! error value.

Example


=ERFC(1) Complementary ERF function of 1 (0.1573)

[birol_mumcu]

16.GESTEP
Returns 1 if number ≥ step; returns 0 (zero) otherwise. Use this function to filter a set of values. For example, by summing several GESTEP functions you calculate the count of values that exceed a threshold.

Syntax

GESTEP(number,step)

Number is the value to test against step.

Step is the threshold value. If you omit a value for step, GESTEP uses zero.

Remark

If any argument is nonnumeric, GESTEP returns the #VALUE! error value.

Example


=GESTEP(5, 4) Checks whether 5 is greater than or equal to 4 (1)
=GESTEP(5, 5) Checks whether 5 is greater than or equal to 5 (1)
=GESTEP(-4, -5) Checks whether -4 is greater than or equal to -5 (1)
=GESTEP(-1, 0) Checks whether -1 is greater than or equal to 0 (0)

[birol_mumcu]

17.HEX2BIN
Converts a hexadecimal number to binary.

Syntax

HEX2BIN(number,places)

Number is the hexadecimal number you want to convert. Number cannot contain more than 10 characters. The most significant bit of number is the sign bit (40th bit from the right). The remaining 9 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Places is the number of characters to use. If places is omitted, HEX2BIN uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number is negative, HEX2BIN ignores places and returns a 10-character binary number.
If number is negative, it cannot be less than FFFFFFFE00, and if number is positive, it cannot be greater than 1FF.
If number is not a valid hexadecimal number, HEX2BIN returns the #NUM! error value.
If HEX2BIN requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, HEX2BIN returns the #VALUE! error value.
If places is negative, HEX2BIN returns the #NUM! error value.
Example


=HEX2BIN("F", 8) Converts hexadecimal F to binary, with 8 characters (00001111)
=HEX2BIN("B7") Converts hexadecimal B7 to binary (10110111)
=HEX2BIN("FFFFFFFFFF") Converts hexadecimal FFFFFFFFFF to binary (1111111111)

[birol_mumcu]

18.HEX2DEC
Converts a hexadecimal number to decimal.

Syntax

HEX2DEC(number)

Number is the hexadecimal number you want to convert. Number cannot contain more than 10 characters (40 bits). The most significant bit of number is the sign bit. The remaining 39 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Remark

If number is not a valid hexadecimal number, HEX2DEC returns the #NUM! error value.

Example


=HEX2DEC("A5") Converts hexadecimal A5 to decimal (165)
=HEX2DEC("FFFFFFFF5B") Converts hexadecimal FFFFFFFF5B to decimal (-165)
=HEX2DEC("3DA408B9") Converts hexadecimal 3DA408B9 to decimal (1034160313)

[birol_mumcu]

19.HEX2OCT
Converts a hexadecimal number to octal.

Syntax

HEX2OCT(number,places)

Number is the hexadecimal number you want to convert. Number cannot contain more than 10 characters. The most significant bit of number is the sign bit. The remaining 39 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Places is the number of characters to use. If places is omitted, HEX2OCT uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number is negative, HEX2OCT ignores places and returns a 10-character octal number.
If number is negative, it cannot be less than FFE0000000, and if number is positive, it cannot be greater than 1FFFFFFF.
If number is not a valid hexadecimal number, HEX2OCT returns the #NUM! error value.
If HEX2OCT requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, HEX2OCT returns the #VALUE! error value.
If places is negative, HEX2OCT returns the #NUM! error value.
Example


=HEX2OCT("F", 3) Converts hexadecimal F to octal with 3 characters (017)
=HEX2OCT("3B4E") Converts hexadecimal 3B4E to octal (35516)
=HEX2OCT("FFFFFFFF00") Converts hexadecimal FFFFFFFF00 to octal (7777777400)

[birol_mumcu]

20.IMABS
Returns the absolute value (modulus) of a complex number in x + yi or x + yj text format.

Syntax

IMABS(inumber)

Inumber is a complex number for which you want the absolute value.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The absolute value of a complex number is:




z = x + yi
Example


=IMABS("5+12i") Absolute value of 5+12i (13)

[birol_mumcu]

21.IMAGINARY
Returns the imaginary coefficient of a complex number in x + yi or x + yj text format.

Syntax

IMAGINARY(inumber)

Inumber is a complex number for which you want the imaginary coefficient.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
Example


=IMAGINARY("3+4i") Imaginary coefficient of the complex number 3+4i (4)
=IMAGINARY("0-j") Imaginary coefficient of the complex number 0-j (-1)
=IMAGINARY(4) Imaginary coefficient 4 (0)

[birol_mumcu]

22.IMARGUMENT
Returns the argument (theta), an angle expressed in radians, such that:



Syntax

IMARGUMENT(inumber)

Inumber is a complex number for which you want the argument .

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
IMARGUMENT is calculated as follows:



z = x + yi
Example


=IMARGUMENT("3+4i") Theta argument of 3+4i, in radians (0.927295)

[birol_mumcu]

23.IMCONJUGATE
Returns the complex conjugate of a complex number in x + yi or x + yj text format.

Syntax

IMCONJUGATE(inumber)

Inumber is a complex number for which you want the conjugate.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The conjugate of a complex number is:

Example


=IMCONJUGATE("3+4i") Complex conjugate of 3+4i (3 - 4i)

[birol_mumcu]

24.IMCOS
Returns the cosine of a complex number in x + yi or x + yj text format.

Syntax

IMCOS(inumber)

Inumber is a complex number for which you want the cosine.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
If inumber is a logical value, IMCOS returns the #VALUE! error value.
The cosine of a complex number is:

Example

=IMCOS("1+i") Cosine of 1+i (0.83373 - 0.988898i)

[birol_mumcu]

25.IMDIV
Returns the quotient of two complex numbers in x + yi or x + yj text format.

Syntax

IMDIV(inumber1,inumber2)

Inumber1 is the complex numerator or dividend.

Inumber2 is the complex denominator or divisor.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The quotient of two complex numbers is:

Example


=IMDIV("-238+240i","10+24i") Quotient of the two complex numbers in the formula (5 + 12i)

[birol_mumcu]

26.IMEXP
Returns the exponential of a complex number in x + yi or x + yj text format.

Syntax

IMEXP(inumber)

Inumber is a complex number for which you want the exponential.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The exponential of a complex number is:

Example


=IMEXP("1+i") Exponential of the complex number 1+i (1.468694 + 2.287355i)

[birol_mumcu]

27.IMLN
Returns the natural logarithm of a complex number in x + yi or x + yj text format.

Syntax

IMLN(inumber)

Inumber is a complex number for which you want the natural logarithm.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The natural logarithm of a complex number is:

Example


=IMLN("3+4i") Natural logarithm of 3+4i (1.609438 + 0.927295i)

[birol_mumcu]

28.IMLOG10
Returns the common logarithm (base 10) of a complex number in x + yi or x + yj text format.

Syntax

IMLOG10(inumber)

Inumber is a complex number for which you want the common logarithm.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The common logarithm of a complex number can be calculated from the natural logarithm as follows:

Example


=IMLOG10("3+4i") Logarithm (base 10) of 3+4i (0.69897 + 0.402719i)

[birol_mumcu]

29.IMLOG2
Returns the base-2 logarithm of a complex number in x + yi or x + yj text format.

Syntax

IMLOG2(inumber)

Inumber is a complex number for which you want the base-2 logarithm.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The base-2 logarithm of a complex number can be calculated from the natural logarithm as follows:

Example

=IMLOG2("3+4i") Base-2 logarithm of 3+4i (2.321928 + 1.337804i)

[birol_mumcu]

30.IMPOWER
Returns a complex number in x + yi or x + yj text format raised to a power.

Syntax

IMPOWER(inumber,number)

Inumber is a complex number you want to raise to a power.

Number is the power to which you want to raise the complex number.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
If number is nonnumeric, IMPOWER returns the #VALUE! error value.
Number can be an integer, fractional, or negative.
A complex number raised to a power is calculated as follows:


where:



and:



and:


Example


=IMPOWER("2+3i", 3) 2+3i raised to the power of 3 (-46 + 9i)

[birol_mumcu]

31.IMPRODUCT
Returns the product of 1 to 255 complex numbers in x + yi or x + yj text format.

Syntax

IMPRODUCT(inumber1,inumber2,...)

Inumber1, inumber2,… are 1 to 255 complex numbers to multiply.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The product of two complex numbers is:

Example


=IMPRODUCT("3+4i","5-3i") Product of the two complex numbers (27 + 11i)
=IMPRODUCT("1+2i",30) Product of a complex number and 30 (30 + 60i)

[birol_mumcu]

32.IMREAL
Returns the real coefficient of a complex number in x + yi or x + yj text format.

Syntax

IMREAL(inumber)

Inumber is a complex number for which you want the real coefficient.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
Example


=IMREAL("6-9i") Real coefficient of 6-9i (6)

[birol_mumcu]

33.IMSIN
Returns the sine of a complex number in x + yi or x + yj text format.

Syntax

IMSIN(inumber)

Inumber is a complex number for which you want the sine.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The sine of a complex number is:

Example


=IMSIN("3+4i") Sine of 3+4i (3.853738 - 27.016813i)

[birol_mumcu]

34.IMSQRT
Returns the square root of a complex number in x + yi or x + yj text format.

Syntax

IMSQRT(inumber)

Inumber is a complex number for which you want the square root.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The square root of a complex number is:


Example


=IMSQRT("1+i") Square root of 1+i (1.098684 + 0.45509i)

[birol_mumcu]

35.IMSUB
Returns the difference of two complex numbers in x + yi or x + yj text format.

Syntax

IMSUB(inumber1,inumber2)

Inumber1 is the complex number from which to subtract inumber2.

Inumber2 is the complex number to subtract from inumber1.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The difference of two complex numbers is:

Example


=IMSUB("13+4i","5+3i") Difference between the two complex numbers in the formula (8 + i)

[birol_mumcu]

36.IMSUM
Returns the sum of two or more complex numbers in x + yi or x + yj text format.

Syntax

IMSUM(inumber1,inumber2,...)

Inumber1,inumber2,... are 1 to 255 complex numbers to add.

Remarks

Use COMPLEX to convert real and imaginary coefficients into a complex number.
The sum of two complex numbers is:

Example


=IMSUM("3+4i","5-3i") Sum of the two complex numbers in the formula (8+i)

[birol_mumcu]

37.OCT2BIN
Converts an octal number to binary.

Syntax

OCT2BIN(number,places)

Number is the octal number you want to convert. Number may not contain more than 10 characters. The most significant bit of number is the sign bit. The remaining 29 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Places is the number of characters to use. If places is omitted, OCT2BIN uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number is negative, OCT2BIN ignores places and returns a 10-character binary number.
If number is negative, it cannot be less than 7777777000, and if number is positive, it cannot be greater than 777.
If number is not a valid octal number, OCT2BIN returns the #NUM! error value.
If OCT2BIN requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, OCT2BIN returns the #VALUE! error value.
If places is negative, OCT2BIN returns the #NUM! error value.
Example


=OCT2BIN(3, 3) Converts octal 3 to binary with 3 characters (011)
=OCT2BIN(7777777000) Converts octal 7777777000 to binary (1000000000)

[birol_mumcu]

38.OCT2DEC
Converts an octal number to decimal.

Syntax

OCT2DEC(number)

Number is the octal number you want to convert. Number may not contain more than 10 octal characters (30 bits). The most significant bit of number is the sign bit. The remaining 29 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Remark

If number is not a valid octal number, OCT2DEC returns the #NUM! error value.

Example


=OCT2DEC(54) Converts octal 54 to decimal (44)
=OCT2DEC(7777777533) Converts octal 7777777533 to decimal (-165)

[birol_mumcu]

39.OCT2HEX
Converts an octal number to hexadecimal.

Syntax

OCT2HEX(number,places)

Number is the octal number you want to convert. Number may not contain more than 10 octal characters (30 bits). The most significant bit of number is the sign bit. The remaining 29 bits are magnitude bits. Negative numbers are represented using two's-complement notation.

Places is the number of characters to use. If places is omitted, OCT2HEX uses the minimum number of characters necessary. Places is useful for padding the return value with leading 0s (zeros).

Remarks

If number is negative, OCT2HEX ignores places and returns a 10-character hexadecimal number.
If number is not a valid octal number, OCT2HEX returns the #NUM! error value.
If OCT2HEX requires more than places characters, it returns the #NUM! error value.
If places is not an integer, it is truncated.
If places is nonnumeric, OCT2HEX returns the #VALUE! error value.
If places is negative, OCT2HEX returns the #NUM! error value.
Example


=OCT2HEX(100, 4) Converts octal number 100 to hexadecimal with 4 characters (0040)
=OCT2HEX(7777777533) Converts octal number 7777777533 to hexadecimal (FFFFFFFF5B)

[birol_mumcu]

these are new formulas in excel 2007. (all of the pages are taken to excel2007 help menu)
i didnt do any experiment for the formulas. be couse i dont use these.
if i find new news and things, i will attach this headline for you.

[birol_mumcu]

hi friends;
there is an explanation for excel2007 in the lower line.
http://office.microsoft.com/en-us/excel/HA100738731033.aspx
may it be easy