Peter Birkholm-Buch

Azure Functions

2019-09-02 / admin / Kommentarer lukket

These are my thoughts and key take aways from working with Azure Functions for a while now.

Creating a Function in the Azure Portal is easy as Pie

Creating your first Function in the Azure Portal is a simple process and you can use pretty much any language you want. I prefer PowerShell for prototyping and management stuff – like reacting to events where I have to fire some PowerShell command to handle something in Azure – and I use JavaScript/TypeScript for the more heavy programmatic tasks like creating “real” solutions.

As Microsoft adds support for more languages the possibilities of using serverless Functions will extend to other areas. Recently Python was GA’ed (see below) and as soon as PowerShell for Functions is GA’ed “DevOps”-people can become Azure Function Developers too. Functions is not only for web services and databases but for all things serverless! Think Flow/Logic Apps -> Functions written in PowerShell which do IT management operations.

Great Developer Experience

The developer experience for creating, running and testing Functions locally is just perfect. You can do everything completely locally and even offline – just like any other local development stack/platform/toolchain.

Local development in Visual Studio Code on a Mac

Then push changes to a central repo like Azure DevOps where CI/CD pipelines can build and deploy the Function to Azure completely automated.

On the left building the Function deployment package and on the right deploying the package to Azure

If you don’t have Azure DevOps then Functions can pull in code from pretty much any cloud reachable Git repo – it’s completely cross platform.

There is complete support for Visual Studio and Visual Studio Code and lots of other editors for creating Functions so that writing, editing, testing, debugging and so on is a first class experience.

Triggers & Bindings

Functions can be triggered or react to a range of builtin sources like Azure Event Grid, Service Bus, Cosmos DB and so on – and of course HTTP requests. So calling and activating Functions is really easy.

Bindings are a way of declaratively receiving the input from a trigger or other resources and passing the output of the Function to a receiver – like Azure Cosmos DB, storage or Service Bus. This makes it very easy to react to events – get and process data and output the result with very little friction. Moving data in and out of Cosmos is almost like magic (Please note that more complicated usage of Cosmos requires the use of the SDK!).

For me it has completely replaced the necessity to create Web APIs in .NET or Node and deploy to Azure Web App. If you’re doing web services today using the regular technology platforms and you want to move to Azure I would recommend looking into Functions rather than Web Apps for hosting web services.

Proxies & API Management Gateway

Function Proxies is like a miniature API Manangement Gateway (APIM) which can route URL based requests to methods in your Function or even other Functions if you’re scaling out at the implementation level.

For instance a proxy could route requests to /api/shipments to the actual implementation in the “GetShipments” method.

I also really like the actual APIM and the integration with Functions but updating the API specification in the APIM when a Function is updated is a bit of a pain. I would never expose a Function to the internet just through the URL or even Proxies I would always use APIM as the front door.

Automated Scaling

You can deploy using either App Plan or Consumption Plan and unless you have very specific requirements (or extremely high load) I can’t think of a reason not to choose Consumption Plan and just let Azure handle everything.

Some of our APIs are hammered in the morning and Azure just scales the number of “servers” up in seconds and scales back down again when things settle. We haven’t missed a single call yet with Consumption Plan and we did that on App Plan because we ran out of horse power during that single it will never happen freak influx of data moment.

Azure Logic Apps

2019-09-02 / admin / Kommentarer lukket

This is not a guide or any kind of introduction to Logic Apps but my thoughts and key take aways from working with them for a while now.

Events & Connectors

Logic Apps are great for reacting to events – especially Azure events – and I usually use them to build up the outer and more general logic of a system. This is to react to things that happens and maybe do comparison against thresholds and other configuration settings. It’s easy to call out to other services both in Azure, Office and external APIs.

As a general rule I don’t use Logic Apps to update data – I prefer to use Azure Functions for that as SDKs usually supports more advanced retries and error handling. However, some connectors supports this also – your mileage may vary.

The connectors really are the stars of Logic Apps – you can receive and act on data from pretty much any resource in the Microsoft eco system and most enterprise systems that support Azure Active Directory have connectors too.

Developer Support

Creating Logic Apps using the visual designer in the Portal and Visual Studio is a breeze – Visual Studio Code still only supports editing the underlying JSON document but will display the visual designer in read-only mode. Actual development using a repo and Azure DevOps CI/CD is a bit clunky and deployment requires an ARM template to be built using a script. Better support for storing the “code” for a Logic App in a code repository and deployment is something I’m hoping that will be added in the future.

