Previous Topic Next Topic

Incentive Calculation with few Condition

Solved/Closed
YeohSeow Posts 17 Registration date Sunday May 2, 2010 Status Member Last seen May 25, 2010 - May 2, 2010 at 03:44 AM
YeohSeow Posts 17 Registration date Sunday May 2, 2010 Status Member Last seen May 25, 2010 - May 3, 2010 at 06:50 AM
Hi,

I need help...

each month I need to calculate the incentive. manual calculation is wasting my time.

i know excel can do it, but I am not expert... I looking for some one to help me..

Entitlement of Incentive base on below condition,
1.condition 1-Model A, B, C, D - at less 2 units.
2.condition 2-Model E, F, G, H, I - at less 2 units
3.condition 3-Total Units with minimum 8 to 11 entitle for 1000 & above 12 units 1500

if minimun of total units not archive no incentive were given.

hope some one can give me a help.

Thank you

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 2, 2010 at 10:40 AM 
This is the best I could make out

1. at least one value in column A, B, C  and D needs to be >= 2
2. at least one value in column E, F, G, H, and I needs to be >= 2
3. If both above conditions (1 and 2) are true and: 
          a. value in K is between 8-11, then incentive is 1000
          b. value in K is greater or equal to 12, then incentive is 1500

if condition 1, 2 or 3 is not true then incentive is 0

If that is correct then use this formula (this is for row 7)

=IF(AND(OR(A7>=2, B7>=2,C7>=2,D7>=2), OR(E7>=2,F7>=2, G7>=2,H7>=2,I7>=2)),IF(AND(K7>=8,K7<=11),1000,IF(K7>=12,1500,0)),0)
YeohSeow Posts 17 Registration date Sunday May 2, 2010 Status Member Last seen May 25, 2010
May 2, 2010 at 10:51 AM
Dear Friend,

i just updated the file, here the link :https://authentification.site/files/22232906/Sample_1.xls

refer to O22, the answer become o, it should be 1000, how to solve it.

Thanks
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 2, 2010 at 11:10 AM
The formula I gave you will work How ever now I notice that you have added J column too, in that case

=IF(AND(OR(A7>=2, B7>=2,C7>=2,D7>=2), OR(E7>=2,F7>=2, G7>=2,H7>=2,I7>=2, J7>=2)),IF(AND(K7>=8,K7<=11),1000,IF(K7>=12,1500,0)),0)
YeohSeow Posts 17 Registration date Sunday May 2, 2010 Status Member Last seen May 25, 2010
May 2, 2010 at 11:14 AM
but it not work for O22. any idea on it.

thanks
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 2, 2010 at 05:42 PM
what O22 ? It works for all the rows that you had in sample. 022 is blank in the book that you provided
YeohSeow Posts 17 Registration date Sunday May 2, 2010 Status Member Last seen May 25, 2010
May 3, 2010 at 06:50 AM
my mistake... you are right... thanks for your help. may I know how you master your excel skill?
Answer 2 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 2, 2010 at 07:18 AM
It seems that you need to use IF statements. Unfortunately I am not able to understand your conditions etc. But the basic idea would be

=IF(AND(OR(A1="model a",A1="model b",A1="model c",A1="model d"),B1<2),59, IF(AND(OR(A1="model e",A1="model f",A1="model g",A1="model h",A1="model i"),B1<2), 60, IF( AND(C1>=8, C1<=11), 1000, IF(C1>12, 1500,0) )))

Could you please upload a sample file with sample data etc on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
YeohSeow Posts 17 Registration date Sunday May 2, 2010 Status Member Last seen May 25, 2010
May 2, 2010 at 09:49 AM
https://authentification.site/files/22231783/Sample_1.xls
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 2, 2010 at 10:03 AM
Now based on the data could you explain what you are looking for. I am still lost at your conditions. Incentive can occur based on any of the three, or all three has to be true etc. If you could show by example what you seek, it would help in understanding your issue
YeohSeow Posts 17 Registration date Sunday May 2, 2010 Status Member Last seen May 25, 2010
May 2, 2010 at 10:39 AM
Dear Friend,

Latest link as below https://authentification.site/files/22232669/Sample_1.xls

Condition 1: must archieve minimum 2 units
Condition 2: must archieve minimum 2 units
Condition 3: Total Units with minimum 8 to 11 units entitle for 1000 & above 12 units 1500

More detail kindly refer to the file.

Similar discussions

calculating fuel surcharge
Nancy -
xpcman -

1 response
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Need to calculate monthly avg and yearly miles from odometer
jamishore -
vcoolio -

5 responses
Clear cell contents based on value of another cell range using VBA
Tbonekiller -
Tbonekiller -

6 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response