Free Microsoft Excel 2013 Quick Reference

Hello,

I have been working on a dynamic commercial real estate leasing model formula where I created a table where I can select a term (say 5 years from 1/1/12-12/31/16), add a rental rate ($20/sf) and a free rent number (say 3 months), as well as year over year escalations. As of now I have the model flows flawlessly in creating the time period to constrict the term as well as strings out the rental rate over the term and subtracts the free rent.

Where I am running into trouble is with the escalation formula. I have created the below formula which triggers a 3% escalation every year after the initial yearBasically, the only problem is that it only allows me to go to year 6 (I need it to exted to 10)DCF Model__Fully Functional Test.xls. Every past that gives me the following box in the title of this post: "Formula Uses More Levels Of Nesting Than Are Allowed In Current File Format". Below is my formula which starts in the "Rent" tab at E8 and attached is the model:

=IF(AND(Q7>=Inputs!$D$6,Q7<Inputs!$D$12),0,IF(AND(Q7>=Inputs!$D$12,Q7<(Inputs!$D$12+(Inputs!$D$13*1) )),1,IF(AND(Q7>=(Inputs!$D$12+(Inputs!$D$13*1)),Q7<(Inputs!$D$12+(Inputs!$D$13*2))),2,IF(AND(Q7>=(In puts!$D$12+(Inputs!$D$13*2)),Q7<(Inputs!$D$12+(Inputs!$D$13*3))),3,IF(AND(Inputs!$D$12+(Inputs!$D$13 *3),Q7<(Inputs!$D$12+(Inputs!$D$13*4))),4,IF(AND(Q7>=(Inputs!$D$12+(Inputs!$D$13*4)),Q7<(Inputs!$D$1 2+(Inputs!$D$13*5))),5,IF(AND(Q7>=(Inputs!$D$12+(Inputs!$D$13*5)),Q7<(Inputs!$D$12+(Inputs!$D$13*6)) ),6,IF(AND(Q7>=(Inputs!$D$12+(Inputs!$D$13*5)),Q7<(Inputs!$D$12+(Inputs!$D$13*6))),7,0)))))))

I have been working on a dynamic commercial real estate leasing model formula where I created a table where I can select a term (say 5 years from 1/1/12-12/31/16), add a rental rate ($20/sf) and a free rent number (say 3 months), as well as year over year escalations. As of now I have the model flows flawlessly in creating the time period to constrict the term as well as strings out the rental rate over the term and subtracts the free rent.

Where I am running into trouble is with the escalation formula. I have created the below formula which triggers a 3% escalation every year after the initial yearBasically, the only problem is that it only allows me to go to year 6 (I need it to exted to 10)DCF Model__Fully Functional Test.xls. Every past that gives me the following box in the title of this post: "Formula Uses More Levels Of Nesting Than Are Allowed In Current File Format". Below is my formula which starts in the "Rent" tab at E8 and attached is the model:

=IF(AND(Q7>=Inputs!$D$6,Q7<Inputs!$D$12),0,IF(AND(Q7>=Inputs!$D$12,Q7<(Inputs!$D$12+(Inputs!$D$13*1) )),1,IF(AND(Q7>=(Inputs!$D$12+(Inputs!$D$13*1)),Q7<(Inputs!$D$12+(Inputs!$D$13*2))),2,IF(AND(Q7>=(In puts!$D$12+(Inputs!$D$13*2)),Q7<(Inputs!$D$12+(Inputs!$D$13*3))),3,IF(AND(Inputs!$D$12+(Inputs!$D$13 *3),Q7<(Inputs!$D$12+(Inputs!$D$13*4))),4,IF(AND(Q7>=(Inputs!$D$12+(Inputs!$D$13*4)),Q7<(Inputs!$D$1 2+(Inputs!$D$13*5))),5,IF(AND(Q7>=(Inputs!$D$12+(Inputs!$D$13*5)),Q7<(Inputs!$D$12+(Inputs!$D$13*6)) ),6,IF(AND(Q7>=(Inputs!$D$12+(Inputs!$D$13*5)),Q7<(Inputs!$D$12+(Inputs!$D$13*6))),7,0)))))))

- Formula can't be entered becauses it uses more levels of nesting than allowed...
- Levels of nesting help
- Maximum levels of nesting if
- Levels of Nesting 2003 vs 2007
- Nesting Limit In 2007
- Nesting limitation using Substitute function
- Nesting Formula Issue???
- Error message in a simple nested formula
- Nested level limit exceeded with IF statements
- Nesting issues
- "too much nesting"
- Logical formula for multiple input
- Help using "if" "or" & "and" functions together
- Too many IFs-when trying to apply my IF function ?
- Too much nesting with if/then - need another solution
- If statement help-multiple statements
- Levels of nesting
- The specified Formula cannot be entered.
- Whats New in Excel 2007
- Nested IF Statements shortcut
- Too many levels of nesting
- Significant Loss of Functionality Error
- Need Help -iIf formula exceeded nesting limit & I think too many criteria for Vlookup
- How many levels of nesting .

But I’m getting this message:

“The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format”

Does anyone know how to make it work??

Thank you so much,

Cesar

Would appreciate some help on this...

i have created a spreadsheet to determine how many miles i drive on a monthly basis and i wanted to create a formula to do this for me.

For example, when i first started i used the following information:

h-w (home to work) 4 Miles

h-gf (home to girlfriends) 8 Miles

h-f (home to football) 10 Miles

h-c (home to cinema) 17 Miles

...and i used the formula =IF(B11="h-w","4",IF(B11="h-gf","8",IF(B11="h-f","10",IF(B11="h-c","17"))))

meaning that if write "h-w" in B11 then C11 shows "4", if i put "h-gf" it would show "8", and so on...

This worked fine until i added more circumstances. Now i have 15, it comes up with an error message " The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format", i can only put in 7 before this error message comes up...

How can i do i increase the amount of levels of nesting?

Or is there a different formula that u can use?

Please help,

Thanks!

Jamie

I am receiving the below message while trying a formula.

"The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

Formula is:

=IF(OR(C9=C8,C9=C7),0,IF(B9=1,MAFRAQ!K$68,IF(B9=2,MAFRAQ!L$68,IF(B9=3,MAFRAQ!M$68, IF(B9=4,MAFRAQ!N$68, IF(B9=5,MAFRAQ!O$68, IF(B9=6,MAFRAQ!P$68, IF(B9=7,MAFRAQ!Q$68, IF(B9=8,MAFRAQ!R$68, IF(B9=9,MAFRAQ!S$68, IF(B9=10,MAFRAQ!T$68, IF(B9=11,MAFRAQ!U$68, IF(B9=12,MAFRAQ!V$68, IF(B9=13,MAFRAQ!W$68, IF(B9=14,MAFRAQ!X$68, IF(B9=15,MAFRAQ!Y$68, IF(B9=16,MAFRAQ!Z$68, IF(B9=17,MAFRAQ!AA$68, IF(B9=18,MAFRAQ!AB$68, IF(B9=19,MAFRAQ!AC$68, IF(B9=20,MAFRAQ!AD$68, IF(B9=21,MAFRAQ!AE$68, IF(B9=22,MAFRAQ!AF$68, IF(B9=23,MAFRAQ!AG$68, IF(B9=24,MAFRAQ!AH$68, IF(B9=25,MAFRAQ!AI$68, IF(B9=26,MAFRAQ!AJ$68, IF(B9=27,MAFRAQ!AK$68, IF(B9=28,MAFRAQ!AL$68, IF(B9=29,MAFRAQ!AM$68, IF(B9=30,MAFRAQ!AN$68, IF(B9=31,MAFRAQ!AO$68,0))))))))))))))))))))))))))))))))

Please help.

Regards,

I created a workbook with the following formula in:

=IF(OR(AND(A39=2,A19=5),AND(A39=1,A19=5)),0,IF(AND(A39=1,A19=1),'Reseller Pricing'!$H$76,IF(AND(A39=1,A19=2),'Reseller Pricing'!$H$98,IF(AND(A39=1,A19=3),'Reseller Pricing'!$H$120,IF(AND(A39=1,A19=4),'Reseller Pricing'!$H$121,IF(AND(A39=2,A19=1),'Reseller Pricing'!$L$76,IF(AND(A39=2,A19=2),'Reseller Pricing'!$L$98,IF(AND(A39=2,A19=3),'Reseller Pricing'!$L$120,IF(AND(A39=2,A19=4),'Reseller Pricing'!$L$121,"End")))))))))

And it works 100% in 2007, however if someone opens it in 2003 or I try to save in 2003 format, I get the following message "Some formulas have more levels of nesting than are supported by selected file format. Formulas with more than seven levels of nesting will not be saved and converted to #Value! errors"

So the question is, anybody got any ideas how I can get around this issue, as not all my clients use 2007 (unfortunately) formula is in E19... and I have attached a sample file to assist... Rgds,Simon

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE

(SUBSTITUTE(SUBSTITUTE(A1,"0","S"),"1","M"),"2","A"),"3","R"),"4","K"),"5","E"),"6","T"),"7","I"),"8","N"),"9","G")

