✕

Find the best tutors and institutes for Microsoft Excel Training

Find Best Microsoft Excel Training classes

✕

Search for topics

Lesson Posted on 09 Jul IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training

Practical use of Two way Look-up (Category: Advanced Excel)

Rakesh Pradhan

I have done BTech and MTech in CS from VTU Bangalore. I have around 3 years of Experience in Teaching....

Dear Friends, Today in this lesson,We will understand the practical application of two way lookup. Most of the Advanced Excel user or MIS people will be very much acquainted with the concept of Two-way lookup. Ok, so let start our concept with a small story. Mr.Vinod works in Sales for a Private... read more

Dear Friends,

Today in this lesson,We will understand the practical application of two way lookup. Most of the Advanced Excel user or MIS people will be very much acquainted with the concept of **Two-way lookup.**

Ok, so let start our concept with a small story.

Mr.Vinod works in Sales for a Private Company.The company has devised incentive plans for the sales work force for each territory across a region in order to appreciate there achievements.Hence for a candidate to achieve his incentive amount.He has to achieve following two condition.

1) Target Achievement in %

2) and his/her territory sales figures

The company has presented following table:

% Achievement for Target of 3 Months | Territory Sales figure per Month | ||

0-2,50,000 | 2,50,000-4,00,000 | >4,00,000 | |

101%-105% | 2.0% | 2.5% | 3.0% |

105%-115% | 2.25% | 2.75% | 3.5% |

>115% | 2.5% | 3.0% | 4.0% |

(Table 1)

For example if Mr.Vinod's per month per territory sales is in the bracket of** 0-2,50,000** and % Achievement is between **101% to 105%** for the target of 3 months then he will be eligible for **2%** on his entire sales figures.

and if per month per territory sales figures is greater than **>4,00,000** and surplus achievement is beyond **115%** then he gets **4%** of the entire sales.

So how can a MIS person get down to put this table in excel to prepare incentive report.

Putting down the figures manually will be very time consuming and prone to mistakes.

Best way to put the table to work is to remove duplication of condition which are appearing more than once.

For e.g in the header condition,2,50,000 is appearing twice.If the table has mentioned for 0-2,50,000,the resultant values will be 2%, 2.5% and 2.5% then why one need to mention again the bracket of 2,50,000-4,00,000.If values for the amount of 2,50,000 has already been mentioned under the bracket of 0-2,50,000.The next batch of values must start for amount greater than 2,50,000,which is obiviously the second column,similarly for the sales amount for more than 4,00,000 must appear in third column.

Similary for the conditions of % Achievement of Target for 3 Months,shall be changed in similar manner by removing the duplication.

Can you see that 105% is appearing twice in Table-1.If values for condition 105% is appearing in the bracket of 101%-105%.Then why one needs to put 105% condition along with 115% and create a bracket of 105%-115%,it's nothing but unnecessary duplication.

The simplified version of the above table looks like this.

% Achievement for target of 3 Months | Territory Sales figure per Month | ||

0 | 250001 | 400001 | |

101 | 2 | 2.5 | 3.0 |

105 | 2.25 | 2.75 | 3.5 |

115.1 | 2.5 | 3.0 | 4.0 |

(Table 2)

Now you may be wondering how come a sales person achieving zero sales figures per month becomes eligible for incentive amount.Though the company has mentioned from 0 to 2,50,00 bracket but no company would entertain a candidates zero contribution towards said target.In order resovle this query,we can put if condition to see the sales person sales figure achievement is more than zero.

=IF(AND(SALES_FIGURE>0,ACHIEVEMENT>101),"ELIGIBLE","NOT_ELGIBLE")

Now apply filter for eligible candidates.So we have eliminated those candidates who have brought zero contribution for there territory.

Our next formula to fetch % value for incentive calculation will come for this eligible candidates.

Now the main logic of solving the problem.

As you can see all the figures in the above table are fixed.This table shows us that in order to get eligible for incentive a candidate must bring the same figures on the bench which are specified in the table.But how's that possible.The person sales figures can be any random number.It may 500,1,88,800 or it may be 6,50,000.It can be any number.

So how to figure out.As we can see in the top most table(Refer Table-1).They have formed the range i.e for example *2,50,000 to 4,00,000 Sales figures and % Achievement in the bracket of 105%-115%,the candidate will get a certain % of incentive on his/her sales,irrespective of whatever sales figure the candidate has achieved under that category.

