{"id":18731,"date":"2013-05-08T15:55:45","date_gmt":"2013-05-08T10:25:45","guid":{"rendered":"http:\/\/vskills.in\/certification\/tutorial\/?p=18731"},"modified":"2024-04-12T14:24:18","modified_gmt":"2024-04-12T08:54:18","slug":"use-statistical-date-and-time-financial-and-logical-functions","status":"publish","type":"page","link":"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/","title":{"rendered":"Use Statistical, Date and Time, Financial, and Logical Functions"},"content":{"rendered":"

Use Statistical, Date and Time, Financial, and Logical Functions<\/strong><\/h4>\n

Microsoft Excel spreadsheets have become somewhat of a standard for data storage,
\nat least for smaller data sets. This, along with the program often being packaged with new
\ncomputers, naturally encourages its use for statistical analyses. This is unfortunate, since
\nExcel is most decidedly not a statistical package.
\nHere\u2019s an example of how the numerical inaccuracies in Excel can get you into trouble.
\nConsider the following data set:
\nData Display
\nRow X Y
\n1 10000000001 1000000000.000
\n2 10000000002 1000000000.000
\n3 10000000003 1000000000.900
\n4 10000000004 1000000001.100
\n5 10000000005 1000000001.010
\n6 10000000006 1000000000.990
\n7 10000000007 1000000001.100
\n8 10000000008 1000000000.999
\n9 10000000009 1000000000.000
\n10 10000000010 1000000000.001<\/p>\n

The Date and Time Components<\/h2>\n

In the table below I entered a\u00a0date<\/strong>\u00a0in cell B2, a\u00a0time<\/strong>\u00a0in C2, and a formula in D2 that adds the two together. Excel automatically formatted this cell using a\u00a0m\/d\/yyyy hh:mm<\/strong><\/em>\u00a0format. I changed the row 3 cell formatting to\u00a0General<\/strong>\u00a0so you can see the\u00a0serial number values<\/strong>.<\/p>\n

\"Date<\/a><\/p>\n

You can clearly see that Date is a integer value, Time is a decimal fraction, and the Date\/Time format has both together in one number<\/p>\n

Why Does the INT Function Extract a Date?<\/h3>\n

All Dates, Times, and Date-Times are know as\u00a0serial numbers<\/strong>\u00a0in Excel. You don\u2019t normally see the underlying serial number. A Date-Time value like 9\/14\/2012 8:43 AM looks like that in the cell\u00a0AND<\/strong>\u00a0in the formula bar.<\/p>\n

\"1\"<\/a><\/p>\n

Only when you change the cell formatting to\u00a0General<\/strong>\u00a0do you see the Date-Time serial number. The integer portion is the date serial number and the fractional part is the time serial number.<\/p>\n

\"2\"<\/a><\/p>\n

This is why the INT function will work to extract a Date from a Date-Time value.\u00a0The integer value is a date serial number<\/strong>. The trick is to change the cell formatting to a Date format so that Excel will show you the Date.<\/p>\n

\"3\"<\/a><\/p>\n

Logical Functions in Excel (IF, AND, OR, NOT)<\/h1>\n

Conditional and logical functions are used very often in Excel.\u00a0 Most of the time we use them when we want to test if a certain criteria is met, and based on that result, we generate an output.\u00a0 This gives our spreadsheet the power to make decisions.<\/p>\n

Let\u2019s look at the IF function first, probably the most common.\u00a0 The IF function has the following format:<\/p>\n

=IF(logical_test,value_if_true,value_if_false)<\/strong><\/p>\n

In this first example, we want to determine if students passed a course.\u00a0 If they received at least 70% they passed, otherwise they failed.\u00a0 We see that in C2 our formula is, IF(B6>=70%,return the text string PASS, otherwise return the text string FAIL).\u00a0 We then copy and paste that formula down to the rest of the cells.\u00a0 Remember that when we want to return a text string to a cell we have to put it in quotes, otherwise we get an error.\u00a0 And we double check and it looks like our formula is correct since those students that got greater than or equal to 70% passed.\u00a0 So that\u2019s a simple example of an IF statement.<\/p>\n

\"1\"<\/a><\/p>\n

We can also nest IF statements within IF statements.\u00a0 In this second example here, we have several people and their incomes listed.\u00a0 We want to determine which income tax bracket they fall into based on the guidelines on the right.\u00a0 So our formula is going to ask:\u00a0 If the income is less than or equal to the upper limit of the tax bracket, return as a value that tax bracket, otherwise next check the upper limit for the next tax bracket and so on until it finds one that matches.\u00a0 We see that the formula in C2 is:<\/p>\n

=IF(B2<=8700, 10%, IF(B2<=35350, 15%, IF(B2<=85650, 25%, IF(B2<=178650, 28%, IF(B2<=388350, 33%, 35%)))))<\/p>\n

\"2\"<\/a><\/p>\n

After entering the formula we copy it all the way down for the rest of the names.\u00a0 With nested IF statements, think of a big box that when you open it, has a smaller box in it, and that smaller box has an even smaller box within that, and when you open that one, you find an even smaller box.\u00a0 And so on.\u00a0 The nested IF statements keep checking for a condition until they find one that is TRUE, otherwise they will return a default value if nothing turns out to be TRUE.\u00a0 And double-checking, it looks like our formula is correct since Excel gives us the correct tax brackets for all the people listed here.<\/p>\n

Let\u2019s move on to the AND logical function.\u00a0 The AND function takes the form:<\/p>\n

\n

Apply for MS Excel Certification Now!!<\/h3>\n

https:\/\/www.vskills.in\/certification\/excel-online-certification-course<\/a><\/p>\n

Back to Tutorial<\/strong><\/a><\/h4>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Use Statistical, Date and Time, Financial, and Logical Functions Microsoft Excel spreadsheets have become somewhat of a standard for data storage, at least for smaller data sets. This, along with the program often being packaged with new computers, naturally encourages its use for statistical analyses. This is unfortunate, since Excel is most decidedly not a…<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"categories":[47],"tags":[5118,5116,5117,5115],"class_list":["post-18731","page","type-page","status-publish","hentry","category-ms-excel","tag-and-logical-functions","tag-date-and-time","tag-financial","tag-use-statistical"],"acf":[],"yoast_head":"\nUse Statistical, Date and Time, Financial, and Logical Functions - Tutorial<\/title>\n<meta name=\"description\" content=\"Use statistical, date and time, financial, and logical functions. Govt of India Certification for Excel\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Use Statistical, Date and Time, Financial, and Logical Functions - Tutorial\" \/>\n<meta property=\"og:description\" content=\"Use statistical, date and time, financial, and logical functions. Govt of India Certification for Excel\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"Tutorial\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/vskills.in\/\" \/>\n<meta property=\"article:modified_time\" content=\"2024-04-12T08:54:18+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/excelsemipro.com\/wp-content\/uploads\/2010\/07\/Date-Time-Values.png\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/\",\"url\":\"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/\",\"name\":\"Use Statistical, Date and Time, Financial, and Logical Functions - Tutorial\",\"isPartOf\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/excelsemipro.com\/wp-content\/uploads\/2010\/07\/Date-Time-Values.png\",\"datePublished\":\"2013-05-08T10:25:45+00:00\",\"dateModified\":\"2024-04-12T08:54:18+00:00\",\"description\":\"Use statistical, date and time, financial, and logical functions. Govt of India Certification for Excel\",\"breadcrumb\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/#primaryimage\",\"url\":\"http:\/\/excelsemipro.com\/wp-content\/uploads\/2010\/07\/Date-Time-Values.png\",\"contentUrl\":\"http:\/\/excelsemipro.com\/wp-content\/uploads\/2010\/07\/Date-Time-Values.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.vskills.in\/certification\/tutorial\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Use Statistical, Date and Time, Financial, and Logical Functions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#website\",\"url\":\"https:\/\/www.vskills.in\/certification\/tutorial\/\",\"name\":\"Tutorial\",\"description\":\"Vskills - A initiative in elearning and certification\",\"publisher\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.vskills.in\/certification\/tutorial\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#organization\",\"name\":\"Vskills\",\"url\":\"https:\/\/www.vskills.in\/certification\/tutorial\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.vskills.in\/certification\/tutorial\/wp-content\/uploads\/2017\/07\/vskills-min-logo.jpg\",\"contentUrl\":\"https:\/\/www.vskills.in\/certification\/tutorial\/wp-content\/uploads\/2017\/07\/vskills-min-logo.jpg\",\"width\":73,\"height\":55,\"caption\":\"Vskills\"},\"image\":{\"@id\":\"https:\/\/www.vskills.in\/certification\/tutorial\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/vskills.in\/\",\"https:\/\/x.com\/vskills_in\",\"https:\/\/www.linkedin.com\/company-beta\/1371554\/\",\"https:\/\/www.youtube.com\/channel\/UCMWnscxPwRF_PqXo9B7q_Tw\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Use Statistical, Date and Time, Financial, and Logical Functions - Tutorial","description":"Use statistical, date and time, financial, and logical functions. Govt of India Certification for Excel","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/","og_locale":"en_US","og_type":"article","og_title":"Use Statistical, Date and Time, Financial, and Logical Functions - Tutorial","og_description":"Use statistical, date and time, financial, and logical functions. Govt of India Certification for Excel","og_url":"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/","og_site_name":"Tutorial","article_publisher":"https:\/\/www.facebook.com\/vskills.in\/","article_modified_time":"2024-04-12T08:54:18+00:00","og_image":[{"url":"http:\/\/excelsemipro.com\/wp-content\/uploads\/2010\/07\/Date-Time-Values.png","type":"","width":"","height":""}],"twitter_misc":{"Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/","url":"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/","name":"Use Statistical, Date and Time, Financial, and Logical Functions - Tutorial","isPartOf":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/#primaryimage"},"image":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/#primaryimage"},"thumbnailUrl":"http:\/\/excelsemipro.com\/wp-content\/uploads\/2010\/07\/Date-Time-Values.png","datePublished":"2013-05-08T10:25:45+00:00","dateModified":"2024-04-12T08:54:18+00:00","description":"Use statistical, date and time, financial, and logical functions. Govt of India Certification for Excel","breadcrumb":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/#primaryimage","url":"http:\/\/excelsemipro.com\/wp-content\/uploads\/2010\/07\/Date-Time-Values.png","contentUrl":"http:\/\/excelsemipro.com\/wp-content\/uploads\/2010\/07\/Date-Time-Values.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/use-statistical-date-and-time-financial-and-logical-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.vskills.in\/certification\/tutorial\/"},{"@type":"ListItem","position":2,"name":"Use Statistical, Date and Time, Financial, and Logical Functions"}]},{"@type":"WebSite","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#website","url":"https:\/\/www.vskills.in\/certification\/tutorial\/","name":"Tutorial","description":"Vskills - A initiative in elearning and certification","publisher":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.vskills.in\/certification\/tutorial\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#organization","name":"Vskills","url":"https:\/\/www.vskills.in\/certification\/tutorial\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#\/schema\/logo\/image\/","url":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-content\/uploads\/2017\/07\/vskills-min-logo.jpg","contentUrl":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-content\/uploads\/2017\/07\/vskills-min-logo.jpg","width":73,"height":55,"caption":"Vskills"},"image":{"@id":"https:\/\/www.vskills.in\/certification\/tutorial\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/vskills.in\/","https:\/\/x.com\/vskills_in","https:\/\/www.linkedin.com\/company-beta\/1371554\/","https:\/\/www.youtube.com\/channel\/UCMWnscxPwRF_PqXo9B7q_Tw"]}]}},"_links":{"self":[{"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/pages\/18731","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/comments?post=18731"}],"version-history":[{"count":6,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/pages\/18731\/revisions"}],"predecessor-version":[{"id":130036,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/pages\/18731\/revisions\/130036"}],"wp:attachment":[{"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/media?parent=18731"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/categories?post=18731"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vskills.in\/certification\/tutorial\/wp-json\/wp\/v2\/tags?post=18731"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}