Tuesday, February 28, 2017

5 easy methods to Double verify Your Excel formulation.

whats up Excellers. i am excited to share with you some more method enjoyable, and in specific a guidelines to double check your Excel formulation are on aspect!.
Let’s face it, formulation are unbelievable, however they could can additionally trigger massive headaches even with a tiny mistake, Excel will go ahead and do what you tell it to, so you ought to be certain your are giving it the appropriate guidance. here is an easy checklist to double check your Excel formula work.

1. Are You blended Up?…..
it is fantastically effortless to get all combined up principally if you have a tonne of formulation going on in your worksheet. from time to time it is not so effortless to identify blunders with mix ups.
This method =D2-D3 appears at lot like =D3-D2. Take a few minutes to ensure there aren't any small discrepancies like this one.

2. Roughly know What You answer should still Be…..
if you're in a position to, then take a stab at a ballpark effect in your formulation. I do this when checking my formulas, as an instance the calculation maybe the percent change between two sales figures as in my example beneath. i know that it should be a negative number, and round about a quarter?. This gives ball park evaluation to assist me spot and evident concerns with my formulation. this is a really simple method to check formulas, like in my example understanding it's a poor number, but the rest greater specific, it’s not so helpful. you can at all times substitute some standard numbers in the system to make it less demanding to determine what the reply may still be.
FORMULA FRIDAY CHECK YOUR EXCEL FORMULAS3 three. check The Order Of Precedents or Operations.
What the heck does that suggest?, smartly, it is lower back to some fundamental mathematics classification…. but this relates to the order during which Excel calculates.
It’s crucial to keep in mind that for those who create a components with several operators, Excel evaluates and performs the calculation in a selected order. for instance, Excel at all times performs multiplication earlier than addition. This order is known as the order of operator precedence. you can force Excel to override the constructed-in operator priority through the use of parentheses to specify which operation to consider first.
Then order of calculations may also be with ease remembered through the acronym BEDMAS
here is an illustration. right here components produces 11 as a result of Excel calculates multiplication earlier than addition. The system multiplies 2 via 3 and then provides 5 to the result.
In distinction, in case you use parentheses to alternate the syntax, Excel adds 5 and 2 collectively and then multiplies the result by means of three to provide 21.
See how this changes issues?…. in case you need to see greater instance of this in action take a look at my blog submit method Friday – How Excel Calculates – The Order Of Excel Calculations.
4. change Your View To demonstrate formulas…
a very quick method to check formulas is to make use of the reveal formula function. This allows you to see the entire formulas on your worksheet on the equal time. There are a couple of easy methods to try this.
  • Hit the CTRL+` shortcut (the grave accent)
  • formulas Tab – system Auditing – reveal formulation
    which you could now see the formulation.

    5. be certain All formula Arguments Are Accounted For…
    if you're the use of a feature in Excel then it could actually have distinctive arguments that are required. If here's the case then make sure they are all accounted for. As you begin to category the feature, Excel will reveal the dialog field to e-book you during the ingredients of the method.
    formulas get simpler with follow and even more observe. in case your formulation returns an error, or isn't giving an expected influence, then work via some troubleshooting within the steps above. The error is always evident when you look for it after which easily fastened.

    Don’t neglect to check in to the Excel at Excel newsletter for 3 free Excel counsel the primary Wednesday of the month. just click on the sign up form to the right or use the link under.

    Monday, December 12, 2016

    Convert incoming XML to Excel Sheet

    Currently most of our clients got here with a requirement to create an excel file as an output of an xml. state of affairs was Proxy-SAP PI 7.31 (Java Stack)-Third celebration – output as Excel Sheet. First PoC of this requirement was to create a goal file by simply giving output.xls and see what fields populated. As anticipated output get populated as xml.
    To populate goal file as Excel file we need to do transformation somewhere between the XML got and target file generation. we have 2 options –

  • Transformation at the time of Mapping
  • Write Adapter Module in Java

  • on the time of mapping we are able to achieve excel output via XSLT mapping. For this answer we deserve to convert incoming XML file to MS Excel XML structure.
    For the conversion of XML to MS Excel XML format we need to use under code in xslt.
    <?mso-application progid="Excel.Sheet"?><xsl:stylesheet version="1.0"xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:xsl="http://www.w3.org/1999/XSL/radically change" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:workplace" xmlns:x="urn:schemas-microsoft-com:workplace:excel" xmlns:ss="urn:schemas-microsoft-com:workplace:spreadsheet">
    explanation of this scenario can be found below.
    Convert incoming XML to Excel or Excel XML – part 1 – XSLT means
    second option is to write down an adapter module to transform incoming XML to CSV and then to Excel Sheet.
    Pre-Requisite and Assumptions of the building:

  • 1. Apache POI open average for excel Conversion
  • 2. SAP PI specific development libraries

  •   com.sap.aii.af.cpa.svc.api.jar
    complete clarification of this state of affairs can be discovered below.

    Links and related books

    Excel 2007 VBA Macro Programming

    Microsoft Excel VBA Programming for the Absolute Beginner

    Friday, December 9, 2016

    Primary Excel Parser

    A simple excel parser that parses every row to an associative array.
    When to use this?

  • Your excel file has the header on the first row, and records on the leisure rows.
  • You want to map the header titles to database keys.
  • You wish to map the information values to database enums.
  • example
    You need to parse an excel file with this format.
    identify Age Gender mobilephone 1 cell 2 invoice 18 Male 222-222-2222 777-777-7777 Peter 25 Male 222-333-4444 555-666-7777 Monica 31 female 666-666-6666 555-555-5555
    and you wish to map the header titles to database desk fields, and map genders to 'm' or 'f'.
    $parser = new Jhesyong\Excel\Parser; // Map 'identify' to 'name', 'Age' to 'age', and 'Gender' to 'gender'. $parser->addHeader('identify', 'name'); $parser->addHeader('Age', 'age'); // Map the values: 'm' is for 'Male', and 'f' is for 'female' $parser->addHeader('Gender', 'gender')->withOptions(['m' => 'Male', 'f' => 'Female']); // you can use a daily expression and move a callback to map numerous header titles. // in this case, 'cell 1' may be mapped to 'phone_1' $parser->addHeader('/^cell \d+$/', characteristic($title) return str_replace(' ', '_', strtolower($title)); ); // Load the file and start to parse. flow a callback feature // and it will be called for each row. $parser->loadFile('filename.xlsx')->parse(characteristic($information) // information could be an associative array. var_dump($statistics); ); FAQ I actually have distinctive titles of the equal textual content, but I are looking to map them to distinct database table fields.
    you can add headers with the same title however distinctive box names within the desired order.
    // the first 'phone' should be mapped to 'phone_1', // and the 2d 'cell' can be mapped to 'phone_2' $parser->addHeader('phone', 'phone_1'); $parser->addHeader('phone', 'phone_2'); I just want to use the title as the container name
    that you would be able to omit the 2nd argument.
    // 'cellphone' can be mapped to 'mobile' $parser->addHeader('phone'); I need to pre-define the header mapping and the alternate options.
    that you would be able to prolong the Parser and write your rules in the constructor.

    Links and related books

    Thursday, December 8, 2016

    Excel to Html


    Finally a good program that will help you to import data in the web. Stop to write the data in the tables html, you just have to change the data inside the file Excel and this application read your excel and create the table html responsive with sorting..

    Table Responsive


    Contact Form


    Google Maps



    • 100% Responsive
    • Table Sorting
    • Pagination Table
    • Search for column
    • Ajax Contact Form
    • Single Table with 2 design
    • Multi Table
    • Google Maps
    • Browser Compatibility
    • Google fonts included

    Monday, December 5, 2016

    Generating Personalized Letters Using CSV Data From Google Spreadsheets

    Google Spreadsheets are great for collaborative number crunching. Much like Microsoft Excel, Google Spreadsheets allow you to produce financial models, analyze numerical data, keep track of accounts, etc. But what if you want to produce and deliver documents of a different nature, like letters to various recipients, based on the data contained in a Google Spreadsheet? Instead of going through each row of the spreadsheet one at a time and plugging data into a document template manually, you can use WebMerge to pull data from the spreadsheet and automatically fill in a template document, perhaps, to be delivered in PDF form to certain recipients!

    "How can I do this?" you might ask. Don't worry! We’ll walk through the process in the tutorial below.

    First, we’ll need to have a document ready for merging on WebMerge. In this example, let’s say we want to send students a report card based on data we have in a Google Spreadsheet. In the spreadsheet we have the students’ names, their email addresses, and their GPAs.

    We first go to WebMerge and upload or create a new document in the Document Builder. The document we are about to upload or create is the document that will be automatically populated with the data from our Google Spreadsheet. Our merge document might look something like the following. The following is an example of a cover letter with merge-fields for the students’ names and the students’ GPAs. The merge fields will get populated with the data in our spreadsheet.

    Image of Document Builder for Report Card Cover Letter

    After we have our document template on WebMerge, we want to: make sure our configuration settings are how we want them, test our document, and set up delivery.  Notice that in the delivery setup, we specify an <<other>> merge field. We didn’t create a merge field for the students’ email addresses, but we want to use the email address column in our spreadsheet to specify our email recipients.

    Specify the Merge Field for Emailing

    The above shows that we want to send the document to recipients according to a merge field that wasn’t in our document and has as yet to be specified. We want to send emails to the addresses in the email address column of our spreadsheet, so we specify that we want to create a new merge field called Email_Address and use it as a placeholder for the email addresses in our spreadsheet. In the next step we will match this placeholder as well as the placeholders in our document with the data we get from a CSV version of our spreadsheet.

    Now that we have made our document template and have saved our delivery method, we need to pull the information from our Google Spreadsheet. The easiest way to do this is to simply download the spreadsheet as a CSV file.

    Image of Google Spreadsheets Download as CSV

    Once we have downloaded our data as a CSV file, we can upload that to WebMerge.

    Image of WebMerge Upload Data as CSV

    Upload the data, then in our final two steps we map the merge fields to the columns of our spreadsheet and send our newly created documents off to our recipients.

    Image of WebMerge Map CSV Data to Document Merge Fields

    Click Merge the Data!

    Image of WebMerge Merge the Data Button

    Congrats! You just merged your Google Spreadsheet data with a document template and sent it to all the relevant recipients.

    Image of End Result of WebMerge and Google Spreadsheets Combination

    Want to do more with WebMerge? Check out all the ways you can streamline your documentation process today!

    Suggested Posts: 

    Take Your Mail Merge Online

    New: Merge Excel Spreadsheets on the Fly

    10 signals Your advertising team Have Outgrown Excel Planning