* It's 2,50,001 and not 2,50,000

Here concept of approximation fairly works well.So whenever a lookup will see a **amount less** than 2,50,001 in the Sales report for incentive calculation,the formula will search for that slab in the Incentive plan table(Which we have simplified for our purpose).As he cannot see any result for the said figure and neither it could exceed the search beyond 2,50,001.It will certainly fall in category which shows zero sales figure and % Achievement which starts from 101% and goes down the line,so that candidate has to achieve 101% of the target in order to win incentive.In this case also a candidate achieving 115% cannot exceed the % slab of 115.1% which is greater than 115%.Hence we have purposely mentioned it has 115.1% which is greater than 115% as mentioned by the organisation in (Table 1) of there incentive plan.

So lets put all this understanding in our excel sheet.So we practically get to know,how this theory works.

What formula are we going to use.

**Index+Match.**

(Table 3)

As you can see, with the help of IF & AND formula We get the eligibility.You can cleary see in this snap shot. A candidate who's achievement is less than 101% is not eligible though his sales figures is more than zero as per our Table-1 & 2.

Now comes our James Bond,that is the **Index+Match formula** who will be helping us to come to ultimate conclusion the % of incentive on the their sales amount.

(Table-4)

This is my incentive table on the new sheet in same workbook i.e Book 4.The name of the sheet has been renamed to incentive slab.

Now the formula:

**=INDEX('Incentive Slab'!$F$5:$H$7,MATCH('Sales Report'!$C3,'Incentive Slab'!$E$5:$E$7,1),MATCH('Sales Report'!$D3,'Incentive Slab'!$F$4:$H$4,1))**

So you can see, the formula starts with **Index with open bracket immediately after word "Index".**

As it is open and ready to take the conditions,I switch to the next sheet i.e **"Incentive Slab"** Sheet which is our sheet 2 which was renamed for our understanding.As I head towards selecting the range of cells.I target the % value which will decide what amount a candidate will get on his sales figures and % Achievement of the Target for 3 Months.As you can see in the above mentioned formula.Which starts from cell F5 and ends at cell H7,it captures the real flesh of table.After selecting the aforementioned range of cells and fixing it with dollar sign from all side so that selection does not move has we drag the formula down the line in our sales report.Otherwise it will give us the error by bringing unwanted cells in our formula.

Part II

Now once the above part of the formula is done,we start typing the **Match formula,** another secret weapon of our James Bond.With this Match formula we will be certainly targeting the condition which flanks the % value on which incentive amount will be decided.

Kindly observe the Bolder part of Match function

In this part Match formula after opening selects the % Achievement in the Sales report sheet(Refer Table-3) which starts from C3 and goes down the column,now based upon what value it has selected it will search for similar value in "Incentive Slab"sheet (Refer Table-4) which starts E5 and goes up tilll E7,here also we will fix the range has it will not move while dragging the formula.

The next Match does similar calculation,but with some modification.Here instead of targeting the % Achievement in Sales Report sheet(Refer Table-3),it starts its selection from cell D3 which is our Sales figures in Lacs in the same sheet.

As we have clearly mentioned we check for both the condition i.e Sales figures and % Achievement in IF & AND formula,based on that we decide the eligibility.

Coming on the sheet 2 i.e " Incentive Slab"(Refer Table-4) We select the header condition that is the Value in Lacs starting from "Zero" at cell F4 up till H4 and dollar signs fix it.

But still something is left which if not considered will make our formula useless.Till now we have been selecting cell ranges in order to grab the data from both the sheets.

Just look at the formula once again!!

Every match function ends with three choices

0 -Indicates Exact Match

1 -Less than

-1 - Greater Than

Similary in our formula

We will be choosing 1 to at end of our Match formula with closing brackets.The reason behing choosing 1 instead of zero or -1 so that our formula while never looks for the fixed conditions in the incentive table and neither it exceeds the threshold value which is selected at the inception of both the Match formula from Sales report sheet(Refer Table-3).The moment it tries to stress pass the limit,it sees value for which it is searching the data and when it finds that values in next column or row is more than the specified value it stops it search in table (In this case the "Incentive Table"-Table 4 in "Incentive Slab" sheet)falls back in the column or row which is either less to the threshold figure or equal to it.

