Excel 2010 Bible

A comprehensive reference to the newest version of the worldâ??s most popular spreadsheet application: Excel 2010

John Walkenbach's name is synonymous with excellence in computer books that decipher complex technical topics. Known as "Mr. Spreadsheet," Walkenbach shows you how to maximize the power of all the new features of Excel 2010.

An authoritative reference, this perennial bestseller proves itself indispensable no matter your level of skill, from Excel beginners and intermediate users to power users and potential power users everywhere. Fully updated for the new release, this latest edition provides comprehensive, soup-to-nuts coverage, delivering over 900 pages of Excel tips, tricks, and techniques you wonâ??t find anywhere else.

• Excel guru and bestselling author John Walkenbach ("Mr. Spreadsheet") guides you through every aspect of Excel
• Delivers essential coverage of all the newest features of Excel 2010
• Presents material in a clear, concise, logical format that is ideal for all levels of Excel experience
• Includes a CD that contains all the templates and worksheets used in the book plus John Walkenbach's award-winning Power Utility Pak

Excel 2010 Bible serves as an excellent resource on all things Excel!

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Price: $44.99

Click here to buy from Amazon

Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf)

• This book is a single reference thatâ??s indispensable for Excel beginners, intermediate users, power users, and would-be power users everywhere
• Fully updated for the new release, this latest edition provides comprehensive, soup-to-nuts coverage, delivering over 900 pages of Excel tips, tricks, and techniques readers wonâ??t find anywhere else
• John Walkenbach, aka "Mr. Spreadsheet," is one of the worldâ??s leading authorities on Excel
• Thoroughly updated to cover the revamped Excel interface, new file formats, enhanced interactivity with other Office applications, and upgraded collaboration features
• Includes a valuable CD-ROM with templates and worksheets from the book

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Price: $49.99

Click here to buy from Amazon

New Selectsoft Publishing 101 Greatest XP Apps & Utilities For Powerful Digital Photography

Now you can have the greatest Windows XP applications and utilities without spending hundreds of hours searching and downloading them. The Windows Users Group Network (WUGNET) has compiled these amazing shareware applications and utilities for you on one CD-ROM with an easy-to-browse launcher.

Price: $13.41

Click here to buy from Amazon

NeatReceipts Scanalizer Professional 2.5 Mobile Scanner and Software

Amazon.com Product Description Please note: A 3.0 software upgrade for the NeatReceipts Scanalizer will be released on October 15, 2007. You may purchase the software upgrade at the NeatReceipts website: Neatreceipts.com/Upgrade

NeatReceipts Version 2.5 Professional is the easiest way for individuals and small businesses to manage expenses, prepare for taxes, create expense reports, and scan business cards. But it not all just for business--you can also use Neat Receipts for scrapbooking, archiving family recipes, and other family activities. NeatReceipts Version 2.5 Professional edition also includes a new Document Organizer feature in addition to Receipts, Business Card, and Tax Organizers features.

Use NeatReceipts 2.5 to manage expenses, prepare for taxes, create expense reports, and scan business cards. View example.

Organize expenses by IRS form and category. View example.

Sync contact information into Outlook, Plaxo or vCard. View example.

Scan receipts and save in digital spreadsheet format. View example.

Scan documents, recipes, and more and then convert them to PDF. View example.

How NeatRecipts can Make Your Life Easier

Make permanent digital copies of all your financial papers
NeatReceipts 2.5 comes with a high resolution portable scanner. All you have to do is scan your receipts, invoices, and other financial documents through this scanner, and our software will save permanent copies that you can depend on for years to come.

Eliminate financial data entry, simplifies expense tracking
Using patent-pending NeatScan technology, NeatReceipts 2.5 extracts all important information from each receipt including date, amount, vendor and payment type. It then organizes all this expense information into a searchable expense database.

Speed up tax preparation
NeatReceipts allows you to leave digital comments on receipts and mark them for tax purposes. During tax season, retrieve all the marked receipts with ease and review those comments for easy preparation. NeatReceipts stores each receipt in a secure digital format that is accepted by the IRS.

Integrate with QuickBooks, Quicken, Money, Act!, Peachtree
Export to QuickBooks, MS Money, Quicken and most other financial software. By automating data entry and making it easy to export to other software, NeatReceipts makes sure you never have to type information into your financial software again!

Scan business cards
NeatReceipts captures key contact information from business cards and syncs with your Outlook address book. You can also create V-cards to upload to your PDA.

Automatically create expense reports
Automatically creates expense reports from scanned receipts and attaches images of the original receipts for simple verification. Users can email these reports from within the software or export them into PDF, DOC, XLS or HTML formats. If you are looking for expense reporting software with in-built approval, analysis and administration capabilities, please consider NeatReceipts Enterprise.

How NeatReciepts Works

NeatScan Technology Using advanced Optical Character Recognition, NeatScan technology (patent pending) extracts key information from your receipts, such as date, amount, payment method, expense category and vendor. If you scan in business cards, NeatScan automatically pulls out information like name, company, address, phone numbers, email address etc.

