You have been given following data and you sort this data in Excel and you were expecting that lowercase will be sorted before uppercase. But Excel is sorting on the basis of FIFO i.e. First In First Out for the same alphabet. Hence if A is coming before a, it will get sorted as A,…
Excel Quiz 29
Solution – Challenge 31 – Increment All Digits by 1
Below is a possible solution to the problem Challenge 31 – Increment All Digits by 1 Use below formula – =A1+REPT(1,LEN(A1))
Challenge 33 – Convert Matrix into Linear Column – II
Last challenge, we converted a matrix into Linear Column. Last time, we picked up values in a row and populated the column and then moved to next row. This time, we have to do exchange row with column. Hence, you need to pick up values from one column and move to the next column and…
Article 33 – Rank when Duplicates Exist (Ties)
Ranking when duplicates (ties) exist is an interesting problem and you will be called upon to make choices when duplicates exist and you have to rank them. Suppose, you have the data like below. 2 rows are formatted in Yellow and 3 rows are formatted in Green to demonstrate the existence of duplicates.
Excel Quiz 28
Tips & Tricks 124 – VBA – Check if a Range is Blank When Range Contains Formulas returning Blanks
In VBA, often, we have situations where we need to check if a range is blank or not. The popular formula which we see following is used to check whether range is blank or not. WorksheetFunction.CountA(Range("A1:A100")) Sub CheckifBlank() If WorksheetFunction.CountA(Range("A1:A100")) Then MsgBox "Range is Not Blank" Else MsgBox "Range is Blank" End If End Sub…
Tips & Tricks 123 – Change the Color of Border Lines of Active Cell
Notice the color of Active Cell which is Green. Few people may like some other color. Excel doesn't provide a native facility to change this. But Chip Pearson offers an Add-in which provides this functionality and has many custom options also. http://www.cpearson.com/excel/RowLiner.htm
Solution – Challenge 30 – Average Last 5 Numbers in a Range
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
Challenge 32 – Convert Matrix into Linear Column – I
Suppose you have been given a grid like below. The challenge before you is to write a formula to create a linear column with no blanks in between. The column ranges would be A to D whereas row ranges would be from 1 to 100. Hence, your formula has to be flexible enough to consider…