So aif Mr. Vinod has sales greater than zero but less than 2,50,001 in his territory and his achievement is 115%.

The formula will search for condition which is less than 2,50,001 but couldn't find the value and neither it could exceed the figure of 2,50,001 so it choose the lesser amount than 2,50,001 which is obiviously 0 and for % Achievement is 115% which is less than 115.1 so it cannot exceed as we have mentioned 1 in our Match function which stands for "less than",but natural the search stops at 105% as the co-ordinates matches i.e **zero in header condition and** **105% on the left side**,We get the answer as **2.25%**.So the conclusion is that Mr. Vinod will get 2.25% has Incentive amount on his entire Sales figure.

This way Two way look up formula using Index+Match helps us get the right data for the condition mentioned in the Sales Report sheets.

Any query regarding this, please let me Know Or Any query regarding Training in Excel, you can surely contact us.

read less Like 0

Comments Lesson Posted on 07 Apr IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Why Option Explicit is Required in the VBA Editor

Kumar S

I have 5 Years of experience in MNC's and I worked on real time projects in VBA, Python, MSBI and SQL.

Hi Folks,Do you know Excel VBA Editor has functionality where you don't need to declare a variable in memory (DIM).But what will happen if you write a program without declaring a variable nothing happens?Then why do we need to declare a variable and what is Option Explicit? Yes here comes the answer.When... read more

Hi Folks,

Do you know Excel VBA Editor has functionality where you don't need to declare a variable in memory (DIM).

But what will happen if you write a program without declaring a variable nothing happens?

Then why do we need to declare a variable and what is Option Explicit? Yes here comes the answer.

When you write option explicit at the top of your VBA Editor VBA will force you to declare a variable it's a standard practice to declare a variable.

One more point when you are not declaring a variable in VBA by default VBA Program considers that as a variant data type which consumes more space, Also it increases programs running time.

So next time when you write a program don't forget to declare a variable.

Happy learning!!

Like 0

Comments Asked on 29 Mar IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training Exam Coaching/Foreign Education Exam Coaching/Advanced Placement Tests Coaching/Computer Science

How do I transfer AutoCorrect from one computer to another computer?

Answer

Looking for Microsoft Excel Training classes

Find best Microsoft Excel Training classes in your locality on UrbanPro.

Lesson Posted on 24 Feb IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training

MS Excel: Formulas and Functions: Listed by Category

Siddhartha Mahapatra

I am working in IBM as a Lead Finance and Administration. I have gained exposure on financial reporting...

MS Excel: Formulas and Functions: Listed by Category Worksheet formulas are built-in functions that are entered as part of a formula in a cell. These are the most basic functions used when learning Excel. VBA functions are built-in functions that are used in Excel's programming environment called Visual... read more

MS Excel: Formulas and Functions: Listed by Category

Worksheet formulas are built-in functions that are entered as part of a formula in a cell. These are the most basic functions used when learning Excel. VBA functions are built-in functions that are used in Excel's programming environment called Visual Basic for Applications (VBA).

Below is a list of Excel formulas sorted by category.

(Enter a value in the field above to quickly find functions in the list below)

A. Lookup/Ref Functions

- ADDRESS (WS): Returns a text representation of a cell address
- AREAS (WS): Returns the number of ranges in a reference
- CHOOSE (WS, VBA): Returns a value from a list of values based on a given position
- COLUMN (WS): Returns the column number of a cell reference
- COLUMNS (WS): Returns the number of columns in a cell reference
- HLOOKUP (WS): Performs a horizontal lookup by searching for a value in the top row of the table and returning the value in the same column based on the index_number
- HYPERLINK (WS): Creates a shortcut to a file or Internet address
- INDEX (WS): Returns either the value or the reference to a value from a table or range
- INDIRECT (WS): Returns the reference to a cell based on its string representation
- LOOKUP (WS): Returns a value from a range (one row or one column) or from an array
- MATCH (WS): Searches for a value in an array and returns the relative position of that item
- OFFSET (WS): Returns a reference to a range that is offset a number of rows and columns
- ROW (WS): Returns the row number of a cell reference
- ROWS (WS): Returns the number of rows in a cell reference
- TRANSPOSE (WS): Returns a transposed range of cells
- VLOOKUP (WS): Performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position

B. String/Text Functions:

