• 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.

Multiple Formula with Greater than Discount & volume - Formula

J

John D'souza

New Member
Dear Sir,
If dealer has done business in a month as per sale amount in (excel sheet),
The Payout % to be derived based on total volume and discounts given.
There is a grid of volume / discount & product
Please help with the formula
Thanks
John
 

Attachments

  • Dealers Payment.xlsx
    270.7 KB · Views: 11
Hi tried
Following formula
=INDEX(Rate!$D$6:$J$23,MATCH(F5,Rate!$D$6:$D$23,0),MATCH(H5,Rate!$D$6:$J$6,0)) rate doesn't match in case of disc & should be 0% in case discount exceed
attached file for reference
Thanks
 

Attachments

  • Dealers Payment.xlsx
    378 KB · Views: 8
Hi Khalid,
Thanks for revert
Tried =INDEX(Rate!$D$6:$J$23,IF(OR(F5="A",F5="E"),MATCH(F5,Rate!$D$6:$D$23,0)+IF(F5="A",IF(E5<=50,0,IF(E5<=60,1)),IF(E5<=65,0,IF(E5<=75,1))),MATCH(F5,Rate!$D$6:$D$23,0)),MATCH(H5,Rate!$D$6:$J$6,0)) but still not working desired result
Thanks
 
Hi ,

Please give a clear explanation of :

1. What are the elements of data in your workbook ; what are the roles played by each element ; in your case , what is the function of data in the range A6:B79 , D6:J23 in the Rate tab , and A4:I3885 in the Data tab ?

2. In which cells in which tab do you want formulae ?

3. What should these formulae return , and what should their output be based on ?

4. Work out a few examples with as much of variation as possible i.e. if you work out 2 examples , both of them dealing with amounts in the range 2 lakhs to 10 lakhs , it will not give as clear a picture as if you work out 5 or more examples , with each amount from a different slab.

If you take the trouble to do all of the above , I can guarantee that you will get an answer within 2 hours.

Narayan
 
Thanks for your revert Mr. Narayan,
Explaining to understand more about the workbook
we are distributing payout to dealers,
There is a Payment Slab of 2L to 10L, 10L to25L.....
Product bifurcated as A,B,C,D.................
Specific amount of commission is pre-decided as per product, every dealer is bifurcated as per codes and as per their performance in sale payout is given.
In few products there is discount grid mentioned if discount exceeds % to be 0%
Thanks
John
 

Attachments

  • Dealers Payment.xlsx
    365.8 KB · Views: 3
Hi ,

Since there is no answer to any of my questions , I will leave it to others to answer if they have understood your requirements.

Narayan
 
1. What are the elements of data in your workbook ; what are the roles played by each element ; in your case , what is the function of data in the range A6:B79 , D6:J23 in theRate tab , and A4:I3885 in theData tab ?

è A6:B79 , D6:J23 in theRate tab – it is % grid given to dealers as per sale, A4:I3885 in theData tab – in this data product, sale amount etc given

2. In which cells in which tab do you want formulae ?

è Cell No. G5 require formula

3. What should these formulae return , and what should their output be based on ?

è Formulae should return % as per rate chart & if discount exceed 0%

4. Work out a few examples with as much of variation as possible i.e. if you work out 2 examples , both of them dealing with amounts in the range 2 lakhs to 10 lakhs , it will not give as clear a picture as if you work out 5 or more examples , with each amount from a different slab.

Examples already tried one formula its working but issue in case of discount Please check earlier attachment
 
Hi ,

Things are still not very clear , since you are still talking about formulae rather than worked out examples.

If a formula is giving the wrong result , that formula cannot serve as an example.

What is required are worked out examples , which you have manually worked out and which give the correct outputs.

Since these have not been provided , we have to spend more time clearing doubts instead of providing a solution.

1. Column D in the Rate tab table - what purpose does it serve ?

We cannot have data such as upto 50% , 51 to 60% ,...

We need to have data such as 50% , 60% ,...

Also , it is only product A which has 2 entries , all others have just one entry each. What does it mean if discount exceeds 0% ?

2. How are the category labels in column H in the Data tab entered ? Should not these come from the table in columns A and B in the Rate tab ?

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