Hey there,

This may sound like an odd one but I will try and be clear.

If we have a column of values that represent working days of the week I need to be able to convert them in order to sum them as a whole number or half (it's about assigning resources).

values can be 2.5 or 5 or 6 or 7.

If a cell = 2.5 then it needs to be .5 if it = 5 or 6 or 7 then it is 1.

So when you sum that you get the total time someone is working on a project, the 2.5 to .5 means they are working on 2 projects.

A column of 

2.5

5

5

6

 

would end up as 

.5

1

1

1

with a total of 3.5

I have to do this for a large number of business units where each column represents a week of the year.

I was looking up some crazy nesting of formula with O365 and hoping that there is a way to dynamically do a conversion of a value in a cell to come up with a total at the bottom of a range of cells.

 

Thanks.