I’ve seen integration developers use Logic Apps to quickly and easily build integration pipelines using HTTP, data parsing, conditions and service bus but they get stuck when it comes to CI/CD.

No deployment slots

Logic Apps doesn’t support deployment slots so it requires a service window of some sort when deploying in higher load scenarios. Since we’re exposing our Logic Apps through the Azure API Management Gateway we mock the requests to our Logics Apps during deployments. This is certainly not ideal and I hope that Logic Apps will get some form of deployment slot capability in the future.

Scalability

Logic Apps on paper supports reacting to and handling 1000s of events and requests per minute (we’ve done that too – Just be aware that the normal limit is 100.000 requests per 5 minutes). However if you’re not carefull then “long” running activations during high load can cause your entire App to freeze. If this happens then a manual restart can become required. If that happens then you have to start thinking about your usage of Logic Apps; the flows, branching, error handling and perhaps if moving to an Azure Function is better fit for what you’re trying to accomplish.

Setting roles and policies on Azure KeyVault to enable getting secrets from an Azure Function

2019-05-01 / admin / Kommentarer lukket

This is just a follow up on this post: https://azure.microsoft.com/en-us/blog/simplifying-security-for-serverless-and-web-apps-with-azure-functions-and-app-service/ for a bit more clarification on the roles and policies required in KeyVault to make this work.

The enable an Azure Function to access secrets in KeyVault you have to do the following:

Create a system assigned managed identity to the Function. Just go to Platform Features in your Function and select Identity and enable the System assigned identity – remember to click save!
Giving the role “Managed Application Reader” to the managed identity of the Function.
1. Go to KeyVault and click on “Access control (IAM) in the menu and click on “Role assignments”.
2. If you want to see existing App/Functions that have assignments select “App Services or Function Apps” from the Type dropdown menu.
3. Click on Add and select the “Managed Applications Reader” from the Role drop down menu.
4. Type in the name of your Function and select it from the menu – make sure you select the actual identity of the Function – see the icon in the screendump below.
5. Click on save.
Assigning at least the “Get Secret” policy to the service principle of the managed identity.
1. Click on “Access policies” in the KeyVault menu
2. Click on Add and click on the “Select principal” fly out menu and type in the name of your Function
3. This time it’s the service principal we want to select – click on it and click on select
4. From the “Secret permissions” drop down menu select at least the “Get” permission.
5. Click on OK.

You’re done and you should now be able to get secrets from your KeyVault in your application settings in your Function.

Payroll Services Firm Transforms its Product Into a Platform with API Management

2019-03-26 / admin / Kommentarer lukket

Bluegarden, a large Scandinavian payroll service, is using Microsoft Azure API Management to gain a simple, efficient, security-enabled way to share application programming interfaces (APIs) with partners. By publishing APIs for key product capabilities, Bluegarden can create an extensible product to fit every customer’s needs, expand its partners and consequently its business, and help keep APIs secure.

Bluegarden Azure API Management Case Study Download

Installing ELMAH on IIS

2019-02-12 / admin / Kommentarer lukket

Just for reference because it just too long looking for this information on how to install ELMAH on IIS to troubleshoot a failling application.

Download ELMAH using Nuget:

nuget install elmah

Copy elmah.dll to the bin folder of your application.

Add the following to the same sections in your web.config

<system.web>
       <httpHandlers>
              <add verb="POST,GET,HEAD" path="elmah.axd" type="Elmah.ErrorLogPageFactory, Elmah" />
       </httpHandlers>
       <httpModules>
              <add name="ErrorLog" type="Elmah.ErrorLogModule, Elmah"/>
              <add name="ErrorMail" type="Elmah.ErrorMailModule, Elmah" />
              <add name="ErrorFilter" type="Elmah.ErrorFilterModule, Elmah" />
       </httpModules>
</system.web>
<system.webServer>
       <modules runAllManagedModulesForAllRequests="true">
              <add name="ErrorLog" type="Elmah.ErrorLogModule, Elmah"/>
              <add name="ErrorMail" type="Elmah.ErrorMailModule, Elmah" />
              <add name="ErrorFilter" type="Elmah.ErrorFilterModule, Elmah" />
       </modules>
       <handlers>
              <add name="Elmah" verb="POST,GET,HEAD" path="elmah.axd" type="Elmah.ErrorLogPageFactory, Elmah" />
       </handlers>
