INT function

INT rounds of the number to lowest and nearest whole number. INT function does not require any special formatting.

Syntax: INT(Number)

Number	Integer	Formula
3.5	3	=INT(3.5)
7.2	7	=INT(7.2)
15.99	15	=INT(15.99)
-22.7925	-23	=INT(-22.7925)

Example: A competitive exams require the 18 years of age to appear in exams.  Here is example on finding candidate with age over 18.

Click on image to view enlarge

I have uploaded the MS Excel file with INT function and INT function example to ensure that we don't miss anything with respect to basic understanding of INT function.

Also, do let me know your comments about this post and subscribe to us to receive latest updates via email

INT function Example

We assure you knowledge, not SPAM!

Read more on this article...

Rand and Randbetween function to generate random numbers

Rand and Randbetween is used to generate random numbers. It is very useful for people like me who post on blog. This help me in generating random data which I use in post on blog. Randbetween is available in MS Excel 2007 while RAND function is available in MS Excel 2007/2003/XP/2000. Randbetween function has an edge over Rand function as it can generate numbers between lower bound and upper bound and gives user a control to over generation of random numbers.

Rand() Function

Syntax: Rand()

RAND() function does not require any special formatting. However it returns number in decimal or in others words it returns you number between 0 to 1. Take a look at the image below with example of RAND() function.

Click on image  to view enlarge

Randbetween() function

Syntax: RANDBETWEEN(LOWER, UPPER)  is syntax for RANDBETWEEN() function in MS Excel 2007.

Like Rand() function, Randbetween() function does not require any special formatting. Take a look at image below to view example of Randbetween() function.

Click on image  to view enlarge

 Example: The example exhibits how to use RAND() function to generate numbers between fix range.

Click on image  to view enlarge

Mentioned below is RAND() function along with INT function and is equivalent to RANDBETWEEN function of Excel 2007. 

Formula: INT(RAND()*(b-a)+a)

here a= 100 and b=200

=INT(RAND()*(200-100)+100)

(200-100) returns 100 which when multiplied with RAND() return two digit number. Also, here it is treated as upper bound and lower bond. In other words, this function will return numbers between 100 and 200. You can try with different numbers.

RAND()*(200-100)+100 adds to 3 digit number which is greater than 100 but less than 200. Also, some decimal.

INT functions round off to lowest integral value.

If you are still in doubt using RAND() and RANDBETWEEN() function. I have kept the file with example for download

Random Number Example download

To receive more updates on MS Excel help. You can subsribe via email.

We assure you knowledge, not SPAM!

Read more on this article...

Product function

This is simple multiplication function. Very useful in almost all worksheet. However, we prefer to use other alternative to multiply numbers. The alternative to Production function is using cell reference in following manner:  A1*B1*C1 which is equivalent to Product(A1,B1,C1)

Syntax

=Product(number1, number2, number3........,number30)

=Product(Rangeofnumbers)

=Product(number, Rangeofnumbers)

Click on image below

You can download the file with Product formula example. I am sure this will help you in learning this function.

Download Product Example File

Please do let me know your comments about my attempt to help beginners with Product function. Also, you can subscribe via email

We assure you knowledge, not SPAM!

Read more on this article...

Shortcut key to enter Date and Time in worksheet

Using NOW function refreshes each time when you make entry to worksheet. Hence, I dont prefer to use NOW function. I use shortcut key to enter time or Date to worksheet.  This trick is helpful in saving time.

For Time

Shortcut key: Ctrl + Shift + ;

For Date

Shortcut key: Ctrl + ;

Please do let me know your comments about my attempt to help beginners with Shortcut key to enter date and time. Also, you can subscribe via email

We assure you knowledge, not SPAM!

Read more on this article...

Now function for Time

Now function is used to enter current date and  time. The Now function keeps on updating time whenever you open workbook or when you make entry to worksheet. This function is useful when you want to use current date and time.

Formula: Now()

Now function returns date and time. However the format of cells plays very important role. Click on the image below to take a look of different cells formatted returns date and time in different date and time formats. In simple words, Now functions returns date and time based on cell format.

Click on image to view enlarge

For further help, you may download MS Excel file with Now function example.

Now Function Example download

Please do let me know your comments about my attempt to help beginners with Now function. Also, you can subscribe via email

We assure you knowledge, not SPAM!

Read more on this article...

SUMPRODUCT function

The SUMPRODUCT in MS Excel is useful when you want a multiple a set of values to another set of value. This formula is very useful in scenario when you want to calculate the total sales value or cost. Let's take look at syntax.

Syntax: SUMPRODUCT(array1, array2, array3....)

Table

Set A	Set B
a1	b1
a2	b2
a3	b3
a4	b4
.	.
an	bn

SUMPRODUCT({a1, a2, a3, a4...an}, {b1, b2, b3, b4...bn}) = a1 X b1+a2 X b2+a3 X b3+a4 X b4....an X bn

Click on image below to Enlarge

 

In the above example, we have describe an example to calculate Total sales value and Total Cost value. Also, I have enclosed the link with example of SUMPRODUCT formula.

Click here to download SUM Product Example

Kindly let me us know your comment about this post. Also, you can subscribe to this blog to receive latest update in your inbox. Click on the link below

Subscribe to blog via Email

We assure you knowledge, not SPAM!

Read more on this article...