Posted on Leave a comment

Data Entry & Management in Excel

One of the main uses of Microsoft Excel is for data entry, maintenance and monitoring. Data in Excel can be organized in columns, as a table, or in a pie chart or graph.

Right now, we’ll be focusing on the basics of data entry and management in a worksheet. Data here is entered in a table that consists of rows and columns.

The top rows of tables have headings that go down to the left and are used to identify the data being entered in the tables. This lets Excel do any necessary calculations through the use of formulas. The program definitely makes it easier to manage and process data.

To make it even easier, Excel also provides a number of ways to formatting the data. If you want to locate and review some information, you’ll appreciate these data formatting features. They can be used for data formatting in individual cells, different columns and rows, and for formatting entire tables.

In previous versions of Excel each worksheet contained a billion cells, while the most recent updates released by Microsoft have almost seventeen billion cells.

To make it easier to access and reference these multitudinous cells, each of them has been given an address. This is called a cell reference.

Posted on 1 Comment

What is Array?

An array is a data structure that contains a group of elements. Typically these elements are all of the same data type. Arrays are commonly used in computer programs to organize data so that a related set of values can be easily sorted or searched.

Arrays are also used in a number of MS Excel spreadsheet functions.

For example, a search engine may use an array to store Web pages found in a search performed by the user. When displaying the results, the program will output one element of the array at a time. This may be done for a specified number of values or until all the values stored in the array have been output. While the program could create a new variable for each result found, storing the results in an array is much more efficient way to manage memory.

Arrays are also used in Java programming:

The syntax for storing and displaying the values in an array typically looks something like this:

arrayname[0] = "This ";
arrayname[1] = "is ";
arrayname[2] = "pretty simple.";

print arrayname[0];
print arrayname[1];
print arrayname[2];

The above commands would print the first three values of the array, or “This is pretty simple.” By using a “while” or “for” loop, the programmer can tell the program to output each value in the array until the last value has been reached. So not only do arrays help manage memory more efficiently, they make the programmer’s job more efficient as well.

Posted on 1 Comment

MS Excel Index Function

The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.

Syntax 

=INDEX (array, row_num, [col_num], [area_num])

=INDEX (reference, row_num, [col_num], [area_num])

Arguments 

  • array – A range of cells, or an array constant.
  • row_num – The row position in the reference or array.
  • col_num – [optional] The column position in the reference or array.
  • area_num – [optional] The range in reference that should be used.

Explanation:

1.We want to know how much student 5 got In physics. So we first type index and select array (all the numbers).

2.Forrow_num we can give 5 or use match function (it is easier to use match function when the list is long).

3. For column_num we can give 4 or use match function (it is easier to use match function when there are many column).

Usage notes

Use the INDEX function to get a value from a list or table based on its location. For example, the formula =INDEX(A1:B5,2,2) will return the value at the address B2.

The INDEX function has two forms: array and reference.

Posted on Leave a comment

MS Excel Sumproduct Function

You have to install MS Office first. Please click the link below:

Suppose you bought 4 products in many quantities. This is time consuming to multiply unit with price and the add them together. Excel has a built in formula for that.

The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more flexibility. Other functions can easily be used inside SUMPRODUCT to extend functionality even further.

Syntax
=SUMPRODUCT (array1, [array2], …)
Arguments
• array1 – The first array or range to multiply, then add.
• array2 – [optional] The second array or range to multiply, then add.

Select array 1 then multiply with selected array2 and press enter.

How to do this calculation manually:
(3*2) +(5*1.50) +(1*60) +(2*10.5) =94.5

Usage notes

The SUMPRODUCT function works with arrays, but it doesn’t require the normal array syntax (Ctrl + Shift + Enter) to enter. The purpose of the SUMPRODUCT function is to multiply, then sum, arrays. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. Up to 30 arrays can be supplied.

For more MS Excel function please click the link:

Posted on 2 Comments

MS Excel LookUp Function

The Microsoft Excel LOOKUP function is one of the most powerful functions of MS Excel. The function helps in finding a value from a range/table or from a vector/(column, row).

You can use this function to get values from the same spreadsheet and workbook or from different spreadsheets/workbooks.

For more details please click the link:

LOOKUP function is searching specific values within a data set. If you want to be an excel expert then you have to be a great knowledge about LOOKUP formula. It will also save you lots of time.

Here you get a list of most popular excel LOOKUP formulas.

OFFSET MATCH MATCH – The offset function in Excel is an exceptionally flexible formula that, when joined with the Match formula, can imitate the usefulness of a lookup. The Match formula at that point enables you to move vertically and horizontally from that reference indicate discovers your arrival value. The key difference in utilizing the offset function is that you should begin your formula with a solitary cell reference, regularly the upper left hand corner of your information run.

VLOOKUP MATCH – By joining the VLOOKUP and Match elements of Excel, the initially static column reference inside the VLOOKUP equation winds up plainly unique, now at whatever point you embed a column into your informational index, your column reference will refresh with the goal that despite everything you have a similar return value.
INDEX MATCH – The syntax for composing an Index Match can be difficult at first to recall. However, after regular use, it is truly not a difficult formula to remember; I currently use Index Match almost only for the greater part of my lookups.

INDEX MATCH MATCH – Adding an extra Match formula to the basic Index Match formula improves it with the goal that you can play out a matrix lookup. It is an indistinguishable formula from Index Match, with the exception of your column reference is currently powerful.

VLOOKUP – Full abbreviation of VLOOPKUP is Vertical LOOKUP. This VLOOKUP is the most famous and oldest LOOKUP formula in excel. In your excel learning it will be a new milestone when you write a VLOOUP formulas in a first time. By the way, if you use it continually then you will find the flow of this task.

HLOOKUP – Full abbreviation of HLOOPKUP is Horizontal LOOKUP. HLOOKUP works similarly in VLOOKUP. VLOOKUP will find values vertically in columns and HLOOKUP will find values horizontally in rows. VLOOKUP and HLOOKUP’s syntaxes are same. Main difference with the VLOOKUP is that you must horizontal range in HLOOKKUP function.

VLOOKUP HLOOKUP – A matrix lookup infers that you are looking into both a vertical and horizontal value to pinpoint the arrival value you need to pull. When you consolidate Excel’s basic vertical and horizontal lookup formulas, you wind up with a grid query.

You can install MS products, click the link below: