USD > EUR, USD > GBP, CAD > JPY, etc.). To work with currency exchange rates, STOCKHISTORY requires a currency pair entered as text. STOCKHISTORY takes up to 11arguments, most of which are optional. For a more complete description of all arguments and properties, see ourSTOCKHISTORY functionpage. Theintervalargument specifies the time period to use between prices. The options are daily (0), weekly (1), or monthly (2). In this case, we want a monthly interval, so we provide 2....

With default tweaks, the number 1 corresponds to Sunday and the number 7 corresponds to Saturday. The CHOOSE function simply maps numbers to values. The first argument is the number to map, and subsequent arguments represent associated values. With a date from column B, WEEKDAY returns a number which is fed to the CHOOSE function. CHOOSE returns the value at that position in the list of abbreviations. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable....

This means the first occurrence of a customer is 1, the second is 2, and so on. Finally, to force a 1 or 0 result, we add zero. The math operation causes Excel to coerce TRUE and FALSE to equivalent numbers, 1 and 0. Note: The example above uses first name for customer id. This is not realistic, but it makes it easy for the human eye to track. In normal data, customer id will be a unique number of some kind....

Use Paste Special to selectively paste only parts of what was copied. On the Mac, Ctrl + X also works Related videos The videos below demonstrate this shortcut.

By default, VLOOKUP will do an approximate match. Read below to see some examples of how VLOOKUP can cause trouble when you don’t manage match behavior. Note: theMATCH functionhas this same behavior match pop in is optional and defaults to approximate match. In other words, it returns the last number that isless than or equal to the lookup value. Even worse, the result might look completely normal. Video: Great video by Oz du Soleil onhow binary search really works in Excel....

Data Types are a special kind of data in Excel with properties that can be accessed like fields. Example Data Types include Stocks, Geography, and Currency. Linked data types retrieve data from an external source and can be refreshed to show the latest available information. These should be values Excel can recognize as potential Data Types. Next, select or bang out the fields you want to display. In the example above, we selected the “Geography” Data punch in....

Data validation is a feature in Excel used to restrict what a user can enter into a cell. Data validation can also be used to provide a dropdown menu with predefined choices when a cell is selected.

Explanation Data validation rules are triggered when a user adds or changes a cell value. The ISNUMBER function returns TRUE when a value is numeric and FALSE if not. As a result, all numeric input will pass validation. Be aware that numeric input includes dates and times, whole numbers, and decimal values.

Explanation Data validation rules are triggered when a user adds or changes a cell value. The ISTEXT function returns TRUE when a value is text and FALSE if not. As a result, all text input will pass validation, but numbers and formulas will fail validation.

Explanation Data validation rules are triggered when a user adds or changes a cell value. The UPPER function changes text values to uppercase, and the EXACT function performs a case-sensitive comparison. The AND function takes multiple arguments (logical conditions) and returns TRUE only when all arguments return TRUE. If either condition is FALSE, AND returns FALSE and input fails data validation. Numbers, punctuation, and spaces are not affected. AND returns TRUEonly if all the conditions are met....

Explanation Data validation rules are triggered when a user adds or changes a cell value. To get a number for a Monday-based week, thereturn_typeargument for WEEKDAY is provided as 2. The WEEKDAY result is then compared to 6. Any value less than 6 is a weekday, so the expression returns TRUE and validation succeeds. The TODAY function takes no arguments. you’re able to format the value returned by TODAY with a datenumber format....

Explanation Data validation rules are triggered when a user adds or changes a cell value. The TODAY function returns today’s date (recalculated an on on-going basis). The AND function takes multiple logical expressions and returns TRUE only when all expressions return TRUE. If both logical expressions return TRUE, the AND function returns TRUE and validation succeeds. If either expressions returns FALSE, data validation fails. AND returns TRUEonly if all the conditions are met....

Explanation Data validation rules are triggered when a user adds or changes a cell value. The TODAY function takes no arguments. you’re able to format the value returned by TODAY with a datenumber format. If you need current date and time, use…