• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

dashboard to top\bottom accounts summary

U

ushaanu

Member
GM All,

I have some accounts data in excel file . I need in my summary sheet top 10 accost and bottom 10 accounts list automatically , data is month wise and branch wise in present monthly I calculate manually.
Is it possible in excel . when I select branch and month\QTR shows particular month\QTR top 10\bottom data accounts list and their data.

Thanks

Anu
 

Attachments

Hi Narayan,

thank a ton

its Working fine but I req in Quarterly base also like if data is full year Jan-to dec monthly . I need two option to see the data one is monthly base and 2nd the qty base.
Also need show the Gm% of particular accounts

regards
Anu
 
Hi Anu ,

Prepare your dashboard with all that you want , formatted the way you want , and then upload your file so that the formulae can be entered in their places.

Narayan
 
Hi Anu ,

See this file.

If you are selecting a month , enter any month name between Jan and Dec. If you are selecting a quarter , enter any number between 1 and 4.

I have assumed that the first quarter comprises the months Jan , Feb and Mar.

Narayan
 

Attachments

thank you soo much it`s perfectly fine but by mistake I forget in my 2nd sample account name can you add if possible .

Regards
Anu
 
hello out there i just saw this thread and the conversations msgs out here. As i m new to excel and in the way to learn i wanted to ask whether u used to write so big big formulaes or it is directly updated when u perform some command as it saw the excel sheet that u peoples uploaded. Sorry for such a dumb question??:(
 
Hi Jayalaxmi ,

All of us were new to Excel at some stage ; speaking for myself , I joined this forum in late 2011 , and by virtue of going through archived questions and answers , as well as by trying to answer questions myself , I have reached a stage where I can say I am fairly competent.

I would certainly recommend this route to anyone who is not an absolute novice ; if you are just starting on Excel , then it would be better if you started off by reading at least one book ; most people recommend any one by John Walkenbach or Bill Jelen or any of the other masters.

Even going through the Excel help on the functions will give you at least a basic idea of the syntax , which is the real starting point for using functions ; if you cannot get the syntax right , or you don't remember the basic purpose of each function , then you are not likely to use the right function for the right job.

The basic functions are :

MID , LEFT , RIGHT , TEXT , SUBSTITUTE , REPLACE

VLOOKUP , HLOOKUP , LOOKUP

INDEX , MATCH , OFFSET

SUMPRODUCT

SUMIF , COUNTIF , SUMIFS , COUNTIFS

LARGE , SMALL

INDIRECT

FREQUENCY

As you become more competent , you can easily add more functions , but I believe mastery of a set of 50 functions should easily qualify you as an expert , though if you wish to specialize in any domain , you should be good at the functions specific to that domain ; e.g. if you are going to specialize in statistics or accounting , there are at least a dozen functions in each of those domains.

More important than a knowledge of the individual functions is a knowledge of how they can be combined in different ways to achieve different purposes.

Over and above a good knowledge of these functions , you should also know two or three other aspects of Excel , such as CF and DV which you have already asked about , and others which you have not yet touched upon , such as Pivot Tables , Charts.

Though much can be accomplished with just the above , a minimum knowledge of VBA would be very helpful.

Narayan
 
Thanks Mr.Narayan

Yes ofcourse the thing that vowed me about this forum is your grand formulaes and yes i juz have the basic ideas and after this i had just started implementing and playing with the formulaes. Sorry again, a moment before i was doing RND with the mod formula but i m not able to understand the output of it as i gone thru
What is the use of Mod formula?

Tells you what is the reminder after dividing one number with another

Syntax of Mod formula:
mod(of this number, divided by this number)

Examples of Excel Mod formula:
mod(5,3) = 2
mod(3,5) = 3

and i implemented as =MOD(ROW(),3) in condtional formatting in one of my excel sheet. m not getting what it exactly doing??
 
Hi ,

The ROW() function merely returns the row number of the row where it is entered ; if you enter =ROW() in any cell in row 17 , it will return the number 17.
The same formula entered in any cell in row 33 will return the number 33.

The MOD function does exactly what the Excel help says it will do.

Thus , the formula =MOD(49,7) will return 0 , since 49 is exactly divisible by 7 , which means there is no remainder. =MOD(53,7) will return 4.

Thus , when you enter a formula such as =MOD(ROW() , 2) , it is clear that this will divide the row number , what ever it may be , by 2 and return the remainder. When you divide any number by 2 , there are only 2 possible values for the remainder , 0 and 1. Thus , if the row number is even , the remainder when it is divided by 2 will be 0 ; if the row number is odd , the remainder when it is divided by 2 will be 1.

Normally this construct is used in CF to colour alternate rows , since each row is alternately odd and even. You can also use the functions ISODD and ISEVEN for this purpose.

Narayan
 
yeah gr8!!!!:):):)
i got it now..i was trying to colour alternate rows...

5ne. i hope u dont mind that i m asking such silly questions. thanks for help. Can i ask u for help if i m really not getting sumthing,hope that u will help me. sum people juz get irritated or aggressive...that y
 
Hi ,

As long as you post your questions here in the forum , I don't think anyone will get aggressive ; and for every person who does get aggressive , there will be many more people who will gladly help with answers.

Narayan
 
You must log in or register to reply here.
Back
Top