If & mround functions

[katrichards15]

Hi

Since my last post I have a further query I'm hoping someone might be able to help me with please

The example scenario:
Person 1 has 26 envelopes to open
Person 2 has 15 envelopes to open

Person 1 and Person 2 both work 5 hours per day each

The spreadsheet will capture Target figures (work to be processed) and Actual figures (work that was processed), for each hour

I need the the spreadsheet to do two things:

1. Distribute individual's workload across the 5 hour period - this would be the Target figure
2. Automatically update Target figures according to the Actual figures.

• If actual figures are lower than the target for that hour, the remaining Target figures would increase
• If actual figures are higher than the target for that hour, the remaining Target figures would decrease

My example below (first cell is A1):

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Daily[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[/TR]
[TR]
[TD]Person1[/TD]
[TD]26[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person2[/TD]
[TD]15[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If Person1 over-achieves their first target of 6 and opens 7 envelopes I would need the remaining target figures for Person 1 to automatically adjust to show something like this (amendment in red) after the Actual figure of 7 has been entered:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Daily[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[/TR]
[TR]
[TD]Person1[/TD]
[TD]26[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person2[/TD]
[TD]15[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I initially calculated the target rate using: MROUND($B$2/5,1)

and then expanded the formula to this (this is in what should be E2):

IF(D2<c2,(c2-d2)+(mround($b$2 5,1)),(c2-d2)+(mround($b$2="" 5,1)))<="" font="">
<c2,(c2-d2)+(mround($b$2 5,1)),(c2-d2)+(mround($b$2="" 5,1)))

It does work but if there haven't been any figures entered in Actual (as it might be the start of the day) then my table looks like the below and this isn't what I want it to look like!

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Daily[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[TD]Target[/TD]
[TD]Actual[/TD]
[/TR]
[TR]
[TD]Person1[/TD]
[TD]26[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD]-24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person2[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Does anyone know where I might be going wrong?

Hope I have explained this clearly

Thanks in advance, Kat</c2,(c2-d2)+(mround($b$2></c2,(c2-d2)+(mround($b$2>

 

[katrichards15]

For some reason I can't post the second fomula but it looked something like this:
IF (D2< C2, (C2-D2) + (MROUND (B2/5,1 )), (C2 - D2) + (MROUND( B2/5,1 )))





<c2,(c2-d2)+(mround($b$2 5,1)),(c2-d2)+(mround($b$2="" 5,1)))<="" html="">
</c2,(c2-d2)+(mround($b$2>

 

[Mackers]

Hi Kat

I would use the following, with B2 being your daily target and D2, F2, H2, J2, L2 containing your Actual opened:

First target =ROUND(B2/5, 0)
Second target =ROUND((B2-D2)/(5-(COUNTA(D2))), 0)
Third target =ROUND((B2-D2-F2)/(5-(COUNTA(D2,F2))), 0)
Fourth target =ROUND((B2-D2-F2-H2)/(5-(COUNTA(D2,F2,H2))), 0)
Final target =ROUND((B2-D2-F2-H2-J2)/(5-(COUNTA(D2,F2,H2,J2))), 0)

Edit: I had a brain fart and got it wrong - check it now.

Hope that helps.

Mackers