Sat 20 Aug 2016

Tips and Tricks 143 - Increment a Number when Workbook is Opened (Invoice or PO Number)

By |Saturday, August 20th, 2016|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double Click on "This Workbook"
5. Copy paste the Macro code given - Replace Sheet1 and B1 as per your need
6. Save your file as .xlsm if you intend to reuse Macro again.

< A workbook illustrating this can be downloaded from Invoice Number Generator >

'**** Macro Starts

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("B1") = Worksheets("Sheet1").Range("B1") + 1
End Sub

Tue 05 Jan 2016

Solution - Challenge 31 – Increment All Digits by 1

By |Tuesday, January 05th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the problem Challenge 31 – Increment All Digits by 1

Use below formula -

=A1+REPT(1,LEN(A1))

Sun 20 Dec 2015

Solution - Challenge 30 – Average Last 5 Numbers in a Range

By |Sunday, December 20th, 2015|Categories: Solutions|Tags: , , , , , , , , , |0 Comments

Below is a possible solution to Challenge 30 – Average Last 5 Numbers in a Range.

Use below formula -

=IFERROR(AVERAGE(INDIRECT("A"&LARGE(INDEX(ISNUMBER(A1:A100)*
(ROW(A1:A100)),,),MIN(COUNT(A1:A100),5))&":"&"A"&LARGE(
INDEX(ISNUMBER(A1:A100)*(ROW(A1:A100)),,),1))),0)

The workbook containing this solution is uploaded to Solution - Challenge 30 – Average Last 5 Numbers in a Range

Sun 06 Dec 2015

Solution - Challenge 29 – Reverse (Flip) a Number String

By |Sunday, December 06th, 2015|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 29 – Reverse (Flip) a Number String

You may use below formula to reverse a number string -

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
*10^ROW(INDIRECT("1:"&LEN(A1)))/10)

 

Sat 05 Dec 2015

Challenge 31 - Increment All Digits by 1

By |Saturday, December 05th, 2015|Categories: Challenges|Tags: , , , , , , |4 Comments

This time's challenge is a short one but it may take some time to think through.

Suppose, you have a number in cell A1. The challenge is to add 1 in all digits and come with a result.

Hence,

If A1=28, result would be 39.

If A1 = 123, result would be 123+111 = 234

If A1= 912, result would be 912+111 = 1023

If A1=1999, result would be 1999+1111 = 3110

If A1=9999, result would be 9999+1111 = 11110

You can choose to post your answer in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 05-Jan-16.

Mon 23 Nov 2015

Solution - Challenge 28 – Create Pascal’s Triangle

By |Monday, November 23rd, 2015|Categories: Solutions|Tags: , , , , , , , , |1 Comment

Below is a possible solution to the Challenge 28 – Create Pascal’s Triangle.

Put 1 in L2 and put following formula in C2 and drag right and down.

=IF(IF(B2="",0,B2)+IF(D2="",0,D2)=0,"",IF(B2="",0,B2)+IF(D2="",0,D2))

This will generate following Pascal's Triangle.

1

To make it better looking, you may use Conditional Formatting.

Select C2 and U11 > Home tab > Conditional Formatting > New Rule > Use a formula to determine which cells to format and put following formula and choose appropriate Fill Colour

=AND(ISNUMBER(B2),ISNUMBER(D2))

This will generate the following Pascal's Triangle

1

A workbook illustrating the solution can be downloaded from Pascal Triange Solution

Sat 21 Nov 2015

Challenge 30 - Average Last 5 Numbers in a Range

By |Saturday, November 21st, 2015|Categories: Challenges|Tags: , , , , , , , , , |1 Comment

The Excel file related to this challenge can be downloaded from Challenge - Average Last 5 Numbers

Let's say that you have got a range like this. The range which can contain values is A1:A100. The problem is to pick up last 5  numbers and average them. Notice that there are blanks and non numbers also. You need to pick only numbers. In this case, you need to average 2,44,9,26,4, hence answer would be 17.

Note, sometimes range can have less than 5 numbers also, hence formula should take care of this requirement also. Suppose there are only 4 numbers, in that case average should be for those 4 numbers only. Also, entire range can have no numbers. In that case, result should be 0.

1

You may post your answer in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 20-Dec-15.

Sat 07 Nov 2015

Challenge 29 - Reverse (Flip) a Number String

By |Saturday, November 07th, 2015|Categories: Challenges|Tags: , , , , , , , |2 Comments

This time challenge is for flipping a number string. Excel VBA has got a function "StrReverse" which can be used to perform this task through VBA.

The challenge is to do this through a formula.

Let's say A1 = 70948

You formula should return a result of 84907

Note if you number string is 709480, your result will still be 84907 as leading zero doesn't have any meaning in a number string.

You may post your answer in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 6-Dec-15.

Sat 24 Oct 2015

Challenge 28 - Create Pascal's Triangle

By |Saturday, October 24th, 2015|Categories: Challenges|Tags: , , , , , , , |1 Comment

Pascal Triangle is very famous in number theory. Interested persons can read about Pascal's triangle at following Wikipedia article - https://en.wikipedia.org/wiki/Pascal%27s_triangle

Anyway, whether read it at Wikipedia or not, below is Pascal's triangle. One look at it and you will understand the pattern.

1

Now, the challenge before you is following -

1. Put 1 in L2.

2. You need to write a formula which can be put in the row below and dragged left/right and down to create Pascal's Triangle.

1

You may post your answer in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 23-Nov-15.

Sat 03 Oct 2015

Tips & Tricks 112 - Convert a Number into Years and Months

By |Saturday, October 03rd, 2015|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

Suppose, you have been given a number into cell A1 say 26 and you want to display it as 2 Years and 4 Months, you can use following formula -

=INT(A1/12)&" Years and "&MOD(A1,12)&" Months"

Now, an user can become more demanding and he can say that if month is less than 12, then Years should not be displayed. For example, he might say that 8 should be converted to 8 Months and it should not be shown as 0 Years and 8 Months.

In this case, the formula would be -

=IF(INT(A1/12)>0,INT(A1/12)&" Years and ","")&MOD(A1,12)&" Months"

Now 8 will be displayed as 8 Months only not as 0 Years and 8 Months.

Now, user can ask more. He can say when I give 12, it displays as 1 Years and 0 Months and he simply wants to see 1 Years only. And for 36, he wants to see only 3 Years not 3 Years 0 Months. In this case, formula will have to be tweaked more. Now, the formula becomes -

=IF(INT(A1/12)>0,INT(A1/12)&" Years ","")&IF(MOD(A1,12)=0,"",MOD(A1,12)&" Months")

Now an user can come and can ask for one last thing. He can say that if this is 1 Year or 1 Month, it should not be displayed as Years or Months as 1 is not plural. Hence, 25 should be displayed as 2 Years and 1 Month not as 2 Years and 1 Months. Hence, 18 should not be displayed as 1 Years and 6 Months but as 1 Year and 6 Months. Similarly 13 should be displayed as 1 Year and 1 Month not as 1 Years and 1 Months.

=IF(INT(A1/12)>0,INT(A1/12)&" Year"&IF(INT(A1/12)>1,"s","")&" and ","")&MOD(A1,12)&" Month"&IF(MOD(A1,12)>1,"s","")