Quarter sales by month

Key Takeaways

hi guys here your friend rajinikanth excel guru and here i am going to explain you this solution for the test which i posted in the groups right so totally one two sorry one second and let me know that you know one two three and three they got these things are there okay okay let it be i'll explain you so don't skip the video because in between if you skip this video you are unable to understand the concept why i am using that function why i have to use okay all these things you will miss in the future if you face any problem then you are unable to solve it so here is the only months are there okay so here you have to find this which quarter quarter mid january february march is the first quarter april may june this is the second quarter july august september this is third quarter okay so here only months are there see there is no date and there is no years only month so you want to find in which quarter it was this without helper column you have to do and what are the whatever the functions i am using these functions can work from excel 2010 to microsoft p65 right this is the main thing which i am going to explain you because whenever you go for interview so if they it may they use excel 2010 or 12 or 16 like that and not only that i am using excel 2016 then also i'm trying to solve this queries in excel 2010 functions only right so here is the thing which i'm going to explain you step by step don't skip the video and here is the only months are there so you have to find in which quarter it was there one two three totally how many quarters should be there twelve three four quarters okay so what i'll do just i'll use one function but it will not work that function why it will give you value at it because it it is unable to understand if it is a date it can understand but it's not a date it's the only month name okay if it's a date you can understand this is the thing see example i will show you [Applause] something okay so here you can use it like this so to understand it is the sixth june sorry sixth right but here only the month name there is no pre-formatted it's only the month so what i'll do here i'll change little bit to convert this month name to a number that is one ampersand now i press enter see i delete this one okay see february is the second month december is the 12th month july is the seventh month april is the fourth month mark is the tournament now we have to find this in which quarter it was there so what i'll do here simply i'll do divided by three because quarters are only three months entrance so it will give you like this right now here i will use one function that is roundup sorry [Applause] round up and the number of digits will be zero right so if i press ctrl enter it will give you in mid quarter it was there superhour is the first quarter january february mark is in the first quarter april may june this is the second quarter okay so this is the thing double click see december is in the fourth quarter october november december is the three months where it is in the fourth quarter right and coming to here it's very very important and so many people are not able to understand this query now i'm explaining to this query that you have to extract only the third quarter sales third quarter sales means generally month april may june april may june these months sales only i need without helper column april may june okay so here see how i'll do the same procedure but little bit of uh array i'm using the array function but little bit of change that's it the same function month of one ampersand f4 f9 see april geneva this is this is the fourth month okay this is the fourth month so it is showing fourth jan is the first month so it is showing the damn one augustine august is the eighth month so it is showing eight okay see here now see what i'll do and of same procedure divided by three half nine so it will give all the decimals and the whole number so i'll convert this into ground roundup comma zero affluent first of all i'll press enter and i'll show you here to understand better roundup have to [Applause] month of one ampersand yes sorry i have noticed this month number of decimals is zero enter absolutely four one eight six right see if i press f2 and i highlight all the formula and i press f9 see here it is showing two but i did not round up here somewhere after this okay here i did wrong okay here it is wrong it's not a four actually it should be second week okay second somewhere i did wrong here and that's let me check here where i went wrong here round up correct only where i went wrong the same procedure which i over used here see here okay here i did not done divided by three the same this will come over here see f9 2 1 all those things are same wait a minute see two one three two all things are saved now i want to extract only the sales for the third quarter that is april may june so what i'll do here if this whole thing equal to this rocking every set so what you need i need like this row of the whole formula f4 minus rho off in my previous video i explained you why i am using this type of array okay see af9 so wherever it finds the row number it will pick up that number and remaining will be the falses right see if this is the logical text f9 see true true true are they here i'm using the row numbers f9 so automatically it will pick up those numbers which matches the true right uh again i have to do because if comma row of f4 minus row of this thing f4 plus one control enter just simply just show that i don't want to miss the formula now i'll see what i'll do now here so okay control shift enter i'll drag this formula to unlock this let me check simply let me check sit here she'll give all the numbers somewhere i did wrong i think so right that's why it's showing all once wherever um here is equal to this one f4 ctrl shift enter then everything somewhere something it is right already see here i have to now i'll use small function xmd and small and i've come to end of this formula end of this formula and i will use one function that is those goes j dot log five colon js5 ctrl shift enter now now let me check what it will be see the positions where it will be there now simply i will use one function that is index of the sales i'm using without any helper column object guys and if this function will work in all the versions from starting 2000 except 2010 to microsoft t65 right control shift enter um you'll find some errors over here don't worry i'll show you how to read off this error before that i will change this one to see the second quarter sales and the i'll use if error if either if you find any dysfunction give me deep blank i'll learn this formula as it is this is the second work okay this is the quarter within which quarterly will be four fourth month sales it is fourth month sales all of fourth month skills coming to here okay only this query i will giving chance you to prove so how can we find that maximum sales minimum save and who did the maximum sales and some of the quarter okay see here you have to change that thing two it was there so i came to three so automatically it will become three see so here is the criteria where you want to change so that it should get some of quarter three okay so only this thing i will i can post you later and thank you guys thanks for your support have a nice day