Find the best tutors and institutes for Microsoft Excel Training

Find Best Microsoft Excel Training classes

Please select a Category.

Please select a Locality.

No matching category found.

No matching Locality found.

Outside India?

Search for topics

Microsoft Excel Training Updates

Ask a Question

Post a Lesson

All

All

Lessons

Discussion

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

=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)

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
Comments
Dislike Bookmark

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!!

read less
Comments
Dislike Bookmark

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.

FIND NOW

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

  1. ADDRESS (WS): Returns a text representation of a cell address
  2. AREAS (WS): Returns the number of ranges in a reference
  3. CHOOSE (WS, VBA): Returns a value from a list of values based on a given position
  4. COLUMN (WS): Returns the column number of a cell reference
  5. COLUMNS (WS): Returns the number of columns in a cell reference
  6. 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
  7. HYPERLINK (WS): Creates a shortcut to a file or Internet address
  8. INDEX (WS): Returns either the value or the reference to a value from a table or range
  9. INDIRECT (WS): Returns the reference to a cell based on its string representation
  10. LOOKUP (WS): Returns a value from a range (one row or one column) or from an array
  11. MATCH (WS): Searches for a value in an array and returns the relative position of that item
  12. OFFSET (WS): Returns a reference to a range that is offset a number of rows and columns
  13. ROW (WS): Returns the row number of a cell reference
  14. ROWS (WS): Returns the number of rows in a cell reference
  15. TRANSPOSE (WS): Returns a transposed range of cells
  16. 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:

  1. ASC (VBA): Returns ASCII value of a character
  2. CHAR (WS): Returns the character based on the ASCII value
  3. CHR (VBA): Returns the character based on the ASCII value
  4. CLEAN (WS): Removes all nonprintable characters from a string
  5. CODE (WS): Returns the ASCII value of a character or the first character in a cell
  6. CONCAT (WS): Used to join 2 or more strings together
  7. CONCATENATE (WS): Used to join 2 or more strings together (replaced by CONCAT Function)
  8. CONCATENATE with & (WS, VBA): Used to join 2 or more strings together using the & operator
  9. DOLLAR (WS): Converts a number to text, using a currency format
  10. EXACT (WS): Compares two strings and returns TRUE if both values are the same
  11. FIND (WS): Returns the location of a substring in a string (case-sensitive)
  12. FIXED (WS): Returns a text representation of a number rounded to a specified number of decimal places
  13. FORMAT STRINGS (VBA): Takes a string expression and returns it as a formatted string
  14. INSTR (VBA): Returns the position of the first occurrence of a substring in a string
  15. INSTRREV (VBA): Returns the position of the first occurrence of a string in another string, starting from the end of the string
  16. LCASE (VBA): Converts a string to lowercase
  17. LEFT (WS, VBA): Extract a substring from a string, starting from the left-most character
  18. LEN (WS, VBA): Returns the length of the specified string
  19. LOWER (WS): Converts all letters in the specified string to lowercase
  20. LTRIM (VBA): Removes leading spaces from a string
  21. MID (WS, VBA): Extracts a substring from a string (starting at any position)
  22. PROPER (WS): Sets the first character in each word to uppercase and the rest to lowercase
  23. REPLACE (WS): Replaces a sequence of characters in a string with another set of characters
  24. REPLACE (VBA): Replaces a sequence of characters in a string with another set of characters
  25. REPT (WS): Returns a repeated text value a specified number of times
  26. RIGHT (WS, VBA): Extracts a substring from a string starting from the right-most character
  27. RTRIM (VBA): Removes trailing spaces from a string
  28. SEARCH (WS): Returns the location of a substring in a string
  29. SPACE (VBA): Returns a string with a specified number of spaces
  30. SPLIT (VBA): Used to split a string into substrings based on a delimiter
  31. STR (VBA): Returns a string representation of a number
  32. STRCOMP (VBA): Returns an integer value representing the result of a string comparison
  33. STRCONV (VBA): Returns a string converted to uppercase, lowercase, proper case or Unicode
  34. STRREVERSE (VBA): Returns a string whose characters are in reverse order
  35. SUBSTITUTE (WS): Replaces a set of characters with another
  36. T (WS): Returns the text referred to by a value
  37. TEXT (WS): Returns a value converted to text with a specified format
  38. TEXTJOIN (WS): Used to join 2 or more strings together separated by a delimiter
  39. TRIM (WS, VBA): Returns a text value with the leading and trailing spaces removed
  40. UCASE (VBA): Converts a string to all uppercase
  41. UPPER (WS): Convert text to all uppercase
  42. VAL (VBA): Returns the numbers found in a string
  43. VALUE (WS): Converts a text value that represents a number to a number

C. Date/Time Functions:

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

