# Round range of cells to equal another cell

#### Ace71425

##### Board Regular
Hello...I have let's just say 5 cells that are .37, .22, 1.35, .50, .56 which equals the value of another cell in this case 4. Now I need those 5 cells rounded up or down to the nearest .25 but I still need them to equal the other cell 4. How do I put in a round formula that rounds some of the cells conditionally so that the end result equals 4?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
... I have the concept in my mind, but can't get to formula.
Conditional MROUND where the test looks at the SUM(MROUND(Range,0.25) and compare to Sum(Range). If error and item evaluated is MAX value, then MROUND value plus/minus rounding error.
ps. Sum of (.37, .22, 1.35, .50, .56 ) = 3

In your example, rounding all of the cells up to the nearest 0.25 still only totals 3.5.

Yeah its a toughy it's a break down of hours that an employee is working to sign the time card. So they work 8 hours and it breaks down your tasks to like 1.35 2.86 .54 etc. But the timecard only takes multiples of .25 so you kinda have to over or under round some values to get to 8...I was trying to find a way to make excel do it so at least it's uniform with everybody

And I'm sorry all those numbers equal 3 I think but yall get the point lol

Yeah its a toughy it's a break down of hours that an employee is working to sign the time card. So they work 8 hours and it breaks down your tasks to like 1.35 2.86 .54 etc. But the timecard only takes multiples of .25 so you kinda have to over or under round some values to get to 8...I was trying to find a way to make excel do it so at least it's uniform with everybody

Why the breakdown of the task? For what end or goal?
Your are doing rounding of rounding...? That will create some dubious scenarios.

One way:

 Row\Col A​ B​ C​ D​ 1​ Project​ Inp​ Out​ ​ 2​ Project1 0.92​ 1.00​ C2: =MROUND(B2 / (\$B\$9 - SUM(B\$1:B1)) * (\$B\$9 - SUM(C\$1:C1)), 0.25) 3​ Project2 2.02​ 2.00​ 4​ Project3 0.97​ 1.00​ 5​ Project4 1.18​ 1.25​ 6​ Project5 1.05​ 1.00​ 7​ Project6 1.22​ 1.25​ 8​ Project7 0.64​ 0.50​ 9​ Total​ 8.00​ 8.00​

Shouldn't your begin/end times be rounded to the nearest 0:15 before calculating duration?
(MROUND(F18,RoundTime)-MROUND(E18,RoundTime))
Where round time is your interval.
15 minute interval = 0.010416667

Last edited:
oops. Convert to base 24 hour..
(MROUND(F18,RoundTime)-MROUND(E18,RoundTime))*24

You can try this.
This will round up to the nearest .25

Code:
``=CEILING(SUM("yourcells"),0.25)``

Kinda lost on the cell equaling four.

Replies
10
Views
498
Replies
8
Views
263
Replies
0
Views
162
Replies
1
Views
332
Replies
14
Views
401

1,196,306
Messages
6,014,565
Members
441,828
Latest member
cofracr

### 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.

### Which adblocker are you using?

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

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