If & mround functions

katrichards15

New Member
Joined
Jun 24, 2015
Messages
4
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):


DailyTargetActualTargetActualTargetActualTargetActualTargetActual
Person12665555
Person21533333

<tbody>
</tbody>


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:

DailyTargetActualTargetActualTargetActualTargetActualTargetActual
Person126674555
Person21533333

<tbody>
</tbody>



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!

DailyTargetActualTargetActualTargetActualTargetActualTargetActual
Person1265101520-24
Person215

<tbody>
</tbody>


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>
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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>
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top