Result in Microsoft Excel
Example data matrix excel add in Using Barcode printer for Excel Control to generate, create ECC200 image in Office Excel applications. barcode generator excel 2010 free data matrix excel add in www.OnBarcode.comfree upca barcode font for excel Using Barcode drawer for Office Excel Control to generate, create Universal Product Code version A image in Microsoft Excel applications. free upca barcode font for excel www.OnBarcode.com=CONCATENATE (Holm, Aidan) =LEFT ([PropertyCode],2) gs1128 font excel Using Barcode maker for Excel Control to generate, create EAN / UCC  14 image in Microsoft Excel applications. gs1128 font excel www.OnBarcode.comData Matrix 2d Barcode Printer In None Using Barcode generation for Office Excel Control to generate, create ECC200 image in Microsoft Excel applications. www.OnBarcode.comResult
ean 13 excel font Using Barcode maker for Microsoft Excel Control to generate, create UPC  13 image in Excel applications. ean 13 excel font www.OnBarcode.comGenerate PDF417 In None Using Barcode creator for Excel Control to generate, create PDF417 image in Microsoft Excel applications. www.OnBarcode.comAidan Holm Returns the first two characters of the code Returns the last three characters of the code Changes baseball cap to baseball cap Returns the length of the text string BOSTON becomes boston boston becomes BOSTON BOSTON or boston becomes Boston In string baseball cap, returns 10 code 128 barcode excel macro Using Barcode encoder for Office Excel Control to generate, create Code 128 Code Set B image in Microsoft Excel applications. code 128 barcode excel macro www.OnBarcode.comMSI Plessey Encoder In None Using Barcode encoder for Excel Control to generate, create MSI Plessey image in Office Excel applications. www.OnBarcode.comRIGHT
Create Data Matrix 2d Barcode In .NET Framework Using Barcode creation for VS .NET Control to generate, create Data Matrix 2d barcode image in Visual Studio .NET applications. www.OnBarcode.comECC200 Creator In ObjectiveC Using Barcode drawer for iPad Control to generate, create DataMatrix image in iPad applications. www.OnBarcode.comRIGHT([Column1],3) GTIN  13 Creator In Java Using Barcode encoder for Java Control to generate, create GTIN  13 image in Java applications. www.OnBarcode.comDrawing Linear 1D Barcode In Java Using Barcode creation for Java Control to generate, create Linear Barcode image in Java applications. www.OnBarcode.com=LEFT ([PropertyCode],3) Create ANSI/AIM Code 128 In Java Using Barcode printer for Java Control to generate, create Code128 image in Java applications. www.OnBarcode.comBarcode Recognizer In Java Using Barcode reader for Java Control to read, scan read, scan image in Java applications. www.OnBarcode.comTRIM
EAN13 Decoder In C# Using Barcode reader for .NET framework Control to read, scan read, scan image in VS .NET applications. www.OnBarcode.comBarcode Reader In Visual Studio .NET Using Barcode reader for .NET framework Control to read, scan read, scan image in VS .NET applications. www.OnBarcode.comTRIM([Column1] Encode QR In C#.NET Using Barcode maker for Visual Studio .NET Control to generate, create QRCode image in VS .NET applications. www.OnBarcode.comCreate Code128 In None Using Barcode generator for Font Control to generate, create Code128 image in Font applications. www.OnBarcode.com=TRIM ([" baseball
Barcode Generation In None Using Barcode maker for Software Control to generate, create Barcode image in Software applications. www.OnBarcode.comPDF417 2d Barcode Drawer In .NET Framework Using Barcode printer for Reporting Service Control to generate, create PDF 417 image in Reporting Service applications. www.OnBarcode.comcap"] LEN([Column1]) =LEN([Item]) LOWER UPPER PROPER
LOWER([Column1]) =LOWER[(Boston)] UPPER([Column1]) =UPPER[(Boston)] PROPER([Column1]) =PROPER[(Boston)] SEARCH
SEARCH(find_txt, within_txt, Start_num) =SEARCH("cap", "baseball cap", [10]) REPLACE
REPLACE(old_text, start_num, num_chars, new_text) =REPLACE([Item],1, 0,"FabWear ") Inserts FabWear in the first position (1), replacing no text (0) CHAPTER 5 CREATING CUSTOM CALCULATIONS IN SHAREPOINT
Applying Logical Functions
You use logical functions to create conditional formulas. You can use logical functions to test whether conditions are true or false, and to make logical comparisons between expressions. For example, using the IF function, you can test if the value in one column is greater or less than a specific value. If it evaluates as true, you could apply a formula to the value; if not, leave it as is. In practical terms, let s say you want to discount all the items in a store s inventory that had been in stock prior to January 1. You could use the IF function to examine the DateAcquired column to determine if an item was acquired prior to January 1. If it was, you would apply the discount; if it wasn t, you d leave it at the current price. The formula might look like the following: =IF([DateAcquired]>1/1/2006,[Price][Price]*30%,[Price]) With an IF function, the first part of the statement sets up the condition: is [DateAcquired]>1/1/2006 The second part of the statement, after the first comma, says to do this if the condition is true. In this case, you d discount the item by 30 percent. The third part of the statement, after the second comma, says to do this is the condition is false. In this example, you d keep the price as it is. The syntax for an IF function is as follows: =IF(logical_test,value_if_true,value_if_false) You can use AND, OR, and NOT functions to combine conditions and set up different conditions. For example, let s say in the preceding example, you want to apply a 30 percent discount if the item was acquired between 10/1/2005 and 1/1/2006, and a 50 percent discount if it was older than 10/1/2006. You could do this with the AND function. The following formula contains an AND function (AND(logical1,logical2,...) that sets up the condition of dates between 10/1/2005 and 12/31/2006 (<1/1/2006) and a nested IF: =IF(AND([DateAquired]<1/1/2006,>=10/1/2005),[Price][Price]*30%, IF([DateAcquired]<10/1/2005,[Price][Price]*50%,[Price])) These two IF statements are dependent on each other (for more about nested functions, see Nesting Functions for Maximum Efficiency later in this chapter). Use AND when you want the condition to meet both criteria. You can use OR if you want the condition to meet either criteria; for example, if you acquired the item prior to 1/1/2006 OR the item is damaged. You could also use NOT to include only items that haven t been damaged: =NOT([Condition]="Damaged" CHAPTER 5 CREATING CUSTOM CALCULATIONS IN SHAREPOINT
When creating formulas with logical functions, you can often find more than one way to develop the formula. The key is to create an accurate formula that makes sense to you. As long as it meets those criteria, it doesn t matter which functions you choose to do it. Table 57 describes the logical functions. Table 57. Logical Functions Test for Conditions
Function
Syntax
IF(logical_test, value_if_true, value_if_false) Description
Tests to see if a condition is true. If it is, the formula sets the first value. If it isn t, it sets the second. Example
=IF[FlightMiles]<500, [FlightMiles]*50%+ [FlightMiles], [FlightMiles] Result
Evaluates whether a flight is less than 500 miles. If it is, adds a 50 percent miles bonus. Evaluates to true when a flight is more than 500 and less than 1,000 miles. Evaluates to true when a flight is more than 500 miles or the destination is Los Angeles. Anywhere but Detroit. AND(logical1, logical2,...)

