by Crispo Mwangi | Aug 5, 2022 | ADVANCED EXCEL
The ability to clean data is one of the most valuable skills for any data analyst. Below are 3 ways how to clean any special character from a string. Using a Formula...
by Crispo Mwangi | Jul 29, 2022 | ADVANCED EXCEL
Recently a friend approached me with the below issue. How do you compare if the applicant has all the 3 required qualifications? Using a formula =IF(SUM(ISNUMBER(SEARCH($G$3:$G$5,C3))+0)=3,”Y”,”N”) How the formula works: SEARCH($G$3:$G$5,C3)...
by Crispo Mwangi | Jul 22, 2022 | ADVANCED EXCEL
Here are 3 methods you can use to count uppercase lowercase or numbers in a string. Using a Formula =SUM(ISERROR(MID(B3,SEQUENCE(LEN(B3)),1)+0)*EXACT(LOWER(MID(B3,SEQUENCE(LEN(B3)),1)),MID(B3,SEQUENCE(LEN(B3)),1))) =SUM({0;0;0;1;1;1;0;0}*{1;1;1;0;1;1;1;1})…...
by Crispo Mwangi | Jul 8, 2022 | ADVANCED EXCEL
Missing numbers in a sequence can lead to wrong results in data analysis, especially if you are using these numbers to keep a count of items. In the last article, we showed How to Highlight Non-Sequential Numbers. This article shows how to list the missing numbers...
by Crispo Mwangi | May 18, 2022 | ADVANCED EXCEL, CHARTS
Waterfall charts are used to explain the +ve and -ve components of a change in value between two points. It reveals the contribution per factor affecting the item being analyzed. For example, If you had Budgeted $ 66M but ended up spending $ 72M, you can easily tell...
by Crispo Mwangi | May 4, 2022 | ADVANCED EXCEL
Summing data based on weekdays or weekends is easy, but what if you are required to SUMIF Excluding Weekends & Holidays? There are 2 ways of doing this: Using SUM, IF & WORKDAY FUNCTION =SUM(IF ( WORKDAY(tbls[Sales Date]-1,1,Holidays) = tbls[Sales...
Recent Comments