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]

excel 2007

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]

excel2007

ı send the files
first paragraph of the answer

 

[birol_mumcu]

excel2007

the second file

 

[birol_mumcu]

excel2007

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]

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,caption)

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$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,property)

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,property)

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,caption)

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_order,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_expression2&#8230

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)