Round range of cells to equal another cell

[Ace71425]

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?

 

[SpillerBD]

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

 

[shg]

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

 

[Ace71425]

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

 

[Ace71425]

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

 

[SpillerBD]

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.

 

[shg]

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​

 

[SpillerBD]

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

 

[SpillerBD]

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

 

[bwalker1984]

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

=CEILING(SUM("yourcells"),0.25)

Kinda lost on the cell equaling four.