NeatScan actually learns to associate an expense category (such as meals or lodging) to a particular vendor (brands such as Starbucks or Hilton) so you don't have to type in the information each time. This ensures that your financial records are filed consistently and with minimal effort.

Each user of NeatReceipts Professional 2.5 receives the NeatReceipts scanner--a lightweight, portable, durable color scanner. Our software was designed with this scanner in mind and offers the following unmatched functionality:

• Auto-sizes any scanned receipt or document.
• Auto-crops and auto-rotates any scanned receipt or document.
• Scan widthwise or lengthwise -- the image will appear upright in image viewer.
• Powered by USB port, so no plug needed (it's truly portable!).
• Scans quickly and easily without opening and closing scanner lid (flatbed).
• Small, portable and lightweight. Perfect for the office, the home and the road!

What New with NeatRecipts 2.5

NeatReceipts Professional 2.5 offer many new features like recognition of credit cards by last 4 digits, simplified and enhanced Advanced Fast Find for your folder and receipts, and added printing options that let you print one to four images per page and choice of paper size.

Just like its previous version, NeatReceipts Professional 2.5 includes a business card reader at no additional charge. Simply scan your business cards and sync your contact information automatically with your Outlook address book, or create a V-Card for your palm pilot. Version 2.5 has the added convenience of Plaxo integration, so you can just go online, click "sync with Plaxo" and have your contact information updated.

New in business card features in version 2.5 include an improved interface and parsing, both black & white and color scanning, one-click e-mail to and browse-to options, and completed action item check box column.

Also, for the first time, NeatReceipts Scanalizer Professional Version 2.5 also includes a document organizer so you can keep track of all types of documents and access them up as needed. Document Organizer features include:

• Black & White and Color Scanning
• Document copier
• Copy Document command
• Ability to combine documents together
• Ability to separate multi-page documents

What's in the Box
Scanner, carrying case, stand

Price: $209.95

Click here to buy from Amazon

Learn Excel 2007 Essential Skills with the Smart Method: Courseware Tutorial for Self-Instruction to Beginner and Intermediate Level

This book is very, very different to any other computer book you have ever read. The Smart Method provides an entirely new and better way to learn Excel. Here are just five of the many ways in which this book is unique:

• The book will equip you with excellent Excel skills, good enough to impress any employer, but it doesn’t confuse by attempting to teach skills that are not common in the workplace. Only users who have advanced requirements need progress to the Expert Skills book.
• This is the only book that is also available as a low-cost video (DVD). Each can be used as a stand-alone product, but together they provide an incredibly powerful learning experience.
• Instruction is logically structured into sessions and lessons. While this makes the book ideal for self-instruction, you can also use it to run your own, highly effective, adult training courses.
• Every lesson is presented on two facing sheets of A4 paper (the pages are much bigger than in any other Excel book).
• Each lesson has a sample file that models a real-world business problem. You’ll immediately appreciate the value and relevance of each skill as it is taught.

Both IT professionals and absolute beginners will love this book because it avoids needless technical jargon and concisely explains everything you need in a simple and no-nonsense way.

Price: $21.99

Click here to buy from Amazon

BRAND NEW Microsoft Excel 2010 1 PC Spreadsheet Complete Product DVD-ROM PC English

Manufactured to the Highest Quality Available. Marketing Information:Microsoft Excel 2010 makes it possible to analyze, manage, and share information in more ways than ever before, helping you make better, smarter decision. Whether you're producing financial reports or managing personal expenses, Excel 2010 gives you more efficiency and flexibility to accomplish your goal.Distribution Media/Method:DVD-ROM. Product Type:Software. Product Information. Software Main Type:Productivity Application. Software Sub Type:Spreadsheet. Software Name:Excel 2010. Features:Create data charts in a single cell. Zero in on the right data points quickly. Efficiently model and analyze almost any data. Access your spreadsheets from virtually anywhere. Connect, share, and accomplish more when working together. Add more sophistication to your data presentations. Take advantage of more interactive and dynamic PivotCharts. Do things easier and faster. Harness more power for building bigger, more complex spreadsheets. Publish and share through Excel Services. Language Support:English. Platform Support:PC. Operating System Support:Windows. License Information. License Type:Complete Product. License Quantity:1 PC.

Price: $251.30

Click here to buy from Amazon

Excel 2007 For Dummies

One look at Excel 2007, with its new Office Button, Quick Access toolbar, and Ribbon, and you realize youâ??re not in Kansas anymore. Well, have no fearâ?? Excel 2007 for Dummies is here!

If youâ??ve never worked with a computer spreadsheet, or if youâ??ve had some experience with earlier versions of Excel but need help transitioning, here youâ??ll find everything you need to create, edit, format, and print your own worksheets (without sacrificing your sanity!). Excel 2007 for Dummies covers all the fundamental techniques, concentrating on only the easiest, most user-friendly ways to get things done.

Youâ??ll discover how to:

• Rearrange, delete and insert new information
• Keep track of and organize data in a single worksheet
• Transfer data between the sheets of different workbooks
• Create a chart using the data in a worksheet
• Add hyperlinks and graphics to worksheets
• And more!

Plus, in keeping with Excel 2007â??s more graphical and colorful look, Excel 2007 for Dummies has taken on some color of its own, with full-color plates in the mid-section of the book illustrating exactly what youâ??ll see on your screen. Whether you read it from cover to cover or skip to the sections that answer your specific questions, the simple guidance in this book will have you excelling at home or in the office no time.

Price: $21.99

Click here to buy from Amazon

Office 2010 All-in-One For Dummies

The leading book on Microsoft Office, now fully updated for Office 2010

Microsoft Office, the world's leading productivity suite, has been updated with new tools. Veteran Office users as well as newcomers will need the comprehensive information in this bestselling All-in-One guide.

With a self-contained minibook devoted to each Office application plus minibooks on how Office works together and how you can expand its usefulness, Office 2010 All-in-One For Dummies gets you up to speed and answers the questions you'll have down the road.

• Microsoft Office is the office productivity suite used around the globe; nearly every business worker encounters it daily
• The 2010 revision will affect all applications in the suite
• Eight minibooks cover Word, Excel, PowerPoint, Outlook, Access, Publisher, common Office tools, and ways to expand Office productivity
• Also covers the new online versions of Word, Excel, and PowerPoint as well as changes to the interface and new tools and techniques

Office 2010 All-in-One For Dummies makes it easy to learn to use Office and gets you up and running on all the changes and enhancements in Office 2010.

Price: $34.99

Click here to buy from Amazon

The Neat Company NeatScan To Office Scans to Excel Outlook Word and PowerPoint (White)

SCANNER, NEAT SCAN TO OFFICE, USB 2

Price: $279.95

Click here to buy from Amazon

Vending Machine Company Business Plan - MS Word/Excel

The Vending Machine Company Business Plan is a comprehensive document that you can use for raising capital from a bank or an investor. This document has fully automated 3 year financials, complete industry research, and a fully automated table of contents. The template also features full documentation that will help you through the business planning process. This is a full and complete business plan with original research, financial models, and marketing/advertising plans that are specific for a Vending Machine Company. Since 2005, BizPlanDB and its parent company have helped raise more than $100,000,000 through its developed plans.

Price:

Click here to buy from Amazon

Wine Cellar 2.1 Template for Microsoft® Excel Lets You Easily Track Your Personal Wine Collection

Wine Cellar 2.1 Template for Microsoft® Excel lets wine enthusiasts easily track and manage personal wine collections. The template saves people the time of setting up their own system. Data entry is easy and wines can be sorted by varietal, pairing, when to drink, quantity, price and many other categories. The spreadsheet can be customized to match different collections. Please note that the template is delivered via email. Added information: Very important; the template is designed for personal use where someone doesn't want to have to stand in their cellar or thumb through a notebook trying to decide what to drink. If you are in a business where you have to manage wine inventory, this is not the right solution. You need inventory management software.

Price:

Click here to buy from Amazon

Excel 2007 Bible

• This book is a single reference thatâ??s indispensable for Excel beginners, intermediate users, power users, and would-be power users everywhere
• Fully updated for the new release, this latest edition provides comprehensive, soup-to-nuts coverage, delivering over 900 pages of Excel tips, tricks, and techniques readers wonâ??t find anywhere else
• John Walkenbach, aka "Mr. Spreadsheet," is one of the liworldâ??s leading authorities on Excel
• Thoroughly updated to cover the revamped Excel interface, new file formats, enhanced interactivity with other Office applications, and upgraded collaboration features
• Includes a valuable CD-ROM with templates and worksheets from the book

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Price: $39.99

Click here to buy from Amazon

Apple iWork '08 [OLD VERSION]

Item #: 35072F. Apple's amazing productivity suite for the Mac, iWork '08 includes three applications: Pages '08 for word processing with an incredible sense of style; Numbers '08 for powerful, compelling spreadsheets made easy; and Keynote '08 for cinema-quality presentations for everyone.

Product Description
IWork '08 - complete package
Category: Office applications
Subcategory: Office applications - presentation, office applications - word processor
License Type: Complete package
License Qty: 1 user
License Pricing: Standard
Platform: MacOS
Distribution Media: CD-ROM
Package Type: Retail
OS Required: Apple MacOS X 10.4.10
Customers also search for: Discount IWork '08 - Complete Package - 1 User - CD - Mac, Buy IWork '08 - Complete Package - 1 User - CD - Mac Wholesale IWork '08 - Complete Package - 1 User - CD - Mac, 0885909140244, MA790Z/A, Photo Editing Software

Price: $74.00

Click here to buy from Amazon

Use copy paste & to maintain references to tables [Quick Tip]

Posted on 23 May 2011 to learn Excel-9 comments

Using Excel 2007, Microsoft introduced powerful and useful feature called as tables. One advantage of tables that you can write formulas readable using structural references. This means, you can write easy to understand such formulas,

[Help SUMIF formula]

But there is a problem. When you type this formula and dragging formula cell remaining side fill cells, Excel changes the reference columns of the table and thus makes your formulas almost useless.

In addition, there is a simple solution for this problem.

Instead of dragging the cell to fill the formulas, you can use the copy paste to fill formulas &. In this case, Excel preserves all references table while changing cell references and accordingly. Watch this demo to understand:

Since the found feature tables in Excel 2007, I have been using them to save time and simplify working with data. Schedules of many useful features that make life simple for analysts and data junkies everywhere.

What about you? Are you using Excel tables? What are the tricks above your table? Participation please use the comments.

If you use Excel 2007 or above, I encourage you to learn Excel tables. They make your life simpler. Go to the bottom of articles for more information,

Spread some love
It makes you fabulous!

& Navigation functions

Tags: data Excel Excel Excel tables, see "Paste" and "quick tip", "References" and "screencasts" and "spreadsheets" and "structured references", "sumif)

RSS feed for comments on this post. TrackBack URI

View the original article here

Lost Excel functions

Error in deserializing body of reply message for operation 'Translate'. The maximum string content length quota (8192) has been exceeded while reading XML data. This quota may be increased by changing the MaxStringContentLength property on the XmlDictionaryReaderQuotas object used when creating the XML reader. Line 2, position 9250.
Error in deserializing body of reply message for operation 'Translate'. The maximum string content length quota (8192) has been exceeded while reading XML data. This quota may be increased by changing the MaxStringContentLength property on the XmlDictionaryReaderQuotas object used when creating the XML reader. Line 1, position 13902.
Posted on May 16th, 2011 in Excel Howtos , Huis , Posts by Hui - 26 comments

Following on from Chandoo’s MLookup function published on 1st April 2011, I thought it might be worth documenting a few undocumented, no-longer documented and rarely used Excel functions.

Although some of the functions below aren’t documented they still work as of Excel 2010.

Users should be cautious with their use going forward as Microsoft may withdraw them from future versions of Excel.

But if you see them appear in older Excel models at least you’ll be the full bottle.

This post will look at the following functions:

DatedifRoundupRoundDownEvaluateConvertRomanFactDoubleBahttext

Worked examples of all these functions are presented in the Example File which is compatible with all versions of Excel.

The DATEDIF function computes the difference between two dates in a variety of different intervals, such number of years, months, or days.

This function is available in all versions of Excel since at least version 5/95, but is documented in the help file only for Excel 2000.

By the way, do not confuse the DATEDIF worksheet function with the VBA DateDiff function.

=DATEDIF(Start Date, End Date, Interval)

Where:
Start Date must be less than the End Date.

Interval is the interval type to return.

Interval value must be one of the following:

Complete calendar months between the dates.Number of days between the dates.Complete calendar years between the dates.Complete calendar months between the dates as if they were of the same year.Complete calendar days between the dates as if they were of the same year.Days Excluding Years And MonthsComplete calendar days between the dates as if they were of the same month and same year.

If you are including the Interval string directly within the formula, you must enclose it in double quotes:

=DATEDIF(Date1,Date2,”m”)

Datedif has been used a number of times at Chandoo.org

http://chandoo.org/forums/topic/how-to-calculate-age-from-their-dob

http://chandoo.org/wp/2009/09/22/elapsed-time-excel/

http://chandoo.org/wp/2008/08/26/date-time-tips-ms-excel/

Disclaimer:

Although the Datedif function above isn’t documented it still works as of Excel 2010. Users should be cautious with their use going forward as Microsoft may withdraw support for them in future Excel versions.

The Roundup and Rounddown functions rounds a number up or down, away from zero and have pretty much been replaced by the Round function.

The Roundup function rounds a number up, away from zero.

=ROUNDUP(number, num_digits)

The Rounddown function rounds a number down, towards zero.

=ROUNDDOWN(number, num_digits)

Roundup() behaves similarly to the Round() function, except that it always rounds a number up based on the following rules:

If num_digits is greater than 0, then number is rounded up to the specified number of decimal places.If num_digits is 0 or omitted, then number is rounded up to the nearest integer.If num_digits is less than 0, then number is rounded up to the left of the decimal point.

ROUNDUP(4.1,0) equals 5

ROUNDUP(106.9,0) equals 107

ROUNDUP(3.14159, 3) equals 3.142

ROUNDUP(-3.14159, 1) equals -3.2

ROUNDUP(31415.926, -2) equals 31500
Rounddown() behaves similarly to the Round() function, except that it always rounds a number down based on the following rules:

If num_digits is greater than 0, then number is rounded down to the specified number of decimal places.If num_digits is 0 or omitted, then number is rounded down to the nearest integer.If num_digits is less than 0, then number is rounded down to the left of the decimal point.

ROUNDDOWN(4.1, 0) equals 4

ROUNDDOWN(106.9,0) equals 106

ROUNDDOWN(3.14159, 3) equals 3.141

ROUNDDOWN(-3.14159, 1) equals -3.1

ROUNDDOWN(31415.92654, -2) equals 31400

The Roundup and Rounddown functions have been used several times at Chandoo.org

Roundup

http://chandoo.org/wp/2010/04/29/quarterly-totals-from-monthly-data/

http://chandoo.org/wp/2010/04/30/quarterly-totals-multi-year-data/

Rounddown

http://chandoo.org/wp/2010/04/30/quarterly-totals-multi-year-data/

http://chandoo.org/wp/2009/07/06/excel-formulas-round-sort/

Evaluate is an Excel ver 4.0 macro function which is still supported and functional in Excel 2010.

The Evaluate function allows for the evaluation of a text equation as an algebraic equation.

The evaluate function cannot be used as a spreadsheet function but can be used in Named Ranges.

It is probably best described by example; Evaluate 1, from the Example File.

Say you have a polynomial equation in a cell as Text A1: ‘=X2 + 5*Y – Z

Setup 3 named ranges, X, Y , Z with values X=10, Y=5 and Z=3

You can use Evaluate in a a Named Range eg: Result =Evaluate(SheetName!$A$1)

And then on a worksheet =Result, which will return the answer 122 = 102 + 5*5 – 3

Evaluate can be used to allow graphing of equations without use of worksheet functions or even worksheet ranges, an example of each is shown in the examples file as Evaluate 2 and Evaluate 3 .

Evaluate 2: Uses a Range as the X Values and a Named Range using the Evaluate function as the calculated Y Values

Evaluate 3: Uses Named Ranges as the X Values and as the calculated Y Values based on an Evaluate function

Use of the Evaluate function on Chandoo.org:

Not Used

Converts a number from one measurement system to another.

For example, CONVERT can translate a table of distances in Kilometres to a table of distances in Miles.

Convert includes 49 units spread amongst the following 10 categories

=Convert(number, From Unit, To Unit)

A list of all the Conversion Units and Conversion Prefixes is included on the Conversion Factors tab of the Examples File.

Converts a 5 pound mass to kilograms (2.267)Converts 80 degrees Fahrenheit to Celsius (26.6)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).

