Tag: Behviourial Science Bangalore University BBA Notes

AVERAGEIF(range, criteria, [average_range])

The AVERAGEIF function syntax has the following arguments:

• Range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.
• Criteria Required. The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, or B4.
• Average_range Optional. The actual set of cells to average. If omitted, range is used.

Remarks

• Cells in range that contain TRUE or FALSE are ignored.
• If a cell in average_range is an empty cell, AVERAGEIF ignores it.
• If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
• If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
• If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.
• You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
• Average_range does not have to be the same size and shape as range. The actual cells that are averaged are determined by using the top, left cell in average_range as the beginning cell, and then including cells that correspond in size and shape to range. For example:

Average: Which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.

Median: Which is the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.

Mode: Which is the most frequently occurring number in a group of numbers. For example, the mode of 2, 3, 3, 5, 7, and 10 is 3.

Example

The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect.

So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

For example, =IF(C2=” Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).

Common problems

SUMIF(range, criteria, [sum_range])

The SUMIF function syntax has the following arguments:

range   Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format (examples below).

Criteria Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. Wildcard characters can be included – a question mark (?) to match any single character, an asterisk (*) to match any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

For example, criteria can be expressed as 32, “>32”, B5, “3?”, “apple*”, “*~?”, or TODAY().

sum_range   Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

Sum_range should be the same size and shape as range. If it isn’t, performance may suffer, and the formula will sum a range of cells that starts with the first cell in sum_range but has the same dimensions as range. For example:

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

In its simplest form, COUNTIF says:

=COUNTIF(Where do you want to look?, What do you want to look for?)

For example:

• =COUNTIF(A2:A5,”London”)
• =COUNTIF(A2:A5,A4)

To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.

Auto completion of series

Enter the first number in the top cell. Hover until you see the Fill Handle and with the RIGHT mouse button, drag the Fill Handle until you have selected the cells to autofill.

Autofilling Via the Excel Ribbon

The Series window can be reached via the ribbon in Excel version 2007 and newer.

• Enter the first number of the series. Then select that cell and the cells that you want to auto fill.
• Click the Fill button located on the Editing section of the ribbon’s Home tab as shown in the first image.
• A drop-down menu appears as shown in the second image. Click Series and the Series window appears as shown below.
• Enter your step value and any other values on the Series window and click OK.

Through Drag

• Select one or more cells you want to use as a basis for filling additional cells.
• For a series like 1, 2, 3, 4, 5…, type 1 and 2 in the first two cells. For the series 2, 4, 6, 8…, type 2 and 4.
• For the series 2, 2, 2, 2…, type 2 in first cell only.
• Drag the fill handle Fill handle.
• If needed, click Auto Fill Options Button image and choose the option you want.

Sort and filter

Sorting data is an integral part of data analysis. You might want to arrange a list of names in alphabetical order, compile a list of product inventory levels from highest to lowest, or order rows by colors or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by a custom list you create (such as Large, Medium, and Small) or by format, including cell color, font color, or icon set.

Sort text

1. Select a cell in the column you want to sort.
2. On the Data tab, in the Sort & Filter group, do one of the following:

• To quick sort in ascending order, click A to Z command in Excel that sorts A to Z or smallest number to largest (Sort A to Z).
• To quick sort in descending order, click Z to A command in Excel that sorts Z to A or largest number to smallest (Sort Z to A).

Filtering Data

The filter feature applies a drop-down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company.

To do this using the filter you would:

• Go to the Data tab on Excel ribbon
• Select the Filter tool
• Select Eastern Company from the dropdown menu
• Select Dylan Rogers from the Salesperson dropdown menu

Charts

Create a chart

• Select the data for which you want to create a chart.
• Click INSERT > Recommended Charts.
• On the Recommended Charts tab, scroll through the list of charts that Excel recommends for your data, and click any chart to see how your data will look.
• If you don’t see a chart you like, click All Charts to see all the available chart types.
• When you find the chart you like, click it > OK.
• Use the Chart Elements, Chart Styles, and Chart Filters buttons, next to the upper-right corner of the chart to add chart elements like axis titles or data labels, customize the look of your chart, or change the data that is shown in the chart.
• To access additional design and formatting features, click anywhere in the chart to add the CHART TOOLS to the ribbon, and then click the options you want on the DESIGN and FORMAT tabs.

Formulas are the backbone of most advanced calculations within Excel. Think of a formula as an equation; it’s a series of steps that lead to a final output. You can tell Excel that you’re entering a formula in Excel by starting your input with the equals sign (=). For example, try entering the following into a cell:

Formulas in Excel use the standard mathematical order of operations to evaluate. As such, operations within formulas are processed in this order:

• Parentheses
• Exponents (^)
• Multiplication (*)
• Division (/)
• Addition (+)
• Subtraction (-)

All Excel formulas begin with an equal sign (=).

After the equal symbol, you enter either a calculation or function. For example, to add up values in cells B1 through B5, you can either:

• Type the entire equation: =B1+B2+B3+B4+B5
• Use the SUM function: =SUM(B1:B5)

Press the Enter key to complete the formula. Done.

Elements of Microsoft Excel formulas

When you make a formula in Excel, you can use different elements to supply the source data to the formula and indicate what operators should be performed on those data. Depending on the formula type that you create, it can include any or all of the following parts:

Constants: numbers or text values that you enter directly in a formula, like =2*3.

Cell references: reference to a cell containing the value you want to use in your Excel formula, e.g. =SUM(A1, A2, B5).

To refer to data in two or more contiguous cells, use a range reference like A1:A5. For example, to sum values in all cell between A1 and A5, inclusive, use this formula:

=SUM(A1:A5).

Names: defined name for a cell range, constant, table, or function, for example =SUM(my_name).

Functions: predefined formulas in Excel that perform calculations using the values supplied in their arguments.

Operators: special symbols that specify the type of operation or calculation to be performed.

Special paste

Paste menu options

• Select the cells that contain the data or other attributes that you want to copy.
• On the Home tab, click Copy Copy icon.
• Click the first cell in the area where you want to paste what you copied.
• On the Home tab, click the arrow next to Paste, and then do any of the following.

Paste options

Operation options

The Operation options mathematically combine values between the copy and paste areas.

Freeze pane

To keep an area of a worksheet visible while you scroll to another area of the worksheet, go to the View tab, where you can Freeze Panes to lock specific rows and columns in place, or you can Split panes to create separate windows of the same worksheet.

Freeze the first column

• Select View > Freeze Panes > Freeze First Column.

            The faint line that appears between Column A and B shows that the first column is frozen.

Freeze the first two columns

1. Select the third column.
2. Select View >Freeze Panes > Freeze Panes.

Freeze columns and rows

1. Select the cell below the rows and to the right of the columns you want to keep visible when you scroll.
2. Select View > Freeze Panes> Freeze Panes.

Unfreeze rows or columns

• On the View tab > Window > Unfreeze Panes.

Formatting text or numbers can make them appear more visible especially when you have a large worksheet. Changing default formats includes things like changing the font colour, style, size, text alignment in a cell, or apply formatting effects.

All cell content uses the same formatting by default, which can make it difficult to read a workbook with a lot of information. Basic formatting can customize the look and feel of your workbook, allowing you to draw attention to specific sections and making your content easier to view and understand. You can also apply number formatting to tell Excel exactly what type of data you’re using in the workbook, such as percentages (%), currency ($), and so on.

If you want text or numbers in a cell to appear bold, italic, or have a single or double underline, select the cell and on the Home tab, pick the format you want:

Change font style, size, color, or apply effects

Click Home and:

• For a different font style, click the arrow next to the default font Calibri and pick the style you want.
• To increase or decrease the font size, click the arrow next to the default size 11 and pick another text size.

The Internet (or internet) is the global system of interconnected computer networks that uses the Internet protocol suite (TCP/IP) to communicate between networks and devices. It is a network of networks that consists of private, public, academic, business, and government networks of local to global scope, linked by a broad array of electronic, wireless, and optical networking technologies. The Internet carries a vast range of information resources and services, such as the inter-linked hypertext documents and applications of the World Wide Web (WWW), electronic mail, telephony, and file sharing.

The origins of the Internet date back to the development of packet switching and research commissioned by the United States Department of Defense in the 1960s to enable time-sharing of computers. The primary precursor network, the ARPANET, initially served as a backbone for interconnection of regional academic and military networks in the 1970s. The funding of the National Science Foundation Network as a new backbone in the 1980s, as well as private funding for other commercial extensions, led to worldwide participation in the development of new networking technologies, and the merger of many networks. The linking of commercial networks and enterprises by the early 1990s marked the beginning of the transition to the modern Internet, and generated a sustained exponential growth as generations of institutional, personal, and mobile computers were connected to the network. Although the Internet was widely used by academia in the 1980s, commercialization incorporated its services and technologies into virtually every aspect of modern life.

Most traditional communication media, including telephony, radio, television, paper mail and newspapers are reshaped, redefined, or even bypassed by the Internet, giving birth to new services such as email, Internet telephony, Internet television, online music, digital newspapers, and video streaming websites. Newspaper, book, and other print publishing are adapting to website technology, or are reshaped into blogging, web feeds and online news aggregators. The Internet has enabled and accelerated new forms of personal interactions through instant messaging, Internet forums, and social networking services. Online shopping has grown exponentially for major retailers, small businesses, and entrepreneurs, as it enables firms to extend their “brick and mortar” presence to serve a larger market or even sell goods and services entirely online. Business-to-business and financial services on the Internet affect supply chains across entire industries.