- ASC (VBA): Returns ASCII value of a character
- CHAR (WS): Returns the character based on the ASCII value
- CHR (VBA): Returns the character based on the ASCII value
- CLEAN (WS): Removes all nonprintable characters from a string
- CODE (WS): Returns the ASCII value of a character or the first character in a cell
- CONCAT (WS): Used to join 2 or more strings together
- CONCATENATE (WS): Used to join 2 or more strings together (replaced by CONCAT Function)
- CONCATENATE with & (WS, VBA): Used to join 2 or more strings together using the & operator
- DOLLAR (WS): Converts a number to text, using a currency format
- EXACT (WS): Compares two strings and returns TRUE if both values are the same
- FIND (WS): Returns the location of a substring in a string (case-sensitive)
- FIXED (WS): Returns a text representation of a number rounded to a specified number of decimal places
- FORMAT STRINGS (VBA): Takes a string expression and returns it as a formatted string
- INSTR (VBA): Returns the position of the first occurrence of a substring in a string
- INSTRREV (VBA): Returns the position of the first occurrence of a string in another string, starting from the end of the string
- LCASE (VBA): Converts a string to lowercase
- LEFT (WS, VBA): Extract a substring from a string, starting from the left-most character
- LEN (WS, VBA): Returns the length of the specified string
- LOWER (WS): Converts all letters in the specified string to lowercase
- LTRIM (VBA): Removes leading spaces from a string
- MID (WS, VBA): Extracts a substring from a string (starting at any position)
- PROPER (WS): Sets the first character in each word to uppercase and the rest to lowercase
- REPLACE (WS): Replaces a sequence of characters in a string with another set of characters
- REPLACE (VBA): Replaces a sequence of characters in a string with another set of characters
- REPT (WS): Returns a repeated text value a specified number of times
- RIGHT (WS, VBA): Extracts a substring from a string starting from the right-most character
- RTRIM (VBA): Removes trailing spaces from a string
- SEARCH (WS): Returns the location of a substring in a string
- SPACE (VBA): Returns a string with a specified number of spaces
- SPLIT (VBA): Used to split a string into substrings based on a delimiter
- STR (VBA): Returns a string representation of a number
- STRCOMP (VBA): Returns an integer value representing the result of a string comparison
- STRCONV (VBA): Returns a string converted to uppercase, lowercase, proper case or Unicode
- STRREVERSE (VBA): Returns a string whose characters are in reverse order
- SUBSTITUTE (WS): Replaces a set of characters with another
- T (WS): Returns the text referred to by a value
- TEXT (WS): Returns a value converted to text with a specified format
- TEXTJOIN (WS): Used to join 2 or more strings together separated by a delimiter
- TRIM (WS, VBA): Returns a text value with the leading and trailing spaces removed
- UCASE (VBA): Converts a string to all uppercase
- UPPER (WS): Convert text to all uppercase
- VAL (VBA): Returns the numbers found in a string
- VALUE (WS): Converts a text value that represents a number to a number

C. Date/Time Functions:

- DATE (WS): Returns the serial date value for a date
- DATE (VBA): Returns the current system date
- DATEADD (VBA): Returns a date after which a certain time/date interval has been added
- DATEDIF (WS): Returns the difference between two date values, based on the interval specified
- DATEDIFF (VBA): Returns the difference between two date values, based on the interval specified
- DATEPART (VBA): Returns a specified part of a given date
- DATESERIAL (VBA): Returns a date given a year, month, and day value
- DATEVALUE (WS, VBA): Returns the serial number of a date
- DAY (WS, VBA): Returns the day of the month (a number from 1 to 31) given a date value
- DAYS (WS): Returns the number of days between 2 dates
- DAYS360 (WS): Returns the number of days between two dates based on a 360-day year
- EDATE (WS): Adds a specified number of months to a date and returns the result as a serial date
- EOMONTH (WS): Calculates the last day of the month after adding a specified number of months to a date
- FORMAT DATES (VBA): Takes a date expression and returns it as a formatted string
- HOUR (WS, VBA): Returns the hours (a number from 0 to 23) from a time value
- ISOWEEKNUM (WS): Returns the ISO week number for a date
- MINUTE (WS, VBA): Returns the minutes (a number from 0 to 59) from a time value
- MONTH (WS, VBA): Returns the month (a number from 1 to 12) given a date value
- MONTHNAME (VBA): Returns a string representing the month given a number from 1 to 12
- NETWORKDAYS (WS): Returns the number of work days between 2 dates, excluding weekends and holidays
- NETWORKDAYS.INTL (WS): Returns the number of work days between 2 dates, excluding weekends and holidays
- NOW (WS, VBA): Returns the current system date and time
- SECOND (WS): Returns the seconds (a number from 0 to 59) from a time value
- TIME (WS): Returns a decimal number given an hour, minute and second value
- TIMESERIAL (VBA): Returns a time given an hour, minute, and second value
- TIMEVALUE (WS, VBA): Returns the serial number of a time
- TODAY (WS): Returns the current system date
- WEEKDAY (WS, VBA): Returns a number representing the day of the week, given a date value
- WEEKDAYNAME (VBA): Returns a string representing the day of the week given a number from 1 to 7
- WEEKNUM (WS): Returns the week number for a date
- WORKDAY (WS): Adds a specified number of work days to a date and returns the result as a serial date
- WORKDAY.INTL (WS): Adds a specified number of work days to a date and returns the result as a serial date (customizable weekends)
- YEAR (WS, VBA): Returns a four-digit year (a number from 1900 to 9999) given a date value
- YEARFRAC (WS): Returns the number of days between 2 dates as a year fraction

