CODEWITHSUNDEEP
excelexcel-formula
Louise
Louise

Reputation: 1

ROUND IF function

I want a function that will round a number differentially depending on the value in another cell. If my values are, for examples, 0.25 97 2.75 I want the middle number to donate 0.25 to each cell to round them up to the nearest 0.5 so they are 0.5 96.5 3.0. But if the numbers are 0.5 98 1.5 they can stay as they are but I would want 0.75 96.25 3.0 I would want it to become 1.0 96.0 3.0. I've tried ROUND IF but I can't get it quite right.

I've tried FLOOR, CEILING and ROUND IF but I just get get it to do exactly what I want. 

=FLOOR(D26,0.5)

=ROUNDDOWN(D26,0.5)IF(C26, E26,.25,.75)

=ROUNDUP(C26,0.5)*IF(D98,0.25,0.75)

Upvotes: 0

Views: 1044

Answers (2)

JvdV
JvdV

Reputation: 75840

Here is an idea for you:

enter image description here

Formula in D2:

=CEILING.MATH(A2,0.5)

Formula in E2:

=FLOOR.MATH(SUM(A2:C2)-(D2+F2),0.5)

Formula in F2:

=CEILING.MATH(C2,0.5)

Edited E2 formula to take into consideration the rounding when the middle number would be stuck on either .25 or .75

Upvotes: 3

godspeed23.carl
godspeed23.carl

Reputation: 101

Assuming that the 3 numbers are in D4, D5 and D6, then the following formulas can be applied in say E4 - E6:

=ROUND(D4*2,0)/2
=D5-(E4-D4)-(E6-D6)
=ROUND(D6*2,0)/2

Upvotes: 0

Related Questions