<system.webServer>

Goto localhost/yourwebapplication/elmah.axd

8 Principles of Open Government Data

2016-03-17 / admin / Kommentarer lukket

Open Government Data Principles

Government data shall be considered open if it is made public in a way that complies with the principles below:

1. Complete: All public data is made available. Public data is data that is not subject to valid privacy, security or privilege limitations.

2. Primary: Data is as collected at the source, with the highest possible level of granularity, not in aggregate or modified forms.

3. Timely: Data is made available as quickly as necessary to preserve the value of the data.

4. Accessible: Data is available to the widest range of users for the widest range of purposes.

5. Machine processable: Data is reasonably structured to allow automated processing.

6. Non-discriminatory: Data is available to anyone, with no requirement of registration.

7. Non-proprietary: Data is available in a format over which no entity has exclusive control.

8. License-free: Data is not subject to any copyright, patent, trademark or trade secret regulation. Reasonable privacy, security and privilege restrictions may be allowed.

Compliance must be reviewable.

Definitions

1.	“public” means:The Open Government Data principles do not address what data should be public and open. Privacy, security, and other concerns may legally (and rightly) prevent data sets from being shared with the public. Rather, these principles specify the conditions public data should meet to be considered “open.”
2.	“data” means:Electronically stored information or recordings. Examples include documents, databases of contracts, transcripts of hearings, and audio/visual recordings of events. While non-electronic information resources, such as physical artifacts, are not subject to the Open Government Data principles, it is always encouraged that such resources be made available electronically to the extent feasible.
3.	“reviewable” means:A contact person must be designated to respond to people trying to use the data. A contact person must be designated to respond to complaints about violations of the principles. An administrative or judicial court must have the jurisdiction to review whether the agency has applied these principles appropriately.

Source

This naked html version of these 8 principles was dewikified from the original published version.

This is a re-post of https://public.resource.org/8_principles.html

Definition of Big Data

2015-11-29 / admin / Kommentarer lukket

Common Date Time formulas for Sharepoint – Calculated Fields

2014-03-18 / admin / Kommentarer lukket

From http://abstractspaces.wordpress.com/2009/05/02/common-date-time-formulas-for-sharepoint-calculated-fields/ for future reference:

Get Week of the year
=DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time]))+0.5-WEEKDAY(DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])),2)+1
First day of the week for a given date:
=[Start Date]-WEEKDAY([Start Date])+1
Last day of the week for a given date:
=[End Date]+7-WEEKDAY([End Date])
First day of the month for a given date:
=DATEVALUE(“1/”&MONTH([Start Date])&”/”&YEAR([Start Date]))
Last day of the month for a given year (does not handle Feb 29). Result is in date format:
=DATEVALUE (CHOOSE(MONTH([End Date]),31,28,31,30,31,30,31,31,30,31,30,31) &”/” & MONTH([End Date])&”/”&YEAR([End Date]))
Day Name of the week : e.g Monday, Mon
=TEXT(WEEKDAY([Start Date]), “dddd”)
=TEXT(WEEKDAY([Start Date]), “ddd”)

The name of the month for a given date – numbered for sorting – e.g. 01. January:
=CHOOSE(MONTH([Date Created]),”01. January”, “02. February”, “03. March”, “04. April”, “05. May” , “06. June” , “07. July” , “08. August” , “09. September” , “10. October” , “11. November” , “12. December”)
Get Hours difference between two Date-Time :
=IF(NOT(ISBLANK([End Time])),([End Time]-[Start Time])*24,0)
Date Difference in days – Hours – Min format : e.g 4days 5hours 10min :
=YEAR(Today)-YEAR(Created)-IF(OR(MONTH(Today)<MONTH(Created),AND(MONTH(Today)=MONTH(Created),
DAY(Today)<DAY(Created))),1,0)&” years, “&MONTH(Today)-MONTH(Created)+IF(AND(MONTH(Today)
< =MONTH(Created),DAY(Today)<DAY(Created)),11,IF(AND(MONTH(Today)<MONTH(Created),DAY(Today)
> =DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today)<DAY(Created)),-1)))&” months,
“&Today-DATE(YEAR(Today),MONTH(Today)-IF(DAY(Today)<DAY(Created),1,0),DAY(Created))&” days”