D. Math/Trig Functions:

- ABS (WS, VBA): Returns the absolute value of a number
- ACOS (WS): Returns the arccosine (in radians) of a number
- ACOSH (WS): Returns the inverse hyperbolic cosine of a number
- AGGREGATE (WS): Apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows
- ASIN (WS): Returns the arcsine (in radians) of a number
- ASINH (WS): Returns the inverse hyperbolic sine of a number
- ATAN (WS): Returns the arctangent (in radians) of a number
- ATAN2 (WS): Returns the arctangent (in radians) of (x,y) coordinates
- ATANH (WS): Returns the inverse hyperbolic tangent of a number
- ATN (VBA): Returns the arctangent of a number
- CEILING (WS): Returns a number rounded up based on a multiple of significance
- CEILING.PRECISE (WS): Returns a number rounded up to the nearest integer or to the nearest multiple of significance
- COMBIN (WS): Returns the number of combinations for a specified number of items
- COMBINA (WS): Returns the number of combinations for a specified number of items and includes repetitions
- COS (WS, VBA): Returns the cosine of an angle
- COSH (WS): Returns the hyperbolic cosine of a number
- DEGREES (WS): Converts radians into degrees
- EVEN (WS): Rounds a number up to the nearest even integer
- EXP (WS, VBA): Returns e raised to the nth power
- FACT (WS): Returns the factorial of a number
- FIX (VBA): Returns the integer portion of a number
- FLOOR (WS): Returns a number rounded down based on a multiple of significance
- FORMAT NUMBERS (VBA): Takes a numeric expression and returns it as a formatted string
- INT (WS, VBA): Returns the integer portion of a number
- LN (WS): Returns the natural logarithm of a number
- LOG (WS, VBA): Returns the logarithm of a number to a specified base
- LOG10 (WS): Returns the base-10 logarithm of a number
- MDETERM (WS): Returns the matrix determinant of an array
- MINVERSE (WS): Returns the inverse matrix for a given matrix
- MMULT (WS): Returns the matrix product of two arrays
- MOD (WS): Returns the remainder after a number is divided by a divisor
- ODD (WS): Rounds a number up to the nearest odd integer
- PI (WS): Returns the mathematical constant called pi
- POWER (WS): Returns the result of a number raised to a given power
- PRODUCT (WS): Multiplies the numbers and returns the product
- RADIANS (WS): Converts degrees into radians
- RAND (WS): Returns a random number that is greater than or equal to 0 and less than 1
- RANDBETWEEN (WS): Returns a random number that is between a bottom and top range
- RANDOMIZE (VBA): Used to change the seed value used by the random number generator for the RND function
- RND (VBA): Used to generate a random number (integer value)
- ROMAN (WS): Converts a number to roman numeral
- ROUND (WS): Returns a number rounded to a specified number of digits
- ROUND (VBA): Returns a number rounded to a specified number of digits
- ROUNDDOWN (WS): Returns a number rounded down to a specified number of digits
- ROUNDUP (WS): Returns a number rounded up to a specified number of digits
- SGN (VBA): Returns the sign of a number
- SIGN (WS): Returns the sign of a number
- SIN (WS, VBA): Returns the sine of an angle
- SINH (WS): Returns the hyperbolic sine of a number
- SQR (VBA): Returns the square root of a number
- SQRT (WS): Returns the square root of a number
- SUBTOTAL (WS): Returns the subtotal of the numbers in a column in a list or database
- SUM (WS): Adds all numbers in a range of cells
- SUMIF (WS): Adds all numbers in a range of cells based on one criteria
- SUMIFS (WS): Adds all numbers in a range of cells, based on a single or multiple criteria
- SUMPRODUCT (WS): Multiplies the corresponding items in the arrays and returns the sum of the results
- SUMSQ (WS): Returns the sum of the squares of a series of values
- SUMX2MY2 (WS): Returns the sum of the difference of squares between two arrays
- SUMX2PY2 (WS): Returns the sum of the squares of corresponding items in the arrays
- SUMXMY2 (WS): Returns the sum of the squares of the differences between corresponding items in the arrays
- TAN (WS, VBA): Returns the tangent of an angle
- TANH (WS): Returns the hyperbolic tangent of a number
- TRUNC (WS): Returns a number truncated to a specified number of digits