A list of all the Conversion Units and Conversion Prefixes is included on the Conversion Factors tab of the Examples File.

http://chandoo.org/forums/topic/convert-function

The Roman function converts a number to Roman format.

=ROMAN(number, form)

=ROMAN(45 ) = XLV

Form is a number specifying the type of roman numeral you want. The roman numeral style ranges from Classic to Simplified, becoming more concise as the value of form increases.

More concise. See example below.More concise. See example below.More concise. See example below.Converts 499 to Roman (CDXCIX)Converts 499 to Roman (CDXCIX)Converts 499 to Roman (LDVLIV)

Nil

Factdouble returns the double factorial of a number and is expressed in mathematics as n!!

Double factorials are used in probability theory and other higher levels of mathematics and is really just a way to simplify an otherwise complex expression

If the number is Even Factdouble = n(n-2)(n-4)…(4)(2)

If the number is Odd Factdouble = n(n-2)(n-4)…(3)(1)

So it is simpler to write 10!! than 10x8x6x4x2

=Factdouble( number )

Factdouble of 8 = 8x6x4x2 = 384Factdouble of 9 = 9x7x5x3x1 = 945

Not used

Converts a number to Thai Text represention of the number

Use:

=Battext( Number)

=Bahttext(250) , Returns

Not used

An example file with worked examples from all the above functions is available from the following link; Example File

The file is compatible with all Excel versions.

What Functions Have You Stumbled Onto?

Let us know in the comments below:

Spread some love,
It makes you awesome!

Posts & Navigation

Tags: Convert, Datedif, Evaluate, FactDouble, Learn Excel, Microsoft Excel Formulas, Roman, rounddown(), roundup, spreadsheets

RSS feed for comments on this post. TrackBack URI

View the original article here

Sumproduct advanced queries

Posted on 26 May 2011 at Excel Howtos, learn Excel functions of observable by Hui-34 comments

Use the function Sumproduct for multiple criteria in one situation may amount attachments larger than Excel function beyond what it was designed primarily for already designed may with that in mind?

However, Sumproduct also can extend even through the use of 2D ranges along with carefully constructed queries.

Examples are provided below in "example" Excel 2003 is an example of a file.

Your schedule and sold fruit and sold every day

How many bananas sold on 4thMay?

The previously named ranges Setup 3

Use named ranges as it facilitates reading the future versions.

Fruit: C2: H2

Dates: B3: B12

Froitdata: C3: H12

So, how many bananas do not sell to 4thMay?

Use = SUMPRODUCT ((Fruit = D16) * (Date = D15) * FruitData) equation

