Faysal Easy Excel

Mr. D is working in Logistic Department of a Company .  He is trying to make a formula that will show the "Product Remarks" Like below logic: Table 1: IF Statement logic According to his logic he is trying to build a formula. He tries but failed and finally asked " How to create a formula that contain a combination of aging in a cell to result as stated on Remarks? ". Also add " copy it down to the next rows that resulted the aging accordingly ." Solution: In C1 cell, enter below formula: =IF(AND(A1<=0,B1>=0),"Fast Moving",IF(AND(AND(A1>=1,A1<=5),AND(B1>=1,B1<=3)),"Slow Moving",IF(AND(AND(A1>=6,A1<=10),AND(B1>=4,B1<=10)),"Slow Moving",IF(AND(AND(A1>=11,A1<=15),AND(B1>=7,B1<=9)),"Surplus",IF(AND(A1>=16,B1>=10),"Dead Stock","Unknown"))))) The result will looks like below image: Image 1: IF Statement result ...

Someone asked, " How to convert Logical Text to Logical Value in Excel? " In more details, his question like, you have below data table: Image 1: Logical Text to Logical Value The D2 cell contains a formula and it will show the logical result like: =CONCATENATE(A2,B2,C2) as TRUE or FALSE . More specifically he wanted: =IF(CONCATENATE(A2,B2,C2),"Logical","Illogical") But according to Microsoft Excel, if you apply CONCATENATE function then the value always returns TEXT and IF function will always return FALSE that means " Illogical ". He wanted if he change the logical operator symbol in B2 cell, then depends on this logical operator symbol, the result in D2 cell of =CONCATENATE(A2,B2,C2) will change as TRUE or FALSE . Solution: After studying on it, I've found a simple solution. And the solution is why don't we use a IF formula on this? Yes, an IF formula can help us regarding this issu...

Real Life Problem: In a departmental store, the Store Manager needs to buy Onion in January month for getting discount from the Dealer. The Manager get the vegetable buying target in December. He applied a SUMPRODUCT() to get the total kilograms of Onion he must buy to get the discount from the Dealer in January. But the problem is, In Kilograms column, somehow text data entered. And that is why the SUMPRODUCT() is not working. It shows a #VALUE error. Now the question is, how can you help the Store Manager to get the correct total kilograms of Onion need to buy in January by ignoring the #VALUE Error?   Example Data Table: Below is the target for the Store Manager month wise got in December: Image 1: Target data Solution: In this situation There are 2 main things you can suggest to the Store Manager for getting the correct result. First one is by removing the text value from the Kilograms column. and you will get the result 321. The next one is handling the e...

Real Life Problem: Assume that, in a quiz contest there are 5 participants and played 4 games. From the score card need to calculate the Results for each player based on below conditions: In F column, the result should be Win if any player scored less than or equal to 0 (Zero) , in all 4 games. Blank means not participated. So if any player not participate in any of 4 games, then no need to calculate the result. All player must participate in 4 games. Example Data Table: Image 1: Score card Solution: In F2 Column you can apply few functions to get the result. But here I would like to show 2 different types of function here. One is Array formula and another is Regular formula . Array Formula: {=IF(OR(B2:E2="",B2:E2>0),"Loss","Win")} Image 2: Array formula General Formula: =IF(MAX(B2:E2)>0,"Loss","Win") Image 3: Result 2 Excel is fantastic analysis tool. One problem has many solutio...

Real Life Problem: In a security monitoring control room, In-Charge needs to calculate the start time and end time after a video is observing. But the time calculation is not showing correctly after the midnight. Assume that, the Starting Time is: 23:00:00 PM (11:00:00 PM) and ending time is after midnight (that means next date): 00:37:00 AM (12:37:00 AM) . Now the question is how to find the time difference in between in these times? Example Data Table: As the problem already provided the time, so assume that, below is the data: Image 1: Example Data Now how to calculate the estimated time in E2 Cell ? Solution: To calculate in a clear way I have created 3 extra columns. First C column Numerical Difference simply calculates the difference from Starting Time to Ending Time . We know that in Computer ending time or latest time or date is always bigger in numerical value then starting time or date. So, I have just used the below formula for C2 cell: =B2-A2...

Real Life Problem: One of my friend used VLOOKUP() for his daily calculation in corporate office. Many things he can return with VLOOKUP() . One day I have asked him, " return the left side value " by using VLookup . He tried many times but failed. Finally told me to teach him Index and Match. I've done this. Now he is using Index-Match for his daily task in various MIS Reporting. Only 1 limitation I've found in VLookup() and that is, it can't return the left side (-1) column's value. Example Data Table: Assume that, D4:G7 is the range of players data table. D3 = " Player ", E3 = " Rank ", F3 = " Goal ", G3 = " Country ". It contains 4 rows. VLookup() can't return the players name with the help of Rank number. But Index()-Match() can return the Player Name using the same Rank number. I am just going to show you, Index-Match can do that, what a VLookuo() can't. My friend used the formula in E11 Ce...

Real Life Problem: In Corporate Office, there is a common problem, if you are going to print a complete employee list, then it will go for 2nd page. Or any data or report which takes more than 1 pages, then a question is in mind, "how to print the row title or column title on each sheet"? Example Data Table: Assume that A1:D40 is the data table, where A1 = Employee Name, B1 = Designation, C1 = Salary, D1 = Date of Joining. And A2:D40 contains the 39 records of 39 employees. Solution: To print the row and column title on each sheet, you don't need to re-design the table. In Excel you can do it through "Page Setup" dialog box. 1) Go to sheet tab from this dialog box 2) Click on Rows to repeat at top and click on the row number which you want to print as header on each page. 3) Do the same thing for Columns to repeat at left text box. 4) Make sure the page setup 5) Finally click on Ok button to set it. 6) Then use print command to pr...