E. Statistical Functions:

- AVEDEV (WS): Returns the average of the absolute deviations of the numbers provided
- AVERAGE (WS): Returns the average of the numbers provided
- AVERAGEA (WS): Returns the average of the numbers provided and treats TRUE as 1 and FALSE as 0
- AVERAGEIF (WS): Returns the average of all numbers in a range of cells, based on a given criteria
- AVERAGEIFS (WS): Returns the average of all numbers in a range of cells, based on multiple criteria
- BETA.DIST (WS): Returns the beta distribution
- BETA.INV (WS): Returns the inverse of the cumulative beta probability density function
- BETADIST (WS): Returns the cumulative beta probability density function
- BETAINV (WS): Returns the inverse of the cumulative beta probability density function
- BINOM.DIST (WS): Returns the individual term binomial distribution probability
- BINOM.INV (WS): Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion
- BINOMDIST (WS): Returns the individual term binomial distribution probability
- CHIDIST (WS): Returns the one-tailed probability of the chi-squared distribution
- CHIINV (WS): Returns the inverse of the one-tailed probability of the chi-squared distribution
- CHITEST (WS): Returns the value from the chi-squared distribution
- COUNT (WS): Counts the number of cells that contain numbers as well as the number of arguments that contain numbers
- COUNTA (WS): Counts the number of cells that are not empty as well as the number of value arguments provided
- COUNTBLANK (WS): Counts the number of empty cells in a range
- COUNTIF (WS): Counts the number of cells in a range, that meets a given criteria
- COUNTIFS (WS): Counts the number of cells in a range, that meets a single or multiple criteria
- COVAR (WS): Returns the covariance, the average of the products of deviations for two data sets
- FORECAST (WS): Returns a prediction of a future value based on existing values provided
- FREQUENCY (WS): Returns how often values occur within a set of data. It returns a vertical array of numbers
- GROWTH (WS): Returns the predicted exponential growth based on existing values provided
- INTERCEPT (WS): Returns the y-axis intersection point of a line using x-axis values and y-axis values
- LARGE (WS): Returns the nth largest value from a set of values
- LINEST (WS): Uses the least squares method to calculate the statistics for a straight line and returns an array describing that line
- MAX (WS): Returns the largest value from the numbers provided
- MAXA (WS): Returns the largest value from the values provided (numbers, text and logical values)
- MEDIAN (WS): Returns the median of the numbers provided
- MIN (WS): Returns the smallest read less

Like 0

Comments Lesson Posted on 05/09/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training

Variant Datatype: Excel/Access/Office VBA

Punit kumar

I have a total of six years plus of work experience.I have worked on multiple projects of different sizes....

The Variant data type is the data type for all variables that are not explicitly declared as some other type. The Variant data type has notype-declaration character. A Variant is a special data type that can contain any kind of data except fixed-length String data. A Variant can also contain the special... read more

The Variant data type is the data type for all variables that are not explicitly declared as some other type. The Variant data type has notype-declaration character.