Suggestions for SharePoint Conference 201x

2012-11-19 / admin / Kommentarer lukket

Just got back from an amazing week at the SharePoint Conference 2012 in Las Vegas. Great sessions, great networking (with the people in mean), great social events and not so great swag.

On the way out someone asked me what could be done to improve the experience at the next conference.

This year two things comes to mind:

Presentation skills and wired networking for presentations
Use SharePoint to organize and deliver the conference

Presentation skills

Presentation skills are vital to deliver any message and especially when doing technical talks slides and demos have to be dead on. The presenter has to engage the audience and not just deliver a recital from the yellow pages. If the presenter is exited and energetic then the message usually comes across much clearer.

Techniques like knowing how to use magnifier and larger fonts in Visual Studio is just a prerequisite. Unfortunately I experienced several presenters that didn’t use these basic tools and settings which made it impossible to see what was presented on the screens.

Overall I would have to say that the level of presentation skill and talent was lower this year than before. So this is a must for the next conference.

This is a no-brainer for anyone delivering presentations anywhere. I never never go to a customer and rely on wireless connectivity. Wifi and 3/4G hotspots are just to flakey. Next time please make sure that presentations use wired networking so that demos doesn’t fail because of network congestion.

Use SharePoint to organize and deliver the conference

Not using SharePoint to deliver the conference and engage the attendees is simply a mind blower for me. How cool would it be to use SharePoint to collaborate on the sessions – see and try the demos for your self in real time. To get instant access to the code and slides. To interact with the other attendees using social and other features. Use Yammer to setup a private network for the conference – I could have done that in 2 minutes.

So next time – use SharePoint to organize and deliver the conference.

Functions for calculated fields in SharePoint

2012-11-07 / admin / Kommentarer lukket