The Internet has no single centralized governance in either technological implementation or policies for access and usage; each constituent network sets its own policies. The overreaching definitions of the two principal name spaces in the Internet, the Internet Protocol address (IP address) space and the Domain Name System (DNS), are directed by a maintainer organization, the Internet Corporation for Assigned Names and Numbers (ICANN). The technical underpinning and standardization of the core protocols is an activity of the Internet Engineering Task Force (IETF), a non-profit organization of loosely affiliated international participants that anyone may associate with by contributing technical expertise. In November 2006, the Internet was included on USA Today’s list of New Seven Wonders.

The word internetted was used as early as 1849, meaning interconnected or interwoven. The word Internet was used in 1974 as the shorthand form of Internetwork. Today, the term Internet most commonly refers to the global system of interconnected computer networks, though it may also refer to any group of smaller networks.

When it came into common use, most publications treated the word Internet as a capitalized proper noun; this has become less common. This reflects the tendency in English to capitalize new terms and move to lowercase as they become familiar. The word is sometimes still capitalized to distinguish the global internet from smaller networks, though many publications, including the AP Stylebook since 2016, recommend the lowercase form in every case. In 2016, the Oxford English Dictionary found that, based on a study of around 2.5 billion printed and online sources, “Internet” was capitalized in 54% of cases.

The terms Internet and World Wide Web are often used interchangeably; it is common to speak of “going on the Internet” when using a web browser to view web pages. However, the World Wide Web or the Web is only one of a large number of Internet services, a collection of documents (web pages) and other web resources, linked by hyperlinks and URLs.

Surfing the web

Alternatively known as web surfing, surfing with computers describes the act of browsing the Internet by going from one web page to another web page using hyperlinks in an Internet browser. The term “surfing” was first coined by Mark McCahill.

The term comes from “TV channel surfing,” but instead of clicking on the buttons of the remote, the user jumps from page to page by clicking on the links in the Web page.

Surfing is an activity that started with the advent of the World Wide Web. With hypertext links, users can go not only from one part of a document to another, but also from one document to another, including those located in remote sites. Surfing is a favorite pastime for millions of people around the world who have access to the Internet. Many users are hooked on it, spending countless hours doing casual searches or other online activities. Some do it to kill time.

Online aggregation services allow you to surf the Internet. Some of the most popular places include Reddit, StumbleUpon, and Pinterest. Also, you can use a search engine, such as Google, to search for anything that interests you.

HTTP

HTTP is the backbone of the World Wide Web (WWW). It defines the format of messages through which Web Browsers (like Firefox, Chrome) and Web Servers communicate, whilst also defining how a web browser should respond to a particular web browser request.

The Hypertext Transfer Protocol (HTTP) is an application layer protocol in the Internet protocol suite model for distributed, collaborative, hypermedia information systems. HTTP is the foundation of data communication for the World Wide Web, where hypertext documents include hyperlinks to other resources that the user can easily access, for example by a mouse click or by tapping the screen in a web browser.

Development of HTTP was initiated by Tim Berners-Lee at CERN in 1989 and summarized in a simple document describing the behavior of a client and a server using the first HTTP protocol version that was named 0.9.

That first version of HTTP protocol soon evolved into a more elaborated version that was the first draft toward a far future version 1.0.

Development of early HTTP Requests for Comments (RFCs) started a few years later and it was a coordinated effort by the Internet Engineering Task Force (IETF) and the World Wide Web Consortium (W3C), with work later moving to the IETF.

HTTP/1 was finalized and fully documented (as version 1.0) in 1996. It evolved (as version 1.1) in 1997 and then its specifications were updated in 1999 and in 2014.

Its secure variant named HTTPS is used by more than 76% of websites.

HTTP/2 is a more efficient expression of HTTP’s semantics “on the wire”, and was published in 2015; it is used by more than 45% of websites; it is now supported by almost all web browsers (96% of users) and major web servers over Transport Layer Security (TLS) using an Application-Layer Protocol Negotiation (ALPN) extension where TLS 1.2 or newer is required.

HTTP/3 is the proposed successor to HTTP/2; it is used by more than 20% of websites; it is now supported by many web browsers (73% of users). HTTP/3 uses QUIC instead of TCP for the underlying transport protocol. Like HTTP/2, it does not obsolete previous major versions of the protocol. Support for HTTP/3 was added to Cloudflare and Google Chrome first, and is also enabled in Firefox.

HTTPS

HTTPS is for Web sites using additional security features such as certificates. HTTPS is used when Web transactions are required to be secure. HTTPS uses a certificate-based technology such as VeriSign.

