Sometimes, you might face a problem that you input a number say 567 and it automatically becomes 5.67. If you enter 8, it becomes 0.08. To correct this problem > File > Options > Advanced and uncheck Red Zone
Solution – Challenge 50 – Cryptography Challenge 3 – Generate Tabula Recta
Below is a possible solution to the Challenge 50 – Cryptography Challenge 3 – Generate Tabula Recta Put following formula and drag down and right =CHAR(MOD(ROWS($1:1)+COLUMNS($A:A)-2,26)+65) A workbook containing the above solution can be downloaded from Solution – Challenge 50 – Cryptography Challenge 3 – Generate Tabula Recta
Challenge 52 – Generate the Sequence 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5…
This time challenge before you is to write a formula which generates a sequence where every digit appears that many times as that digit. Hence, 3 will repeat 3 times, 8 will repeat 8 times and so on..Hence, the exact sequence would be 1,2,2,3,3,3,4,4,4,4,5,5,5,5,5,6,6,6,6,6,6,7,7,7,7,7,7,7….. The solution to above challenge will be published after a month…
Excel Quiz 34
Downloads 07 – Template 07 – A Flexible Lessons Learned Template
< The template can be downloaded from Template 07 – Lessons Learned Template > This time, the template presented is for Lessons Learned. The template covers both what went well and what didn't go well. The category of learning can be customized. I have already put 3 categories which are based on PMI Process Area,…
Tips & Tricks 145 – Determine the First Sunday or any other Day given Weeknumber
If you have been given a week number and has been asked to find the first Sunday for that week, you can use following formula =CEILING(("1JAN"&A1)-14,7)+8+7*(5-1) Where A1 has the year say A1=2016 5 is the Week Number which you can replace. For Finding Monday, add 1 in the formula, add 2 for Tuesday and…
Solution – Challenge 49 – Whether a Word is Isogram or Not
Below is a possible solution to the challenge – Challenge 49 – Whether a Word is Isogram or Not Use following formula – =IF(SUMPRODUCT(–(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW($65:$90)),""))>1)) =1,"Not Isogram","Isogram")
Challenge 51 – Another Word Challenge – Whether Given Words are Anagram or not
Given a word, an anagram is made by using all letters of the given word exactly once. For example, For example, my name "vijay" can be written as "jaivy", "yajvi" and so on….Some well known anagrams are Lead – Flea Admirer – Married Listen – Silent Mother in Law – Woman Hitler Eleven Plus Two…
Tips & Tricks 144 – Enter the Last Save Date and Time
1. Open your workbook and ALT+F11 2. Locate your Workbook name in Project Explorer Window 3. Right click on your workbook name > Insert > Module 4. Copy paste the Macro code given 5. Save your file as .xlsm To get Last Save Date, enter following in a cell =LastSaveDate() To get Last Save Time,…
Solution – Challenge 48 – Whether a Sentence is Pangram or Not
Below is a possible solution to the challenge – Challenge 48 – Whether a Sentence is Pangram or Not =IF(ISNUMBER(SUMPRODUCT(MATCH(ROW($65:$90),INDEX(CODE(MID(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(UPPER(A1)," ",""),".",""),",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1," ",""),".",""),",","")))),1)),,),0))),"Pangram","Not Pangram") Update on 10-Aug-21: The above can be done with a simpler formula =IF(ISNUMBER(MATCH(FALSE,ISNUMBER(SEARCH(CHAR(ROW(97:122)),A2)),0)), "Not Pangram","Pangram") You will have to confirm the above formula with CTRL+SHIFT+ENTER i.e. as an array formula…