Returns the correct 31answer

Relevant: searching 2way in Excel

Your schedule and "car" and sold every day. There are several entries on different days, perhaps from different vendors.

How many cars Holden sells on May 3rd?

So, how many cars did not sell in Holden may 3rd?

Use = SUMPRODUCT ((Dates = D17) * (Cars = D18) * CarData) equation

Returns the correct answer 9 = (1 + 5 + 3)

Your schedule and "car" and sold every day, and there are multiple entries for different days.

How many vehicles Ford and Suzuki sells in May 10th?

So, how many vehicles Ford and Suzuki does not sell in May 10th?

Equation = SUMPRODUCT ((Dates = D24) * ((Cars = D25) + (auto = E25)) * as ardata)

Returns the correct answer 13 = (4 + 5 + 3 + 1)

Note that this can extend to add additional queries where you can enter "vehicle type" in any cell in the range D25: H25

= SUMPRODUCT ((Dates = D24) * ((Cars = D25) + (auto = E25) + (auto = F25) + (auto = G25) + (auto = H25)) * as ardata)

Your schedule and "car" and sold every day, and there are multiple entries for different days.

How many cars Toyota and Holden sells in May 10th?

How many cars Toyota and Holden sells in May 10th?

Use = SUMPRODUCT ((Dates = D30) * (Cars = D31: H31) * CarData) equation