Certificate-based transactions offer mutual authentication between the client and the server. Mutual authentication ensures the server of the client identity and ensures the client of the server identity. HTTPS, in addition to using certificate-based authentication, encrypts all data packets sent during a session.

Hypertext Transfer Protocol Secure (HTTPS) is an extension of the Hypertext Transfer Protocol (HTTP). It is used for secure communication over a computer network, and is widely used on the Internet. In HTTPS, the communication protocol is encrypted using Transport Layer Security (TLS) or, formerly, Secure Sockets Layer (SSL). The protocol is therefore also referred to as HTTP over TLS, or HTTP over SSL.

The principal motivations for HTTPS are authentication of the accessed website, and protection of the privacy and integrity of the exchanged data while in transit. It protects against man-in-the-middle attacks, and the bidirectional encryption of communications between a client and server protects the communications against eavesdropping and tampering. The authentication aspect of HTTPS requires a trusted third party to sign server-side digital certificates. This was historically an expensive operation, which meant fully authenticated HTTPS connections were usually found only on secured payment transaction services and other secured corporate information systems on the World Wide Web. In 2016, a campaign by the Electronic Frontier Foundation with the support of web browser developers led to the protocol becoming more prevalent. HTTPS is now used more often by web users than the original non-secure HTTP, primarily to protect page authenticity on all types of websites; secure accounts; and to keep user communications, identity, and web browsing private.

FTP

The File Transfer Protocol (FTP) is a standard communication protocol used for the transfer of computer files from a server to a client on a computer network. FTP is built on a client–server model architecture using separate control and data connections between the client and the server. FTP users may authenticate themselves with a clear-text sign-in protocol, normally in the form of a username and password, but can connect anonymously if the server is configured to allow it. For secure transmission that protects the username and password, and encrypts the content, FTP is often secured with SSL/TLS (FTPS) or replaced with SSH File Transfer Protocol (SFTP).

The first FTP client applications were command-line programs developed before operating systems had graphical user interfaces, and are still shipped with most Windows, Unix, and Linux operating systems. Many FTP clients and automation utilities have since been developed for desktops, servers, mobile devices, and hardware, and FTP has been incorporated into productivity applications, such as HTML editors.

In January 2021, support for the FTP protocol was disabled in Google Chrome 88, and disabled in Firefox 88.0. In July 2021, Firefox 90 dropped FTP entirely, and Google followed suit in October 2021, removing FTP entirely in Google Chrome 95.

Hotspot

Wifi Hotspots are sites that offer Internet access over a wireless local area network (WLAN) by way of a router that then connects to an Internet service provider. Hotspots utilize WiFi technology, which allows electronic devices to connect to the Internet or exchange data wirelessly through radio waves. Hotspots can be phone-based or free-standing, commercial or free to the public.

Wi-Fi

Wi-Fi is the radio signal sent from a wireless router to a nearby device, which translates the signal into data you can see and use. The device transmits a radio signal back to the router, which connects to the internet by wire or cable.

A WiFi network is simply an internet connection that’s shared with multiple devices in a home or business via a wireless router. The router is connected directly to your internet modem and acts as a hub to broadcast the internet signal to all your Wi-Fi enabled devices. This gives you flexibility to stay connected to the internet as long as you’re within your network coverage area.

Wi-Fi uses radio waves to transmit data from your wireless router to your Wi-Fi enabled devices like your TV, smartphone, tablet and computer. Because they communicate with each other over airwaves, your devices and personal information can become vulnerable to hackers, cyber-attacks and other threats. This is especially true when you connect to a public Wi-Fi network at places like a coffee shop or airport. When possible, it’s best to connect to a wireless network that is password-protected or a personal hotspot.

LAN Cable

Cable Internet connection is a form of broadband access. Through use of a cable modem, users can access the Internet over cable TV lines. Cable modems can provide extremely fast access to the Internet, making a cable connection a viable option for many.

Broadband

This high-speed Internet connection is provided through either cable or telephone companies. One of the fastest options available, broadband Internet uses multiple data channels to send large quantities of information. The term broadband is shorthand for broad bandwidth. Broadband Internet connections such as DSL and cable are considered high-bandwidth connections. Although many DSL connections can be considered broadband, not all broadband connections are DSL.

USB Tethering

When the mobile internet connection is shared with a connected computer or laptop using physical USB data cable then it is called USB Tethering. It gives a high-speed connection while sharing the internet between mobile devices and connected computer systems, it is because Wired connection is quite stronger than Wireless LAN connection.

Advantages of USB Tethering.

• It does not lower the battery life of mobile, as the mobile gets charged while sharing the internet.
• The computer does not need to have Wi-Fi (Wireless LAN) functionality for USB Tethering.
• The internet speed obtained in the connected computer is quite faster because of the strong connection build by USB data cable.
• Also, If mobile does not have Hotspot functionality, still USB Tethering can work for it.