I am trying to use formula to convert number to text.It works on my excel 2010 but does not work on excel 2007.it gives below error.

Any idea how i can over come on this error?

The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format

=IF(S4>0.15,CONCATENATE(B4&" "&D4&"|"&"Rate of Return ="&T4&"/ "&"MLS ID= "&A4&"/ Row "&ROW()&"/ "&$G4&"/"&H4&" Assumed Cashflow of $"&R4&"/month|blue|15%+ ROR"),IF(S4>0.10,CONCATENATE(B4&" "&D4&"|"&"Rate of Return ="&T4&"/ "&"MLS ID= "&A4&"/ Row "&ROW()&"/ "&$G4&"/"&H4&" Assumed Cashflow of $"&R4&"/month|smallgreen|10%+ ROR"),IF(S4>0.05,CONCATENATE(B4&" "&D4&"|"&"Rate of Return ="&T4&"/ "&"MLS ID= "&A4&"/ Row "&ROW()&"/ "&$G4&"/"&H4&" Assumed Cashflow of $"&R4&"/month|smallgreen|"),IF(S4>0,CONCATENATE(B4&" "&D4&"|"&"Rate of Return ="&T4&"/ "&"MLS ID= "&A4&"/ Row "&ROW()&"/ "&$G4&"/"&H4&" Assumed Cashflow of $"&R4&"/month|smallgreen|0%+ ROR"),IF(S4<0,CONCATENATE(B4&" "&D4&"|"&"Rate of Return ="&T4&"/ "&"MLS ID= "&A4&"/ Row "&ROW()&"/ "&G4&"/"&H4&" Assumed Cashflow of $"&R4&"/month|smallred| Negative ROR <5%", IF(S4<0,CONCATENATE(B4&" "&D4&"|"&"Rate of Return ="&T4&"/ "&"MLS ID= "&A4&"/ Row "&ROW()&"/ "&G4&"/"&H4&" Assumed Cashflow of $"&R4&"/month|smallred| Negative ROR >5%"))))))))