A Variant is a special data type that can contain any kind of data except fixed-length String data. A Variant can also contain the special valuesEmpty, Error, Nothing, andNull. We can determine how the data in a Variant is treated using the VarType function or TypeName function.

We can assign any value at any time within a single module/program. If we don't declare the data type of any variable then it automatically declare as variant data type:

Sub modVariant1()

Dim VData as variant

VData =1234

Msgbox vData

vData="Aman"

Msgbox vData

End Sub

Sub modVariant2()

'In this example, data type is not declared

Dim VData

VData =1234

Msgbox vData

vData="Aman"

Msgbox vData

End Sub

Like 1

Comments Answered on 02/11/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

What is an Excel Macro?

Excel Training Excel Schooling.com

Excel Macro also called as VBA (Visual Basic Application). If you are good in Visual basic you can do more automation process in Excel. If you are not good in VB , you can try Record macro feature in Excel. Where it create programme based on your activities.

Like 2

Answers 1 Comments 3 Looking for Microsoft Excel Training classes

Find best Microsoft Excel Training classes in your locality on UrbanPro.

Asked on 03/08/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training

Hello, I am 25 years old. Due to some financial problem. I didn t complete my study on time. Right now,... read more

Hello, I am 25 years old. Due to some financial problem. I didn?t complete my study on time. Right now, I am a student of B.Com final year from SOL Delhi University. Right now, I am also doing a job. I have a experience of 4 years as a Back end Executive but I don?t know what kind of skills should a back end guy have? In my company, My work is only CRM. I am very afraid now. What will happen in my future? Please provide me some guidance for my bright future. Please suggest me. read less

Answer

Lesson Posted on 25/07/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training

VBA Tip: Print Random Numbers In A Selected Range

Karthik Dale

II have 8 years of Data Analytics Experience. I have extensive experience in wide range of Excel Automation...

Let’s say you want to print some random numbers in a selected range then you can use below Macro. Sub Print_Randomnumbers() Dim myrng As Range For Each myrng In Selection myrng.Value = Application.WorksheetFunction.Int(Rnd * 1000) Next myrng End Sub How it works? It's a for each loop... read more

Let’s say you want to print some random numbers in a selected range then you can use below Macro.

Sub Print_Randomnumbers()

Dim myrng As Range

For Each myrng In Selection

myrng.Value = Application.WorksheetFunction.Int(Rnd * 1000)

Next myrng

End Sub

How it works?

- It's a for each loop which loops through each cell in the selection.
- VBA function Rnd populate random number from 0 to 1.
- If you want to print numbers up to the range of 1000 then multiply Rnd with 1000.
- . Int function pulls the integer value from Rnd*1000.

read less

Like 0

Comments Lesson Posted on 30/05/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel

Saburudeen Ma

I started my teaching carrier since i was doing my UG, as part time faculty in a computer institute....

Purpose: Get a random number between 0 and 1. Return Value: Any decimal number between 0 and 1. Syntax: =RAND() Usage: RAND() function will returns a number between 0 and 1. For example, = RAND() will generate a number like 0.554533. RAND() calculates a new value each time the worksheet is calculated.... read more

Get a random number between 0 and 1.

Return Value:

Any decimal number between 0 and 1.

Syntax:

=RAND()

Usage:

RAND() function will returns a number between 0 and 1. For example, = RAND() will generate a number like 0.554533.

RAND() calculates a new value each time the worksheet is calculated. To stop random numbers from being updated, copy the cells that contain RAND to the clipboard, then use ** Paste Special -> Values** to convert to text.

To get a random number that doesn’t change when the worksheet is calculated, enter = RAND() in the formulas bar and then press F9 to convert the formula in to its result.

To generate a set of random numbers in multiple cells, select the cells, enter RAND() and * press ctrl+enter*.

To generate a random number between a and b, use this formula : **rand()*(b-a)+a**

Like 0

Comments Looking for Microsoft Excel Training classes

Find best Microsoft Excel Training classes in your locality on UrbanPro.

Answered on 14/05/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training

Edulitix

Hi, You can check our course - Analyse and Visualize with Excel. Excel is the most popular tools used by analyst and MIS manager for getting insights from data. Create powerful Dashboards to enable data driven decision making.

Like 2

Answers 32 Comments UrbanPro.com helps you to connect with the best Microsoft Excel Training classes in India. Post Your Requirement today and get connected.

x

Ask a Question