• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested if formula for Incentive calculation based on hours worked

M

melvin

Member
Team,

For eligibility employees have to work 100%. However that could be split into 3-4 stores for example.

I have attached my file explaining my issue.

Appreciate any assistance.

Melv.
 

Attachments

Hi:

Find the attached.

I have used array formula, execute it by pressing control+shift+enter keys
Code: 
=MAX(IF($C7:$G7=$A$17:$A$20,$C$17:$C$20))

Thanks
 

Attachments

My apologies. "the heading No of stores worked " meant the store Numbers i.e. 350 & 351. The hours worked should be the driver after checking if the individual has completed 100% work.
 
Nebu, your formula does give the right result. However how did it pick that store 350 had 60% worked hours.

I tried to change hours worked i.e Store no 2 - 60% and store no 1 as 40%, the result should be $1400, but stays @ $1500. Can you assist in getting the If clause that total hours should be 100%
 
Hi:

Use the following array formula
=MAX(IF((($C7:$G7=$A$17:$A$20)*($M7=1)),$C$17:$C$20))
BTW your Total hours worked % is not changing when I try to change the percentages in the column H through L.

Edit: I could not get your last post can you upload what you have done.

Thanks
 
Nebu,

Thanks. If I copy the formula and paste for employee A, it comes as 0, however since the total hours are 100% it should give 1400 as store no 251 from the group as per the icnentive plan is eligible/

Inscenario B, yes the total hours worked is 100%, however in the incentive plan stores 350 & 351 both are eligible for an incentive. As the employee has worked in store 350 for 60% of the time, they should get 1500.

I do not think the formula is capturing the % of hours in column H to L
 
You must log in or register to reply here.
Back
Top