Returns the correct answer 21 = (3 + 6 + 6 + 6)

Note that this can be extended to allow additional queries, but you must enter "type of vehicle" in the same position in the header row.

Using the above matrix computational techniques to produce coherent truth table inside Sumproduct formula.

Using = SUMPRODUCT ((B4: B6 = D10) * (C3: E3 = D9) * (C4: E6))

Truth table logic interdependent (B4: B6 = D10) * (C3: E3 = D9) simply says that Matt elements true when conditions and false otherwise

Sumproduct then takes this and hit by data values and accumulated values for total matching values.

It is important to note that the width and height of the columns and rows parameters must match the width and height data region or # value! Ritornd error.

To understand and explain how this works I will use a simple form with 3 rows and columns, see below

Formula: = SUMPRODUCT ((B4: B6 = D10) * (C3: E3 = D9) * (C4: E6)), shown above consists of 3 regions

(B4: B6 = D10) scope column rows x 1 3

(C3: E3 = D9) x 3 row column group 1

(C4: E6) scope column row x 3 3

Breaking formula to components

= SUMPRODUCT ((B4:B6=D10)*(C3:E3=D9)*(C4:E6))

(B4: B6 = D10) * (C3: E3 = D9) is same as hitting arrays 2, representing regions 2 as shown below

You can see which components are True I put 1 and 0 as false

If history 3/may Excel evaluates to 1, as well as having fruit banana, Excel evaluates to 1.

Where does not meet this standard Excel evaluates to 0

Multiplication 3 x 1 and a 1 × 3 × 3 array 3

The (B4: B6 = D10) * (C3: E3 = D9) part of the equation

Then the data is multiplied by

= SUMPRODUCT ((B4:B6=D10)*(C3:E3=D9)*(C4:E6))

This is the same two arrays double 2 3 x 3, which produces a 3 × 3 below:

ThenSumproduct adds all elements of the array to get the final answer 3of.

Can be "embedded" data area in "logic truth table or as a separate element of Sumproduct.

= SUMPRODUCT ((B4: B6 = D10) * (C3: E3 = D9) *(C4: E6)) f = SUMPRODUCT ((B4: B6 = D10) * (C3: E3 = D9) (C4: E6)) both equal

You can add multiple kritria "OR" using the + operator within a criteria

In scenario 3 above, we collect the number of vehicles Ford, Suzuki sold on May 10.

SUMPRODUCT ((Dates = D24) *((auto = D25) + (auto = E25) + (auto = F25) + (auto = G25) + (auto = H25))* as ardata)

Logic or is added to the criteria using the above criteria operator within parking lot +

Logic is added by using the * between the dates and vehicle standards

You can add greater-than (>), less than (<) etc="" and="" other="" logic="" elements="" to="" the="" queries="" to="" suit="" your="">

Examples are provided below in "example" Excel 2003 is an example of a file.

What do you think the above method?

Let us know in the comments below.

Spread some love
It makes you fabulous!

& Navigation functions

Tags: 2D, and (), array, array formulas "coherent truth table" downloads, Excel recognized matrix arithmetic, matrix, or Microsoft Excel formulas, screencasts, sumproduct

RSS feed for comments on this post. TrackBack URI

View the original article here

VBA class registration closed a few hours – join now!

Posted on 20 May 2011 in charts and graphs, VBA macros-4 comments

The Quick declaration for you.

As you know, we opened our first batch of recordings from VBA classes online on May 9. This programme is aimed at beginners & VBA intermediate users. The objective of this session make you awesome in VBA. We close the registration for this program in a few hours more (exactly at 11: 59 pm, Pacific time, on 20 May 2011)

Click here to join our VBA class now.

At the time of writing this post (approximately 7: 30 am, "time" on 20 May), we have 153 participants in this program. This is definitely a bit more of what is expected. But, as I am confident and eager to help as many of you as possible. So go ahead and join the program, because you want to be awesome.

