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

[#1]

Given two files,where sales data contains the details of the sales according to ID or month.

Commission data gives the commission percentages according to ranges.

Now my problem is,When I select a month field in the sheet,if they are more than one sales in that month,two fields get select in the commission percentage if they belong to different ranges.

What I need is instead of that,depending the select values in the sales field,it should calculate the total costs of the sales and check the range and highlight the commission matrix the corresponding percentage.

[MarcoWedel · Accepted solution]

Hi,

one solution without creating a static interval matching table could be to use calculated listboxes with expressions like:

=Aggr(Only({$<Commision={'=[Low Value]<=Sum(Cost) and [High Value]>=Sum(Cost)'}>} Commision), Commision)

or

=Aggr(If(Sum(Cost)>=[Low Value] and Sum(Cost)<=[High Value],Commision),Commision)

hope this helps

regards

Marco

[#3]

Hi Marco,

Thanks a lot.This is exactly what I was looking for.

Thanks and regards,

Srinath

[MarcoWedel]

glad you liked it.

regards

Marco