Calculate commission of agents as per sales target achieved:

            Definitions:

1.      Commission: The commission is amount in money that sales representative get it for selling something or products of company.

2.      Sales Rep: Sales representative is a person whose job is to sell product or service for company in particular area

3.      Commission Rate: This is fixed payment or percentage associated with certain amount of sale. Rate is in Percentage. Ex. 10% commission on selling Water Purifier

 

            Description:

            Please watch Video (Multiple Table Lookup) for this belo

                            Multiple Table Lookup: Find Commision Rate

            In excel, we have data of sales rep as per their sales value and company already 

            decided commission for agents as per their policy.

            Here, table1 is ranged name for sales rep in training period, table2 is for below 3 years 

            and table3 for 3+ years.

            (Tip: If we want to calculate months from years use,

                        1 month= (1/12month) =0.0833333333)

                        4 month= 0.0833333*4 = 0.33333

            We are using excel functions VLOOKUP and IF and I call this Multiple LOOKUP.  

            The name stands for we are looking values from multiple tables.

            

            Calculation in Excel:


    1.      To calculate Commission Rate use excel function VLOOKUP with IF condition as I            discussed in video.

                        Syntax of VLOOKUP-

                        =VLOOKUP (lookup_value, table_array, column_index_number)

                        Syntax of If-

                        =IF (logical_test, value_if_true, value_if_false)


    2.      To calculate commission simply multiply Rate to Sales value,

            Commission= (Commission Rate*Sales Value)


                        Please watch this video (Multiple Table Lookup) for more understanding, 

                        video is below


                                                        This is all about it. Have you get this tip ?

Thank you for your valuable time.

SUBSCRIBE to Excel Geeta: As it is.

and Like Comments and Share please.

 






Location: Pandharpur, Maharashtra 413304, India

Comments

Post a Comment

Popular posts from this blog

Image
Hello,   Readers and Excel Lovers .  Welcome to   " Excel-Geeta: as it is ". This page contains an useful information about Excel and Advanced Excel also. Here is a gift for you, "Excel-Geeta's 99 Keyway Table" . This table helps you to keep all useful shortcuts in Microsoft Excel tool with you.           I kept all 99 shortcuts in this table categorized as based on Family as below; 1. Family Enter 2. Family Tab 3. Family Spacebar 4. Family Arrow 5. Family ABCD 6. Family 1to9 7. Family Function  8. Family Special (Highlighted in yellow color) This all placed with same pattern hence it is easy to understand and helps to quick grasping.            So, You have to keep practice and play with keys with your Excel work.                                   ...
Read more
Image
  Calculate Person's Age in Excel:                     Actually, You can calculate person's age orally, But, If you were working with huge data and you want to calculate age belongs to person's Birth Date in that huge data. Then, it will easy and versatile to use formula for calculations.                There are 2 methods as below; 1. Formula mix-up 'INT & YEARFRAC 2. DATEDIF Formula METHOD 1:                 First you should know Birth Date of Person. Formula consist of INT ( ) , Integer function takes 1 argument and we put here another formula YEARFRAC ( ) . YEARFRAC takes 3 arguments, YEARFRAC (start_date, End_date, [basis]) .                 Here, we take Start date as "Current Date" and End date as "Birth Date". and basis is ...
Read more
Image
                 Welcome Readers and Excel Lovers ,                If you are interested in Symbol Nomenclature , You are on right place !!!                Here is a sheet or file that makes all your symbols at one place,                 You only need is read and mind it as per Easy Guidance Method.                                                                  ExcelGeeta's Universal Symbol Table:                                        (If you want to download it simply Double click and  ...
Read more