Hi. This is my first time here so I'm not sure if I attached the excel sheet in the right way but I added a screenshot of it. I'm facing some challenging with calculating the commission and bonus with conditions in excel. I have explained some points below. I would really appreciate if someone can help me to complete the challenges with excel formulas or VB or both.

Book1.PNG

**Notes:**

- Product ID is unique.

- More Products will be added in the future.

- Some products are under a main product (Sub-Product).

- No. of Sub-Products are calculated automatically.- Category Name is selected from a drop-down list from the right-side`Please Login or Register to view this content.`

reference area.

- Category Price is automatically pulled from the reference area based

on the selection of the Category Name.`Please Login or Register to view this content.`

**Challenges:**

- Commission should be calculated based on the Product Level and

Category Name. Product Level depends on how many Products this

Product has on top of it (e.g. Product1 has no Product on top of it

(no main product) but Product5 has 4 products on top of it where

Product4 on top of Product5 and Product3 on top of Product4 up to

Product1) so the Product Level for Product5 is 4. Since Product5 is

Category2 so the Category Commission for Product5 is $ 200 (From the

reference area on the right side).

- Bonus should be calculated based on the Category Name and must have

atleast 2 Sub-Products with the same Category Name within 30 days as

per the Initial Date (e.g. Product1 has 2 Sub-Products which are

Product2 and Product9 (Both are Category1) but they are more than 30

days difference in the Initial date so Bonus should be 0 (zero). e.g.

Product2 has more than 2 Sub-Products all under the same Category

Name and within 30 days so Bonus should be $ 20). I have tried the

formula in the attached excel sheetbut couldn't complete it. Please advise with a formula with VB script which can do that.`Please Login or Register to view this content.`

## Bookmarks