You can watch the lessons whenever you want: there are no live classes. So don't have the Internet at any given time. You can enjoy a vacation or busy at work and still able to learn VBA in your spare time.Get certificate course: at the end of 6 months, you will receive a certificate of completion of the course of the United States. Also know Excel: if you want to learn Excel, then go to school Excel VBA options +.  In this way, could be awesome in Excel and VBA. Plus, you get 9 months access to our online classes where you can discuss topics, ask questions, offer or repeat lessons and download materials. (6 months access to the VBA option only)

We close registration at 11: 59 p.m. Pacific time today (20 May). see this map to see when recording session will be closed in your time zone. (Click on map to download the excel file with chart)

Or Download app countdown timer in our VBA class registration.

Some of you requesting the next batch of VBA class. We plan to reopen this course in September 2011. But, if we get too many students in this instalment, we may be busy for a few weeks more than expected.

So go ahead and join the class on.

We (Hui, Vijay and myself) thank you for supporting our first instalment of category VBA. We are eager to kick start next Monday (23) and share our knowledge of VBA. We hope to learn from your questions, ideas and tips so that we all can benefit from this.

Thank you.

Note: of course, we have a lesson on how to create application countdown timer in VBA row. Join us.

RSS feed for comments on this post. TrackBack URI

View the original article here

Spreadsheets and Monte Carlo simulations (updated)

Sorry, I can read this page, romit content.

View the original article here

Switch "dynamically scenarios using wemshrohat

Posted on 1 June 2011 in Pivot Tables & charts-3 comments

Wemshrohat is the new favorite in Excel attributes. And in 2010 in Excel, such as optical filters are wemshrohat.

Let us be your sales report (link) to multiple vendors. Because you want to show the report to one person at a time, you can use report filters in the pivot table for this view. But find that switching between areas of pain using report filter.

Enter wemshrohat.

Now, just click the name of the region to show report for that region, such as this:

Now, we use wemshrohat Creative Director to make an interactive scenario in Excel, some thing like this:

This method gives the same result as displayed and select scenarios using VBA article, but easier to implement

You need to specify different scenarios in table, like this:

Select the table that you created in step 1, then insert a PivotTable. Use a variable name and variable value row label in the value field.

Select anywhere inside the axis. And now, on the Options tab, click the button "insert morgue". Click to insert a slicer field scenarios.

I'm beyond interpretation creates a form that is not relevant here.

Once you set up the form, simply refer to the pivot table for all values of variables.

Go to PivotTable worksheet and select in the morgue, click CTRL + X to cut it.

Return to the worksheet form and paste it into a morgue.

Wemshrohat Excel by default shows the option to remove the filtered morgue. You can get rid of this button,

1) right click on the slicer
2) go to the slicer settings
3) UN option "show header check

See aside.

That's it, we transform smart scenario morgue ready. Now, you can extend this in several ways. For example, you can write some clever formulas of handling multiple selection wemshrohat. You can compare one scenario and another when you choose one or more of the morgue. Be much more. But let your imagination run wild.

I have made a simple example to demonstrate this technique.

Please download and open it in Excel 2010.

Screening action "scenario" and "axis" model for understanding how morgue setting, and how to do this.

As I said, is the favorite new attributes wemshrohat in Excel. And may use them as much as possible because it is easy to use and very powerful.

What about you? Do you frequently slide? What was your experience like? Please share ideas and tips with your comments.

1) create a "dashboard" in Excel using wemshrohat
2) create a dynamic chart using PivotTable report filters "
3) remove duplicates and sort the list by using the pivot table
4) more on modeling & pivot tables

RSS feed for comments on this post. TrackBack URI

View the original article here

Mod () function in Excel to "implement escalation" [financial modeling tutorial]

Posted on 24 May 2011 in financial modeling, learning to Excel-9 comments

Take the apartment for rent $ 1000 per month and puts the owner provided escalation increased 10% in every 3 years. How you can model this in excel? In this tutorial and see how that escalation in certain frequency using the mod function is implemented in excel.

Function calculates the lip, the Ministry of defence in the Division. For example,

Simple function, it calculates the remainder, but can check some of extremely complex tasks.

We want to create a form where user can change the frequency of escalation, for example, if you are able to negotiate escalate every 4 years, should be flexible enough to incorporate that sample.

First of all we create the search, if the year escalation. We use here the function mod (). For example, if the requirement of three years, then stepped up to take the Ministry of Defence No. 3 year. If the remainder is zero, it means increasing year (I know it can be confusing ... So my suggestion-playing with the mod function ()).

Once the escalation, we total up the escalation and find even one year total escalation.

Then we simply take the basic numbers, and the number of escalations at this point in time.

I have no doubt in my mind that a strong (and confusion). And confusion due to the nonlinear nature of the post (sometimes gives rise to consequences and results sometimes reduced)!.

