Formula to CAP another value

[Kcroft9]

Hi,

First of all I'm not sure if this needs to be a formula or VBA so apologies.

I'm looking for some help with a formula that references others cells to calculate how many temporary staff are required based on capping an overtime factor at 17%. I look at how many people are required vs how many available staff. Then from that I calculate overtime hours required to meet a plan. this is the value I want to cap at 17%. for example if it says 25% overtime is required, then I would like this formula in row 60 (temporary Labour) to increase until that % values reduces to 17%. I'm not exactly sure how to explain it clearly enough so I have attach a test document to show my sheet and what the expected results are, along with some description. (Don't worry I have used Random numbers)

thanks in advance for any support,

KCroft

[XLent]

there are some smart folk about who could no doubt do this very simply - but one immediate question for you

when you determine available HC you're subtracting / excluding # trainers
yet, when you determine the overtime % (row 71) you're including trainers as though they're available.

the below suggestion assumes the above is incorrect - i.e. both calcs should be consistent

below assumes trainers should be excluded from available HC

C60: =MAX(0,CEILING(((C$57*7)-((C$59-SUM(C$61:C$64))*7*1.17))/(7*1.17),1))
copied across

so, in short, I'm saying that in addition to applying above I think row 71 should exclude G64 prior to * 7 ?

with above changes / formulae in place you would get 0, 1, 4, 6, and 7 temp HC respectively - returning 0%, 17%, 16%, 16% & 17% OT

[Kcroft9]

Hi Thanks for the reply.

I'm including trainers in the overtime % calculation as I am saying overtime is done outside working hours but training is done only during normal working hours. so for overtime trainers would be available to produce. if that makes sense.

I tried your formula which works a treat if exclude trainers from the % calculation. if I make this modification to your formula, so sum(C$61:C$63)) it returns me a 0. does the whole structure of the formula need to change with what I said above regarding trainers?

Thanks,

[XLent]

Hi, so, perhaps the below amendment would suffice?

C60: =MAX(0,CEILING(((C57*7)-SUM((C59-SUM(C61:C64))*7*1.17,C64*7*0.17))/(7*1.17),1))
copied across

the above basically just establishes amount of available hours using existing HC {incl. max of 17% OT for Trainers}

obviously the reference to 17% could be modified to cell reference; I missed that.

[XLent]

updated above for B74 requirement (flexing %)

=MAX(0,CEILING(((C57*7)-SUM((C59-SUM(C61:C64))*7*(1+$B$74),C64*7*$B$74))/(7*(1+$B$74)),1))