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)
Article 31 – Slab Billing – Calculate Income Tax, Electricity (Utility) Bills based on Slabs
You will encounter slab billings in two very common documents – One is Electricity / Utility and another one is Income Tax. If you see your electricity bills, you will notice following type of entries (values are for illustration purposes only, please do not attach any meaning to them) 0 – 50 Units – $1.5…
Challenge 31 – Increment All Digits by 1
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…
Excel Quiz 26
Tips & Tricks 120 – Filter by Color Drop Down Menu Greyed Out Problem
Sometimes, you find that though your are having colored cells but Filter by Color is greyed out i.e. it can not be selected. Following are the possible causes – 1. Your sheets may be grouped. If they are grouped, you will notice word "Group" in the title bar of your Excel workbook. Another way to…
Tips & Tricks 119 – Numerology Sum of the Digits aka Sum the Digits till the result is a single digit
In Numerology, it is often a task to add the digits till the result is a single digit. For example, 74 = 7 + 4 = 11 = 1 + 1 = 2 23 = 2 + 3 = 5 78 = 7 + 8 = 15 = 1 + 5 = 6 1234567 =…
Solution – Challenge 28 – Create Pascal’s Triangle
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. To make it better looking, you may use Conditional Formatting. Select C2 and U11 > Home tab > Conditional Formatting…
Article 30 – VBA – Approaches for Unique Count and Time Performance Results for the Same
As part of this article, we will look into various approaches for counting unique in VBA and also see what time do they take to determine the best approach on the basis of "Time Taken". I have used Charles William's MicroTimer for timing the time. https://msdn.microsoft.com/en-us/library/aa730921%28v=office.12%29.aspx We will see performance of these approaches with following…
Challenge 30 – Average Last 5 Numbers in a Range
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…
Tips & Tricks 118 – Stop Auto Creation to Hyperlinks
We all know that if you enter a web address like www.microsoft.com, http://www.microsoft.com or even e mail address like admin@eforexcel.com, Excel converts these to Hyperlinks which can be clicked. Sometimes, we don't want this behaviour and we want to stop this – Trick 1 – After entering a web address / e mail address, press…