If you plan to implement anything attribute for example, you want to color every fifth row of your excel, or you need to specify every day of the week, or anything like that, could be the function mod () function is very easy!

I know that the easiest way to do copy paste values in your form and update it manually. Easy to understand, but at the same time is not flexible. How implemented this functionality in your forms?

I have created a template for you, so give your subheadings and to bind the form to obtain cash numbers! You can download the same. from here you can go through and fill the yellow boxes. It also recommended trying to create this structure yourself (so you can get the hang of what information is logged).

Also you can download this template filled and check, if the information that you recorded, match mine or not!

For any queries regarding the impact of monetary or financial modelling, feel free to put comments in the blog or write an email to [email protected]

Modeling financial one repeated uses Excel. Please go through the below articles for more information,

This article is written in the Pristina madrasa has. The author can be contacted at [email protected].
Awesome try training institution pristine CFA, Olli, etc. They train people in HSBC, etc. of the Board of Auditors. Is partnering with the Pristina madrasa has to bring a training program on the Internet you excel financial modeling Chandoo.org.

Spread some love
It makes you fabulous!

& Navigation functions

Tags: downloads, finance, financial modeling, financial modelling school, Excel, Microsoft Excel formulas, MOD (), modeling, pristine, screencasts

RSS feed for comments on this post. TrackBack URI

View the original article here

Introduction to programming-introduction lesson "from VBA

Posted on 13 May of 2011 with VBA macros-13 comments

Andchallenge in VBA row. Many students who joined the no background programming VBA program. They may have written some simple software for a long time, but most lack a basic understanding of programming. Teaching VBA can be difficult if we tackle this problem.

Thus, we have added a lesson on "Introduction to programming". In this lesson, we aim to provide programmes for non-brogramers.

Because many of you think of join our VBA classes, it is appropriate to give this intro to programming lesson as lesson demonstration. Please see below.

In this lesson you will learn,

What do the terms software programming implies? Hello World program in conceptions of elements handlingmodolarizationkommintingravikal variablisobiratorskonditionslobsixsibshn fababrograming.

Click here to download the presentation slides [pdf]

Click here to load the workbook with the macros example HelloWorld (need to display the code in the workbook).

There is more to this lesson. In part 2 (30 minutes), we discuss various programming language & share tips on how to start programs.

You can get part 2 and more tutorials in VBA to join our VBA classes.

Click here to learn more about VBA tutorial & enrollment.

Please note that registrations will be closed Friday-20 May.

Please tell me how to introduce programming to the layman, by using comments. I would like to learn from your perspective.

Note: join row VBA if you want to become awesome in VBA and move forward.

PPS: also see the introduction to Excel.

RSS feed for comments on this post. TrackBack URI

View the original article here

App countdown timer in VBA to remind you about "closing time fabaklasis"!!!

Posted on 18 May 2011 in products, and VBA macros-12 comments

Here is the countdown timer to make cool in VBA to remind you of our close registration fabaklasis!

I know it's awesome clarity. It will give you a few seconds before reading further.

….

Copy already? Great.

I was thinking of ways to tell you that you've got less than 3 days to join row VBA. Then hit me, why not make an Excel workbook that tell you how much time you have got? So I did that.

Here is demo video of how VBA application (watch on YouTube):

Click here to download the workbook. Please enable macros to see it.

Note: you must drag and drop this file in Excel 2007 or above to see it.

First, we must tell you about its borders:

This workbook assumes that your computer is located in hotspot (or city) you have chosen.Current time is fetched from the local time for your computer by using the formula now ().

Now, basic construction can be broken for this workbook to 3 parts:

HotSpot/city siliktionkontdoon timerformatingi took an outline of the world and put it in a blank sheet. This may add 9 hotspots draw the nine circles. I name these hotspots spot1, spot2 ...You can guess, spot9As, all these points with the region, Australian PST to Time.I might assign macros to each of them once. To modify the macros only cell named valsbot with the name of the location you clicked. building immediately clicked, fetch the corresponding closing time of a table as follows:
Then, I'm calculating time remaining by subtracting the current closing time similar logic is used ... to choose city. Insert checkbox and associated with a cell named also set shootimiri startimer a macro to the check box macro macro will call startimer. different name-(kontdoontimi) in this regard, I wrote some time loop will check if the correct shootimer and ask Excel to update korintimi when examining each symbol sikondthi from the uploaded file.

I leave that to your imagination.

Of course, the whole point of this is very simple.

If you want to learn VBA, please join our VBA classes. We will registration closes in 3 days. And then I'll be busy for the next few months teaching VBA for those of you who joined us.

Click here to join our VBA classes.

Note: when you join our class VBA, you get to learn how to create this timer application in detailed lesson 40 minutes. This is just one of many lessons in the classroom. So, join us.

Spread some love
It makes you fabulous!

& Navigation functions

Tags: Countdown, and the date and time of download, INDEX (), Excel macros identification, maps, screencasts, timer, VBA

RSS feed for comments on this post. TrackBack URI

View the original article here