D. Math/Trig Functions:

  1. ABS (WS, VBA): Returns the absolute value of a number
  2. ACOS (WS): Returns the arccosine (in radians) of a number
  3. ACOSH (WS): Returns the inverse hyperbolic cosine of a number
  4. AGGREGATE (WS): Apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows
  5. ASIN (WS): Returns the arcsine (in radians) of a number
  6. ASINH (WS): Returns the inverse hyperbolic sine of a number
  7. ATAN (WS): Returns the arctangent (in radians) of a number
  8. ATAN2 (WS): Returns the arctangent (in radians) of (x,y) coordinates
  9. ATANH (WS): Returns the inverse hyperbolic tangent of a number
  10. ATN (VBA): Returns the arctangent of a number
  11. CEILING (WS): Returns a number rounded up based on a multiple of significance
  12. CEILING.PRECISE (WS): Returns a number rounded up to the nearest integer or to the nearest multiple of significance
  13. COMBIN (WS): Returns the number of combinations for a specified number of items
  14. COMBINA (WS): Returns the number of combinations for a specified number of items and includes repetitions
  15. COS (WS, VBA): Returns the cosine of an angle
  16. COSH (WS): Returns the hyperbolic cosine of a number
  17. DEGREES (WS): Converts radians into degrees
  18. EVEN (WS): Rounds a number up to the nearest even integer
  19. EXP (WS, VBA): Returns e raised to the nth power
  20. FACT (WS): Returns the factorial of a number
  21. FIX (VBA): Returns the integer portion of a number
  22. FLOOR (WS): Returns a number rounded down based on a multiple of significance
  23. FORMAT NUMBERS (VBA): Takes a numeric expression and returns it as a formatted string
  24. INT (WS, VBA): Returns the integer portion of a number
  25. LN (WS): Returns the natural logarithm of a number
  26. LOG (WS, VBA): Returns the logarithm of a number to a specified base
  27. LOG10 (WS): Returns the base-10 logarithm of a number
  28. MDETERM (WS): Returns the matrix determinant of an array
  29. MINVERSE (WS): Returns the inverse matrix for a given matrix
  30. MMULT (WS): Returns the matrix product of two arrays
  31. MOD (WS): Returns the remainder after a number is divided by a divisor
  32. ODD (WS): Rounds a number up to the nearest odd integer
  33. PI (WS): Returns the mathematical constant called pi
  34. POWER (WS): Returns the result of a number raised to a given power
  35. PRODUCT (WS): Multiplies the numbers and returns the product
  36. RADIANS (WS): Converts degrees into radians
  37. RAND (WS): Returns a random number that is greater than or equal to 0 and less than 1
  38. RANDBETWEEN (WS): Returns a random number that is between a bottom and top range
  39. RANDOMIZE (VBA): Used to change the seed value used by the random number generator for the RND function
  40. RND (VBA): Used to generate a random number (integer value)
  41. ROMAN (WS): Converts a number to roman numeral
  42. ROUND (WS): Returns a number rounded to a specified number of digits
  43. ROUND (VBA): Returns a number rounded to a specified number of digits
  44. ROUNDDOWN (WS): Returns a number rounded down to a specified number of digits
  45. ROUNDUP (WS): Returns a number rounded up to a specified number of digits
  46. SGN (VBA): Returns the sign of a number
  47. SIGN (WS): Returns the sign of a number
  48. SIN (WS, VBA): Returns the sine of an angle
  49. SINH (WS): Returns the hyperbolic sine of a number
  50. SQR (VBA): Returns the square root of a number
  51. SQRT (WS): Returns the square root of a number
  52. SUBTOTAL (WS): Returns the subtotal of the numbers in a column in a list or database
  53. SUM (WS): Adds all numbers in a range of cells
  54. SUMIF (WS): Adds all numbers in a range of cells based on one criteria
  55. SUMIFS (WS): Adds all numbers in a range of cells, based on a single or multiple criteria
  56. SUMPRODUCT (WS): Multiplies the corresponding items in the arrays and returns the sum of the results
  57. SUMSQ (WS): Returns the sum of the squares of a series of values
  58. SUMX2MY2 (WS): Returns the sum of the difference of squares between two arrays
  59. SUMX2PY2 (WS): Returns the sum of the squares of corresponding items in the arrays
  60. SUMXMY2 (WS): Returns the sum of the squares of the differences between corresponding items in the arrays
  61. TAN (WS, VBA): Returns the tangent of an angle
  62. TANH (WS): Returns the hyperbolic tangent of a number
  63. TRUNC (WS): Returns a number truncated to a specified number of digits

E. Statistical Functions:

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

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

read less
Comments
Dislike Bookmark

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.
Answers 1 Comments 3
Dislike Bookmark

Looking for Microsoft Excel Training classes

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

FIND NOW

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 +1 IT Courses/MS Office Software Training/Microsoft Excel Training less

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?

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

 

read less
Comments
Dislike Bookmark

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 +1 IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training less

Rand() Function

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

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. 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

read less
Comments
Dislike Bookmark

Looking for Microsoft Excel Training classes

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

FIND NOW

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.
Answers 32 Comments
Dislike Bookmark

About UrbanPro

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

Overview

Lessons 22

Total Shares  

+ Follow 40,609 Followers

Related Topics

Top Contributors

Connect with Expert Tutors & Institutes for Excel VBA & Macro Training

x

Ask a Question

Please enter your Question

Please select a Tag

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more