ABS function	Article Returns the absolute value of a number. The absolute value of a number is the number without its sign. Syntax ABS ( number ) Number is the real number of whic…
ACOS function	Article Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number . The returned angle is given in radians in the range …
ACOSH function	Article Returns the inverse hyperbolic cosine of a number. Number must be greater than or equal to 1. The inverse hyperbolic cosine is the value whose hyperbolic cosi…
AND function	Article Returns the logical value TRUE if all of the arguments are TRUE; returns FALSE if one or more arguments is FALSE. Syntax AND ( logical1 , logical2 , … ) Logic…
ASC function	Article For Double-byte character set (DBCS) languages, changes full-width (double-byte) characters to half-width (single-byte) characters. Syntax ASC ( text ) Text …
ASIN function	Article Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number . The returned angle is given in radians in the range -pi/2 to…
ASINH function	Article Returns the inverse hyperbolic sine of a number. The inverse hyperbolic sine is the value whose hyperbolic sine is number , so ASINH(SINH(number)) equals numb…
ATAN function	Article Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the rang…
ATAN2 function	Article Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates. The arctangent is the angle from the x-axis to a line containing the origin (…
ATANH function	Article Returns the inverse hyperbolic tangent of a number. Number must be between -1 and 1 (excluding -1 and 1). The inverse hyperbolic tangent is the value whose hy…
AVEDEV function	Article Returns the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set. Syntax AVEDEV ( number1 ,nu…
AVERAGE function	Article Returns the average (arithmetic mean) of the arguments. Syntax AVERAGE ( number1 , number2 , … ) Number1, number2, … are 1 to 30 numeric arguments for wh…
AVERAGEA function	Article Calculates the average (arithmetic mean) of the values in the list of arguments. Unlike the AVERAGE functions, text and logical values such as TRUE and FALSE ar…
BETADIST function	Article Returns the cumulative beta probability distribution function. The cumulative beta probability distribution function is commonly used to study variation in the …
BETAINV function	Article Returns the inverse of the cumulative beta probability distribution function. That is, if probability = BETADIST(x,…), then BETAINV(probability,…) = x. The …
BINOMDIST function	Article Returns the individual term binomial distribution probability. Use BINOMDIST in problems with a fixed number of tests or trials, when the outcomes of any trial …
CEILING function	Article Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your prod…
CHAR function	Article Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters. Synta…
CHIDIST function	Article Returns the one-tailed probability of the chi-squared distribution. The ?2 distribution is associated with a ?2 test. Use the ?2 test to compare observed and ex…
CHIINV function	Article Returns the inverse of the one-tailed probability of the chi-squared distribution. If probability = CHIDIST(x,…), then CHIINV(probability,…) = x. Use this f…
CHOOSE function	Article Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 29 values based on the index number. For example, if value1…
CLEAN function	Article Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operatin…
CODE function	Article Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer. Syntax CODE ( text ) …
CONCATENATE function	Article In Microsoft SharePoint Services, use the CONCATENATE function to join several text strings into one string.
CONFIDENCE function	Article Returns the confidence interval for a population mean with a normal distribution. The confidence interval is a range on either side of a sample mean. For exampl…
COS function	Article Returns the cosine of the given angle. Syntax COS ( number ) Number is the angle in radians for which you want the cosine. Remark If the angle is in degrees, …
COSH function	Article Returns the hyperbolic cosine of a number. Syntax COSH ( number ) Number is any real number for which you want to find the hyperbolic cosine. Remark The formu…
COUNT function	Article Counts the number of arguments that contain numbers. Syntax COUNT ( value1 , value2 , … ) Value1, value2, … are 1 to 30 arguments that can contain or refe…
COUNTA function	Article Counts the number of arguments that are not empty. Syntax COUNTA ( value1 , value2 , … ) Value1, value2, … are 1 to 30 arguments representing the values …
CRITBINOM function	Article Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. Use this function for quality assurance…
DATE function	Article Returns the sequential serial number that represents a particular date. Syntax DATE ( year , month , day ) Year The year argument can be one to four digits. …
DATEDIF function	Article Calculates the number of days, months, or years between two dates. This function is provided for compatibility with Lotus 1-2-3. Syntax DATEDIF ( start_date , e…
DATEVALUE function	Article Returns the serial number of the date represented by date_text. Use DATEVALUE to convert a date represented by text to a serial number. Syntax DATEVALUE ( date_…
DAY function	Article Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31. Syntax DAY ( serial_number ) Serial_number is …
DAYS360 function	Article Returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to…
DDB function	Article Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify. Syntax DDB ( cost , salv…
DEGREES function	Article Converts radians into degrees. Syntax DEGREES ( angle ) Angle is the angle in radians that you want to convert. Example Formula Description (Result) =DEGREES(…
DEVSQ function	Article Returns the sum of squares of deviations of data points from their sample mean. Syntax DEVSQ ( number1 , number2 , … ) Number1,number2, … are 1 to 30 argu…
DOLLAR function	Article Converts a number to text using currency format, with the decimals rounded to the specified place. The format used is $#,##0.00_);($#,##0.00). Syntax DOLLAR ( n…
EVEN function	Article Returns number rounded up to the nearest even integer. You can use this function for processing items that come in twos. For example, a packing crate accepts ro…
EXACT function	Article Compares two text strings and returns the logical value TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive. Use EXACT to test text bein…
EXP function	Article Returns e raised to the power of number. The constant e equals 2.71828182845904, the base of the natural logarithm. Syntax EXP ( number ) Number is the expone…
EXPONDIST function	Article Returns the exponential distribution. Use EXPONDIST to model the time between events, such as how long an automated bank teller takes to deliver cash. For examp…
FACT function	Article Returns the factorial of a number. The factorial of a number is equal to 123… number. Syntax FACT ( number ) Number is the nonnegative number you want t…
FALSE function	Article Returns the logical value FALSE. Syntax FALSE ( ) Remark You can also type the word FALSE directly into the formula, and it is interpreted as the logical valu…
FDIST function	Article Returns the F probability distribution. You can use this function to determine whether two data sets have different degrees of diversity. For example, you can e…
FIND function	Article Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first charact…
FINV function	Article Returns the inverse of the F probability distribution. If p = FDIST(x,…), then FINV(p,…) = x. The F distribution can be used in an F-test that compares the …
FISHER function	Article Returns the Fisher transformation at x. This transformation produces a function that is approximately normally distributed rather than skewed. Use this function…
FISHERINV function	Article Returns the inverse of the Fisher transformation. Use this transformation when analyzing correlations between ranges or arrays of data. If y = FISHER(x), then F…
FIXED function	Article Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text. Syntax FIXE…
FLOOR function	Article Rounds number down, toward zero, to the nearest multiple of significance. Syntax FLOOR ( number , significance ) Number is the numeric value you want to round…
FV function	Article Returns the future value of an investment based on periodic, constant payments and a constant interest rate. Syntax FV ( rate , nper , pmt , pv , type ) For a m…
GAMMADIST function	Article Returns the gamma distribution. You can use this function to study variables that may have a skewed distribution. The gamma distribution is commonly used in que…
GAMMAINV function	Article Returns the inverse of the gamma cumulative distribution. If p = GAMMADIST(x,…), then GAMMAINV(p,…) = x. You can use this function to study a variable whose…
GAMMALN function	Article Returns the natural logarithm of the gamma function, ?(x). Syntax GAMMALN ( x ) X is the value for which you want to calculate GAMMALN. Remarks If x is nonnum…
GEOMEAN function	Article Returns the geometric mean of positive data. For example, you can use GEOMEAN to calculate average growth rate given compound interest with variable rates. Synt…
HARMEAN function	Article Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals. Syntax HARMEAN ( number1 , number2 , … ) N…
HOUR function	Article Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.). Syntax HOUR ( serial_number ) Serial_number …
HYPGEOMDIST function	Article Returns the hypergeometric distribution. HYPGEOMDIST returns the probability of a given number of sample successes, given the sample size, population successes,…
IF function	Article Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. Use IF to conduct conditional tests on values and for…
INT function	Article Rounds a number down to the nearest integer. Syntax INT ( number ) Number is the real number you want to round down to an integer. Example 1 Formula Descripti…
IPMT function	Article Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. For a more complete descrip…
IS functions	Article This article describes the eight functions used for testing the type of a value or column reference. Each of these functions, referred to collectively as the I…
ISPMT function	Article Calculates the interest paid during a specific period of an investment. Syntax ISPMT ( rate , per , nper , pv ) Rate is the interest rate for the investment….
KURT function	Article Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness or flatness of a distribution compared with the normal distribution. Positive…
LEFT function	Article LEFT returns the first character or characters in a text string, based on the number of characters you specify. Syntax LEFT ( text , num_chars ) Text is the t…
LEN function	Article LEN returns the number of characters in a text string. Syntax LEN ( text ) Text is the text whose length you want to find. Spaces count as characters. Example…
LN function	Article Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904). Syntax LN ( number ) Number is the positive real…
LOG function	Article Returns the logarithm of a number to the base you specify. Syntax LOG ( number , base ) Number is the positive real number for which you want the logarithm. B…
LOG10 function	Article Returns the base-10 logarithm of a number. Syntax LOG10 ( number ) Number is the positive real number for which you want the base-10 logarithm. Examples Formu…
LOGINV function	Article Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard_dev. If p = LO…
LOGNORMDIST function	Article Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with the parameters mean and standard_dev. Use this function to analyze …
LOWER function	Article Converts all uppercase letters in a text string to lowercase. Syntax LOWER ( text ) Text is the text you want to convert to lowercase. LOWER does not change c…
MAX function	Article Returns the largest value in a set of values. Syntax MAX ( number1 , number2 , … ) Number1,number2,… are 1 to 30 numbers for which you want to find the ma…
MAXA function	Article Returns the largest value in a list of arguments. Text and logical values such as TRUE and FALSE are compared as well as numbers. MAXA is similar to MINA. For m…
Me function	Article Returns the current user name. Syntax Me Remark The Me function can be used only with default values; you cannot use the Me function in a calculated column.
MEDIAN function	Article Returns the median of the given numbers. The median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than…
MID function	Article MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. Syntax MID …
MIN function	Article Returns the smallest number in a set of values. Syntax MIN ( number1 , number2 , … ) Number1, number2,… are 1 to 30 numbers for which you want to find the…
MINA function	Article Returns the smallest value in the list of arguments. Text and logical values such as TRUE and FALSE are compared as well as numbers. Syntax MINA ( value1 , valu…
MINUTE function	Article Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59. Syntax MINUTE ( serial_number ) Serial_number is the time that …
MOD function	Article Returns the remainder after number is divided by divisor. The result has the same sign as divisor. Syntax MOD ( number , divisor ) Number is the number for wh…
MODE function	Article Returns the most frequently occurring, or repetitive, value in the argument list. Like MEDIAN, MODE is a location measure. Syntax MODE ( number1 , number2 , ……
MONTH function	Article Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December). Syntax MONTH ( serial_n…
NEGBINOMDIST function	Article Returns the negative binomial distribution. NEGBINOMDIST returns the probability that there will be number_f failures before the number_s-th success, when the c…
NORMDIST function	Article Returns the normal distribution for the specified mean and standard deviation. This function has a very wide range of applications in statistics, including hypo…
NORMINV function	Article Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. Syntax NORMINV ( probability , mean , standard_dev ) Pr…
NORMSDIST function	Article Returns the standard normal cumulative distribution function. The distribution has a mean of 0 (zero) and a standard deviation of one. Use this function in plac…
NORMSINV function	Article Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one. Syntax NORMSINV ( proba…
NOT function	Article Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value. Syntax NOT ( logical ) Logical is a value…
NPER function	Article Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate. Syntax NPER ( rate , pmt , pv , fv , type ) F…
NPV function	Article Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). Synta…
ODD function	Article Returns number rounded up to the nearest odd integer. Syntax ODD ( number ) Number is the value to round. Remarks If number is nonnumeric, ODD returns the #VA…
OR function	Article Returns Yes if any argument is TRUE; returns No if all arguments are FALSE. Syntax OR ( logical1 , logical2 , … ) Logical1, logical2,… are 1 to 30 conditi…
PI function	Article Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits. Syntax PI () Examples Formula Description (Result) =PI() Pi (3.1415926…
PMT function	Article Calculates the payment for a loan based on constant payments and a constant interest rate. Syntax PMT ( rate , nper , pv , fv , type ) For a more complete descr…
POISSON function	Article Returns the Poisson distribution. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number o…
POWER function	Article Returns the result of a number raised to a power. Syntax POWER ( number , power ) Number is the base number. It can be any real number. Power is the exponen…
PPMT function	Article Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate. Syntax PPMT ( rate …
PRODUCT function	Article Multiplies all the numbers given as arguments and returns the product. Syntax PRODUCT ( number1 , number2 , … ) Number1, number2,… are 1 to 30 numbers tha…
PROPER function	Article Capitalizes the first letter and any other letters that follow a non-letter character in a text string. Converts all other letters in the text string to lowerca…
PV function	Article Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow mo…
RADIANS function	Article Converts degrees to radians. Syntax RADIANS ( angle ) Angle is an angle in degrees that you want to convert. Example Formula Description (Result) =RADIANS(270…
RATE function	Article Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do no…
REPLACE function	Article REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string. Syntax REPLACE ( old_text , start_num , num…
REPT function	Article Repeats text a given number of times. Use REPT to add a number of instances of a text string. Syntax REPT ( text , number_times ) Text is the text you want to…
RIGHT function	Article RIGHT returns the last characters in a text string, based on the number of characters you specify. Syntax RIGHT ( text , num_chars ) Text is the text string c…
ROMAN function	Article Converts an arabic numeral to roman, as text. Syntax ROMAN ( number , form ) Number is the arabic numeral you want converted. Form is a number specifying t…
ROUND function	Article Rounds a number to a specified number of digits. Syntax ROUND ( number , num_digits ) Number Is the number you want to round. Num_digits Specifies the number …
ROUNDDOWN function	Article Rounds a number down, toward zero. Syntax ROUNDDOWN ( number , num_digits ) Number is any real number that you want rounded down. Num_digits is the number o…
ROUNDUP function	Article Rounds a number up, away from 0 (zero). Syntax ROUNDUP ( number , num_digits ) Number is any real number that you want rounded up. Num_digits is the number …
SEARCH function	Article SEARCH returns the number of the character at which a specific character or text string is first found, beginning with start_num. Use SEARCH to determine the lo…
SECOND function	Article Returns the seconds of a time value. The second is given as an integer in the range 0 (zero) to 59. Syntax SECOND ( serial_number ) Serial_number is the time…
SIGN function	Article Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative. Syntax SIGN ( number ) Numb…
SIN function	Article Returns the sine of the given angle. Syntax SIN ( number ) Number is the angle in radians for which you want the sine. Remark If your argument is in degrees, …
SINH function	Article Returns the hyperbolic sine of a number. Syntax SINH ( number ) Number is any real number. Remark The formula for the hyperbolic sine is: Example set 1 Formul…
SKEW function	Article Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean. Positive skewness indicates a distribu…
SLN function	Article Returns the straight-line depreciation of an asset for one period. Syntax SLN ( cost , salvage , life ) Cost is the initial cost of the asset. Salvage is t…
SQRT function	Article Returns a positive square root. Syntax SQRT ( number ) Number is the number for which you want the square root. Remark If number is negative, SQRT returns the…
STANDARDIZE function	Article Returns a normalized value from a distribution characterized by mean and standard_dev. Syntax STANDARDIZE ( x , mean , standard_dev ) X is the value you want …
STDEV function	Article Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). Syntax…
STDEVA function	Article Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). Text a…
STDEVP function	Article Calculates standard deviation based on the entire population given as arguments. The standard deviation is a measure of how widely values are dispersed from the…
STDEVPA function	Article Calculates standard deviation based on the entire population given as arguments, including text and logical values. The standard deviation is a measure of how w…
SUM function	Article Adds all the numbers in the specified arguments. Syntax SUM ( number1 , number2 , … ) Number1, number2,… are 1 to 30 arguments for which you want the tota…
SUMSQ function	Article Returns the sum of the squares of the arguments. Syntax SUMSQ ( number1 , number2 , … ) Number1, number2,… are 1 to 30 arguments for which you want the su…
SYD function	Article Returns the sum-of-years’ digits depreciation of an asset for a specified period. Syntax SYD ( cost , salvage , life , per ) Cost is the initial cost of the a…
T function	Article Returns the text referred to by value. Syntax T ( value ) Value is the value you want to test. Remarks If value is or refers to text, T returns value. If valu…
TAN function	Article Returns the tangent of the given angle. Syntax TAN ( number ) Number is the angle in radians for which you want the tangent. Remark If your argument is in deg…
TANH function	Article Returns the hyperbolic tangent of a number. Syntax TANH ( number ) Number is any real number. Remark The formula for the hyperbolic tangent is: Examples Formu…
TDIST function	Article Returns the Percentage Points (probability) for the Student t-distribution where a numeric value (x) is a calculated value of t for which the Percentage Points …
TEXT function	Article Converts a value to text in a specific number format. Syntax TEXT ( value , format_text ) Value is a numeric value, a formula that evaluates to a numeric valu…
TIME function	Article Returns the decimal number for a particular time. The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99999999, representing the times fro…
TIMEVALUE function	Article Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times f…
TINV function	Article Returns the t-value of the Student’s t-distribution as a function of the probability and the degrees of freedom. Syntax TINV ( probability , degrees_freedom ) P…
TODAY function	Article Returns the serial number of the current date. The serial number is the date-time code used for date and time calculations. You can use the TODAY function only …
TRIM function	Article Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular sp…
TRUE function	Article Returns the logical value TRUE. Syntax TRUE () Remark You can enter the value TRUE directly without using this function. The TRUE function is provided primarily…
TRUNC function	Article Truncates a number to an integer by removing the fractional part of the number. Syntax TRUNC ( number , num_digits ) Number is the number you want to truncate…
UPPER function	Article Converts text to uppercase. Syntax UPPER ( text ) Text is the text you want converted to uppercase. Text can be a column reference or text string. Example For…
USDOLLAR function	Article Converts a number to text using currency format, with the decimals rounded to the specified place. The format used is $#,##0.00_);($#,##0.00). Syntax USDOLLAR (…
VALUE function	Article Converts a text string that represents a number to a number. Syntax VALUE ( text ) Text is the text enclosed in quotation marks or a column reference containi…
VAR function	Article Estimates variance based on a sample. Syntax VAR ( number1 , number2 , … ) Number1,number2,… are 1 to 30 number arguments corresponding to a sample of a p…
VARA function	Article Estimates variance based on a sample. In addition to numbers, text and logical values such as TRUE and FALSE are included in the calculation. Syntax VARA ( valu…
VARP function	Article Calculates variance based on the entire population. Syntax VARP ( number1 , number2 , … ) Number1,number2, … are 1 to 30 number arguments corresponding t…
VARPA function	Article Calculates variance based on the entire population. In addition to numbers, text and logical values such as TRUE and FALSE are included in the calculation. Synt…
WEEKDAY function	Article Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default. Syntax WEEKDAY ( seria…
WEIBULL function	Article Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device’s mean time to failure. Syntax WEIBULL ( x , alpha…
YEAR function	Article Returns the year corresponding to a date. Syntax YEAR ( serial_number ) Serial_number is the date of the year you want to find. Remarks Dates are stored as se…