Please Help

I'm trying to use this formula, and the very last part of the formula doesn't work with the "IF" and "AND" condition. I get the following message error:

"The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format".

This is the formula:

If you remove the last part of the formula, that is,

IF(AND(E21=0.75,E22>3.75),-1.3448*(E16-4.125)+8.875

and 1 parentesis the formula works....

Please, if somebody knows how to fix this, it would be greatly appreciated.

Thanks,

Cesar

This formula doesn't work:

...because of error: "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

I need to continue the above logic all the way down to "ZZ"

Any ideas?

Thanks!

UPDATE:

Sample data excel file uploaded a couple posts below this one: http://www.excelforum.com/2633576-post4.html

as marco enabled workbook.

The specified formula cannot be entered because it uses more levels of

nesting than are allowed in the current file format.

I'm using the following formula to look up the amount of an item used by lot

numder across 6 work sheets. I am simply adding the next formula in the false

part of the statement.

=IF(AND(C43='[Stuffing Time Sheet A.xls]sheet5'!$J$5,A43='[Stuffing Time

Sheet A.xls]sheet5'!$B$4),IF(ISERROR(VLOOKUP(R52,'[Stuffing Time Sheet

A.xls]sheet5'!$I$7:$J$17,2,FALSE))," ",(VLOOKUP(R52,'[Stuffing Time Sheet

A.xls]sheet5'!$I$7:$J$17,2,FALSE)))," ")

I need this go across sheet1 thru sheet 6, any ideas? Also posting this in

program forum also.

thank you in advance!!!

Is there any way around this? I need the entire formula I'm entering into the cell.

Thanks

trying to formulate the excel to return a percentage rebate/charge from the table which contain of 2 argument(purchase year & how many installment) but got error, i was trying to apply nesting if to do it but error come "the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format.

this is my formula, however error come when i insert another nesting if, i believe i do the wrong way, please correct me.

=IF(AND(C11<=2006,C13<=3),"65%",IF(AND(C11<=2006,C13<=6),"60%",IF(AND(C11<=2006,C13<=12),"55%",IF(AN D(C11<=2006,C13<=18),"50%",IF(AND(C11<=2006,C13<=24),"35%",IF(AND(C11=2007,C13<=3),"55%",IF(AND(C11< =2007,C13<=6),"50%")))))))

Appreciate your assistance.

example 2.xls

Best Regards

Eric Tham

I have an excel sheet with several drop down lists and I need a formula that will bring up a price according to what is selected. Cell B27 contains the product (t-moulding, reducer or stair nosing), cell D27 contains the size (8, 12, 15 or 18mm). Here are the prices:

8mm 12mm 15mm 18mm

t-moulding 2.95 2.95 3.95 3.95

reducer 2.95 2.95 3.95 3.95

stair nosing 3.95 3.95 4.95 4.95

From that, I need a formula that will automatically bring up the correct price in cell G27. There are so many IF, AND, OR situations that I just cannot figure it out. And when I think I finally have it, I get the message "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format" Any help would be GREATLY appreciated.

Thank you!

Monia

"the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

here is the formula im trying to enter:

=IF(AND(O174>=2000,O174<2050),VLOOKUP(TRUE,Sheet6!O73:AA783,10,FALSE),IF(AND(O174>=2050,O174<2100),V LOOKUP(TRUE,Sheet6!AC74:AO784,10,FALSE),IF(AND(O174>=2100,O174<2150),VLOOKUP(TRUE,Sheet6!AQ56:BC766, 10,FALSE),IF(AND(O174>=2150,O174<2200),VLOOKUP(TRUE,Sheet6!BE74:BQ784,10,FALSE),IF(AND(O174>=2200,O1 74<2250),VLOOKUP(TRUE,Sheet6!BS74:CE784,10,FALSE),IF(AND(O174>=2250,O174<2300),VLOOKUP(TRUE,Sheet6!C G74:CS784,10,FALSE),IF(AND(O174>=2350,O174<2400),VLOOKUP(TRUE,Sheet6!DI74:DU784,10,FALSE),IF(AND(O17 4>=2400,O174<2450),VLOOKUP(TRUE,Sheet6!DW74:EI784,10,FALSE),IF(AND(O174>=2450,O174<2500),VLOOKUP(TRU E,Sheet6!EK74:EW784,10,FALSE),IF(AND(O174>=2500,O174<4000),VLOOKUP(TRUE,Sheet6!EY74:FK784,10,FALSE), 0))))))))))

i followed a help file from a website for IF and and functions to do 2 variables, why does it not work with the 9 variables i have above?

Trying to have a workbook for 15 employees summarize on a main worksheet by showing if any of the employees are off a particular day. For example if Joe is off (signified by a V in the proper cell on his sheet), the main summary sheet will have a V so we know one of the 15 people have off.

I thought a multi nested IF/THEN would work, but I got this :

"The specified forumula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

Doing some searches has me believe I am hitting a character limitation and not a nesting limitation.

Here is what I was trying to do :

=IF(Sheet15!F6="V","V",IF(Sheet14!F6="V","V",IF(Sheet13!F6="V","V",IF(Sheet12!F6="V","V",IF(Sheet11! F6="V","V",IF(Sheet10!F6="V","V",IF(Sheet9!F6="V","V",IF(Sheet8!F6="V","V",IF(Sheet7!F6="V","V",IF(S heet6!F6="V","V",IF(Sheet5!F6="V","V",IF(Sheet4!F6="V","V",IF(Sheet3!F6="V","V",IF(Sheet2!F6="V","V" ,IF(Sheet1!F6="V","V","")))))))))))))))

I've looked at lookup, sum, isblank and am not really sure what can do what I need without hitting some type of limitation.

I'm working in 2007 backwards compatible for 2003 - but even in 2007 file format I got the same error.

Any ideas?

Thanks in advance....

I was wondering if I could get some help with the following code. I have been trying to add another line of logic but excel gives me an error saying "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

= IF(A2="gv", F2, IF(OR(B2="Missing",B2="Maintain"),B2, IF(AND(A2="gv", B2="return",COUNTIF(C2:G2,"Save")>0),"save", IF(AND(B2="return",COUNTIF(C2:E2,"save")+COUNTIF(G2,"save")>0),"save", IF(AND(A2<>"gv",COUNTIF(B2:E2,"destroy")+COUNTIF(G2,"destroy")=5),"destroy", IF(AND( COUNTIF(B2:E2,"")+COUNTIF(G2,"") > 0),"Pending", "something not considered" ))))))The above is the current code and I wanted to change the logic to add this extra line at the very beginning of the statement shown below:

=IF(and(B2="Missing",a2="gv"),B2, IF(A2="gv", F2, IF(OR(B2="Missing",B2="Maintain"),B2, IF(AND(A2="gv", B2="return",COUNTIF(C2:G2,"Save")>0),"save", IF(AND(B2="return",COUNTIF(C2:E2,"save")+COUNTIF(G2,"save")>0),"save", IF(AND(A2<>"gv",COUNTIF(B2:E2,"destroy")+COUNTIF(G2,"destroy")=5),"destroy", IF(AND( COUNTIF(B2:E2,"")+COUNTIF(G2,"") > 0),"Pending", "something not considered" )))))))

I have also attached a pdf portfolio that contains the excel 2007 file that will better explain this logic. It also uses conditional formatting in combination with this statement.

Thanks

=IF(AO39>5500000, 350,IF(AO39>4800000, 300,IF(AO39>4000000, 250,IF(AO39>3200000, 200,IF(AO39>2400000, 160,IF(AO39>2000000, 130,IF(AO39>1500000, 100,IF(AO39>1250000, 80,IF(AO39>720000, 40,IF(AO39>400000, 20,IF(AO39>170000, 10,IF(AO39>60000, 2,))))))))))))

but i get this message:

the specified formula cannot be entered because it uses more levels of nesting than allowed in the current file format.

Can some one help please

The error is: The specified Formula cannot be entered because it uses more levels of nesting then are allowed in the current fil format.

The Formula:

=IF('Competency Level'!$C$2=Level!C2;Level!C4;IF('Competency Level'!$C$2=Level!D2;Level!D4;IF('Competency Level'!$C$2=Level!E2;Level!E4;IF('Competency Level'!$C$2=Level!F2;Level!F4;IF('Competency Level'!$C$2=Level!G2;Level!G4;IF('Competency Level'!$C$2=Level!H2;Level!H4;IF('Competency Level'!$C$2=Level!I2;Level!I4;IF('Competency Level'!$C$2=Level!J2;Level!J4;IF('Competency Level'!$C$2=Level!K2;Level!K4))))))))

-Smil

New User Interface

The new results-oriented user interface makes it easy for you to work in Microsoft Office Excel. Commands and features that were often buried in complex menus and toolbars are now easier to find on task-oriented tabs that contain logical groups of commands and features. Many dialog boxes are replaced with dropdown galleries that display the available options, and descriptive tooltips or sample previews are provided to help you choose the right option.

No matter what activity you are performing in the new user interface, whether its formatting or analyzing data, Excel presents the tools that are most useful to successfully complete that task.

Introducing the new interface

There is a new look for Office Excel 2007, a new user interface (UI) that replaces menus, toolbars, and most of the task panes from previous versions of Excel with a single mechanism that is simple and apparent. The new user interface is designed to help you be more productive in Excel, more easily find the right features for various tasks, discover new functionality, and be more efficient.

The New Interface commands hierarchy:

1. Menu Tabs.

2. Ribbons.

3. Groups within each tab break a task into subtasks.

4. Command buttons (icons) in each group carry out a command or display a menu of commands.

Ribbon user interface: The primary replacement for menus and toolbars in Office Excel 2007 is the Ribbon. Designed for easy browsing, the Ribbon consists of tabs that are organized around specific scenarios or objects. The controls on each tab are further organized into several groups. The Ribbon can host richer content than menus and toolbars can, including buttons, galleries, and dialog box content.

Tabs that appear only when you need them: In addition to the standard set of tabs that you see on the Ribbon whenever you start Office Excel 2007, there are two other kinds of tabs, which appear in the interface and are useful for the type of task you are currently performing.

Contextual tools: Contextual tools enable you to work with an object that you select on the page, such as a table, a picture, or a drawing. When you click the object, the pertinent set of contextual tabs appears in an accent color next to the standard tabs.

Program tabs: Program tabs replace the standard set of tabs when you switch to certain authoring modes or views, including Print Preview.

File Button : This button is located in the upper-left corner of the Excel window and opens the menu shown here:

Quick Access Toolbar: The Quick Access Toolbar is located by default at the top of the Excel window and provides quick access to tools that you use frequently. You can customize the Quick Access Toolbar by adding commands to it.

Adding Commands to Quick Access Toolbar: In the Customize Quick Access Toolbar box, select either For all documents (as a default) or a specific document.

Click the command that you want to add, and then click Add.

Dialog Box Launchers: Dialog Box Launchers are small icons that appear in some groups. Clicking a Dialog Box Launcher opens a related dialog box or a task pane, providing more options related to that group.

Use the Keyboard to Access any Commands in the Ribbon

To use keyboard shortcut: To open a menu tab, press the Alt tab, now press a letter(s) or a number or a combination of a letter & a number , see below:

Step 1: press the Alt key or F10.

Step 2:

Press H, and then a letter(s) or a number or a combination of both (a letter & a number).

Or

Use the Tab key to move between command buttons in the Ribbon.

Memory management, Workbook, Worksheet & Cells

Memory Management

Memory management has been increased from 1 GB of memory in Microsoft Office Excel 2003 to 2 GB in Office Excel 2007.

You will also experience faster calculations in large, formula-intensive worksheets because Office Excel 2007 supports dual-processors and multithreaded chipsets.

Numbers of Rows, Columns & Cells in a Worksheet

Excel 2007 sheet contains 1,048,576 rows by 16,384 columns, total of 17,180,033,024 cells compare to previous Excel versions which hold 65,536 rows by 256 columns, total of 16,777,216 cells.

New file formats

XML-based file format: In 2007 Microsoft Office system, Microsoft is introducing new files formats for Word, Excel, and PowerPoint, known as the Microsoft Office Open XML formats. These new file formats facilitate integration with external data sources, and also offer reduced file sizes and improved data recovery. In Excel 2007, the default format for an Excel workbook is the Office Excel 2007 XML-based file format (.xlsx). Other available XML-based formats are the Excel 2007 XML-based and macro-enabled file format (.xlsm), the Excel 2007 file format for an Excel template (.xltx), and the Excel 2007 macro-enabled file format for an Excel template (.xltm).

Themes, Colors & Formatting

Office themes

In Office Excel 2007, you can quickly format the data in your worksheet by applying a theme and by using a specific style. Themes can be shared across other 2007 Office release applications, such as Microsoft Office Word and Microsoft Office PowerPoint, while styles are designed to change the format of Excel-specific items, such as Excel tables, charts, PivotTables, shapes, or diagrams.

Number of Colors

Excel 2007 supports up to 16 million colors.

Rich conditional formatting

You can implement and manage multiple Conditional Formatting rules that apply rich visual formatting in the form of gradient colors, data bars, and icon sets to data that meets those rules. Conditional formats are also easy to apply in just a few clicks, you can see relationships in your data that you can use for your analysis purposes.

Formulas & Functions

Easy formula writing

Resizable formula bar: The formula bar automatically resizes to accommodate long, complex formulas, which prevents the formulas from covering other data in your worksheet. You can also write longer formulas with more levels of nesting than you could in earlier versions of Excel.

Function AutoComplete: With Function AutoComplete, you can quickly write the proper formula syntax. From easily detecting the functions that you want to use, to getting help completing the formula arguments, you will be able to get formulas right the first time and every time.

Easy access to Named ranges: By using Name manager, you can organize, update, and manage multiple Named ranges in a central location, which helps all users who need to work on your worksheet interpret its formulas and data.

New Functions

Very important and useful functions are added to Excel 2007. The functions are IFERROR, AVERAGEIF, AVERAGEIFS, SUMIFS and COUNTIFS. Read more and see example in Chapter 9, page 155.

New OLAP formulas and cube functions

When you work with multidimensional databases (such as SQL Server Analysis Services) in Excel 2007, you can use OLAP formulas to build complex, free form, OLAP data bound reports. New cube functions are used to extract OLAP data (sets and values) from Analysis Services and display it in a cell. OLAP formulas can be generated when you convert PivotTable formulas to cell formulas or when you use AutoComplete for cube function arguments when you type formulas.

Charts

A New look of charts

You can use new charting tools to easily create professional-looking charts that communicate information effectively. Based on the theme that is applied to your workbook, the new, up-to-date look for charts includes special effects, such as 3-D, transparency, and soft shadows.

The new user interface makes it easy to explore the available chart types so that you can create the right chart for your data. Numerous predefined chart styles and layouts are provided so that you can quickly apply a good-looking format and include the details that you want in your chart.

Visual chart element pickers: Beside the quick layouts and quick formats, you can now use the new user interface to quickly change any element of the chart to best present your data. In a few clicks, you can add or remove titles, legends, data labels, trendlines, and other chart elements.

A modern look with OfficeArt: Since charts in Excel 2007 are drawn with OfficeArt, almost everything you can do to an OfficeArt shape can also be done to a chart and its elements. For example, you can add a soft shadow or a bevel effect to make an element to stand out or use transparency to make elements visible that are partially hidden in a chart layout. You can also use realistic 3-D effects.

Clear lines and fonts: Lines in charts appear less jagged, and ClearType fonts are used for text to improve readability.

More colors than ever: You can easily choose from the predefined theme colors and vary their color intensity. For more control, you can also add your own colors by choosing from 16 million colors in the Colors dialog box.

Chart templates: Saving your favorite charts as a chart template is much easier in the new user interface.

Shared charting

Using Excel charts in other applications: In Excel 2007, charting is shared between Excel, Word, and PowerPoint. Rather than using the charting features that are provided by Microsoft Graph, Word and PowerPoint now incorporate the powerful charting features of Excel. Since an Excel worksheet is used as the chart data sheet for Word and PowerPoint charts, shared charting provides the rich functionality of Excel, including the use of formulas, filtering, sorting, and the ability to link a chart to external data sources, such as Microsoft SQL Server and Analysis Services (OLAP), for up-to-date information in your chart. The Excel worksheet that contains the data of your chart can be stored in your Word document or PowerPoint presentation, or in a separate file to reduce the size of your documents.

Copying charts to other applications: Charts can be easily copied and pasted between documents or from one application to another. When you copy a chart from Excel to Word or PowerPoint, it automatically changes to match the Word document or PowerPoint presentation, but you can also retain the Excel chart format. The Excel worksheet data can be embedded in the Word document or PowerPoint presentation, but you can also leave it in the Excel source file.

Animating charts in PowerPoint: In PowerPoint, you can more easily use animation to emphasize data in an Excel-based chart. You can animate the entire chart or the legend entry and axis labels. In a column chart, you can even animate individual columns to better illustrate a specific point. Animation features are easier to find and give you much more control. For example, you can make changes to individual animation steps, and use more animation effects.

Sorting, Filtering & Tables

Improved sorting and filtering

You can now sort data by color and by more than 3 (and up to 64) levels. You can also filter data by color or by dates, display more than 1000 items in the AutoFilter dropdown list, select multiple items to filter, and filter data in PivotTables.

Excel table enhancements

You can use the new user interface to quickly create, format, and expand an Excel table (known as an Excel list in Excel 2003) to organize the data on your worksheet so that its much easier to work with.

PivotTables

Easy-to-use PivotTables

By using the new PivotTable user interface, the information that you want to view about your data is just a few clicks away. You no longer have to drag data to drop zones that arent always an easy target. Instead, you can simply select the fields that you want to see in a new PivotTable field list.

After you create a PivotTable, you can take advantage of many other new or improved features to summarize, analyze, and format your PivotTable data.

Sharing & Connections

New ways to share your work

Using Excel Services to share your work: If you have access to Excel Services, you can use it to share your Office Excel 2007 worksheet data with other users, such as executives and other stakeholders in your organization. In Excel 2007, you can save a workbook to Excel Services and specify the worksheet data that you want other people to see. In a browser (browser: Software that interprets HTML files, formats them into Web pages, and displays them. A Web browser, such as Microsoft Internet Explorer, can follow hyperlinks, transfer files, and play sound or video files that are embedded in Web pages.), they can then use Microsoft Office Excel Web Access to view, analyze, print, and extract this worksheet data. They can also create a static snapshot of the data at regular intervals or on demand. Excel Web Access makes it easy to perform activities, such as scrolling, filtering, sorting, viewing charts, and using drill-down in PivotTables. You can also connect the Excel Web Access Web Part to other Web Parts to display data in alternative ways. And with the right permissions, Excel Web Access users can open a workbook in Excel 2007 so that they can use the full power of Excel to analyze and work with the data on their own computers if they have Excel installed.

Using this method to share your work ensures that other users have access to one version of the data in one location, which you can keep current with the latest details. If you need other users, such as team members, to supply you with comments and updated information, you may want to share a workbook the same way.

Quick connections to external data

You no longer need to know the server or database names of corporate data sources. Instead, you can use Quick Launch to select from a list of data sources that your administrator or workgroup expert has made available for you. A connection manager in Excel allows you to view all connections in a workbook and make it easier to reuse a connection or to substitute a connection with another user.

Printing

Better printing experience

Page Layout View: In addition to the Normal view and Page Break Preview view, Excel 2007 provides a Page Layout View. You can use this view to create a worksheet while keeping an eye on how it will look in printed format. In this view, you can work with page headers, footers, and margin settings right in the worksheet, and place objects, such as charts or shapes, exactly where you want them to be. You also have easy access to all page setup options on the Page Layout tab in the new user interface so that you can quickly specify options, such as page orientation. Its easy to see what will be printed on every page, which will help you avoid multiple printing attempts and truncated data in printouts.

Saving to PDF and XPS format: Like other 2007 Office release applications, Excel 2007 supports saving a workbook to a high-fidelity fixed file format, such as Portable Document Format (PDF) or XML Paper Specification (XPS) format, that encapsulates how it will look when it is printed. This allows you to share the content of your workbook in a format that is easy for other people to view online or print, without including the underlying formulas, external data queries, or comments.

Actually, this is what you did in earlier versions of Excel to collect the information you need before you save it to Excel Services.

Using Document Management Server: Excel Services can be integrated with Document Management Server to create a validation process around new Excel reports and workbook calculation workflow actions, such as a cell-based notification or a workflow process based on a complex Excel calculation. You can also use Document Management Server to schedule nightly recalculation of a complex workbook model.

I'm thinking if there is a shortcut for this formula.

I'm using compatibility mode (03-07).

=IF(A2="Philippines", INDEX(Sheet2!B:B,MATCH(Sheet1!F2,Sheet2!A:A,0)), IF(A2="Malaysia", INDEX(Sheet2!C:C,MATCH(Sheet1!F2,Sheet2!A:A,0)), IF(A2="Thailand", Index(Sheet2!D:D,match(Sheet1!F2,Sheet2!A:A,0)), IF(A2="Vietnam", Index(Sheet2!E:E,match(Sheet1!F2,Sheet2!A:A,0)), IF(A2="Japan", Index(Sheet2!F:F,match(Sheet1!F2,Sheet2!A:A,0)), IF(A2="Australia", Index(Sheet2!G:G,match(Sheet1!F2,Sheet2!A:A,0)), IF(A2="India", Index(Sheet2!H:H,match(Sheet1!F2,Sheet2!A:A,0)), IF(A2="China", Index(Sheet2!I:I,match(Sheet1!F2,Sheet2!A:A,0)), IF(A2="Taiwan", Index(Sheet2!J:J,match(Sheet1!F2,Sheet2!A:A,0)))))))))))

Thanks!!!

=IF(K4="","",IF(AND(Hta_1>=D4,Hba_1<=D4,Hta_1<>"",Hba_1<>""),1,IF(AND(Hta_2>D4,Hba_2<=D4,Hta_2<>"",H ba_2<>""),2,IF(AND(Hta_3>D4,Hba_3<=D4,Hta_3<>"",Hba_3<>""),3,IF(AND(Hta_4>D4,Hba_4<=D4,Hta_4<>"",Hba _4<>""),4,IF(AND(Hta_5>D4,Hba_5<=D4,Hta_5<>"",Hba_5<>""),5 ,IF(AND(Hta_6>D4,Hba_6<=D4,Hta_6<>"",Hba_6<>""),6,IF(AND(Hta_7>D4,Hba_7<=D4,Hta_7<>"",Hba_7<>""),7," No reinforcements"))))))

=IF(L4="","",IF(AND(L4=1,MAX(Tcha_1,Tpa_1,Dsra_1)<>0),MAX(Tcha_1,Tpa_1,Dsra_1),IF(AND(L4=2,MAX(Tcha_ 2,Tpa_2,Dsra_2)<>0),MAX(Tcha_2,Tpa_2,Dsra_2),IF(AND(L4=3,MAX(Tcha_3,Tpa_3,Dsra_3)<>0),MAX(Tcha_3,Tpa _3,Dsra_3),IF(AND(L4=4,MAX(Tcha_4,Tpa_4,Dsra_4)<>0),MAX(Tcha_4,Tpa_4,Dsra_4),IF(AND(L4=5,MAX(Tcha_4, Tpa_4,Dsra_4)<>0),MAX(Tcha_5,Tpa_5,Dsra_5), IF(AND(L4=6,MAX(Tcha_6,Tpa_6,Dsra_6)<>0),MAX(Tcha_6,Tpa_6,Dsra_6), IF(AND(L4=7,MAX(Tcha_7,Tpa_7,Dsra_7)<>0),MAX(Tcha_7,Tpa_7,Dsra_7),"-"))))))

Can anyone help with this and also explain how to get rid of the error?

Thanks.

When saving it gives me an error 'Significant Loss of Functionality'

Followed by : 'Some of the Formulas have more levels of nesting are supported by the selected file format.

It seems to be only one formula in the workbook and the formula is as follows:

=IF(H3=0,H2,IF(H2>0,H3,IF(H3>0,H4,IF(H4>0,H5,IF(H5>0,H6,IF(H6>0,H7,IF(H7>0,H8,IF(H8>0,H9,IF(H9>0,H10 ,IF(H10>0,H11,IF(H11>0,H12,IF(H12>0,H13))))))))))))

Each Cell from H2 to H13 will display the monthly account balance from that particular months spreadsheet.

The Formula (H14) is supposed to show the monthly total $ as the account balance varies from Jan-Dec. I only want the Current month to display when it varies from the previous months balance. (Progressive account total month by month) .

Each cell from H2-H13 displays blank when the Month's balance is 0.

If I save the workbook without changing, I get #VALUE Error in Cell H14.

Thanks in Advance,

Martin

From the data below I want to be able select various criteria between col A - col F to create different reports. For example, when PGR in col C =273 and col F = Yes this will be the report for client A. The report for client B may have different criteria e.g. PGR in col C = 234 and col E = RBAINS2. I have about 25 reports all with different criteria and want to display the Report Name in col G.

I have tried to use the following formula G2 = If(AND(C2=273,F2="YES"),"Client A",If(AND(C2=234,E2="RBAINS2"),"Client B","")) but I am restricted by the amount of nesting you are allowed in Excel. I'm not sure a Vlookup would work because I have so many different criterias for the report.

Open to any suggestions! Thanks :-)

Col A Col B Col C Col D Col E Col F Col G

1 Invoice Date Doc.no. PGR Vendor PO Creator On last report Report Name

2 12/10/2010 1030042293 273 112066 CPIERCE6 YES

3 03/24/2011 1030051202 234 148543 NMASON2 NO

4 06/16/2011 1030009862 O06 108977 TSANTOE YES

5 06/23/2011 1030011117 G06 120984 RWEGNER No

6 07/04/2011 1030011699 234 166290 RBAINS2 YES

can anyone help