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-sidePlease 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