How to update the value based on a range in another table?

[#1]

There are two tables.

1.Commision percentage based on the max and min ranges of the price of the product.

2.Sales info.

The problem is I have to calculate the commission amount based on the values in table 1.We cant hard-code it because the percentage may change in table1.

[ankitaag]

Hi,

Can you provide some sample data and tell what you exactly wanna calculate.

[#3]

Suppose the Commission percentages are:

Low Value   Max Value    Percentage

0                   100000          2

100001           500000         2.5

500001           1000000       3

And I have given a table which contains various cost prices.Now the objective is to calculate the commission amount using these values.And one constraint,these values should not be hard-coded.That means these values may be changed.I heard something called dynamic bucketing using macros.Does that help?If yes,then how can you upload fields from the sheet to the macro?

[#4]

And I will also provide some sample data for which I need to calculate the commission values.

Sales Id       Cost

1                  200000  

2                  300000

3                  670000

4                  950000

5                  750000

And the problem is no of buckets may also change,so is there a way in which the data in excel files(where the data is changed) can be loaded as fields into the macro and corresponding updates are done when the data is reloaded.

I have also seen one post on dynamic bucketing,is this helpful in solving my problem?The problem I am facing is how to upload fields from the sheet into the macro and dividing into buckets.

http://www.infinityinsight.com/blog/?p=221

[MarcoWedel · Accepted solution]

‌search for intervalmatch

regards

Marco

[#6]

Hi Marco,

Thanks a lot.It worked

[MarcoWedel]

Glad it helped.

please close your thread if your question is answered.

thanks

regards

Marco

[#8]

Hi Marco,

I would like to ask you more about this interval match.Suppose I want to check the interval for a changing variable in the sheet itself.

For example,Take sales ID's 1,2,3,4 .Suppose they belong to January.I would like to check the interval based on the sum of the costs of those sales ID's.i.e.,I should check the interval based on a variable that changes according to the selections.Can we do that without using macros?

[MarcoWedel]

Hi,

please post some sample data and your expected result related to selections made (if possible in a new thread).

thanks

regards

Marco

[#10]

Hi,

I have started a new Thread and attached some files for examples.

How to update the value based on a range in the another table or variable?

Thanks and Regards,

Srinath