# Sum in selected cell

#### leaper1981

##### New Member
Hi again,

This is almost perfection!! Just one more thing (I know I said that before lol) Does it round up or down? eg: If the sum came out at 3.3, would it put 3.5 or would it put 3? I ask as usually we round it up not down.

Secondly if that could be problematic would it be possible for it to put the actual figure it comes out at (say 3.3465 or whatever) Then I could just look at all those figures and decide to round it up or down myself. It could be beneficial to see the exact figure I would have got on the calculator in the hours column just in case some jobs that are close to being the next number feel like they could do with adding an extra .5 on when I ***** the other factors we have for production.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Fluff

##### MrExcel MVP, Moderator
It currently rounds up to the nearest 0.5, if you don't want that use.
Code:
``         Cl.Offset(, 3).Value = Cl.Offset(, 7) * 1.1 / (Sp(0) * Sp(1)) / IIf(Cl.Offset(, 12) > 17, 5000, 7000)``

#### leaper1981

##### New Member
Hi me again,

Last one I promise! That looks great with it displaying the actual figure, would it be possible to add 1 onto the figures it is spitting out, I tried to alter the formula myself looking at your previous ones but its seemingly not as simple as just adding a 1 to the end (which is why you're the expert!) So if it currently comes out at say 3.8934......it adds an extra 1 to it and becomes 4.8934

After that I'll go away and leave you alone lol

Thanks again

#### Fluff

##### MrExcel MVP, Moderator
Try
Code:
``         Cl.Offset(, 3).Value = Cl.Offset(, 7) * 1.1 / (Sp(0) * Sp(1)) / IIf(Cl.Offset(, 12) > 17, 5000, 7000)[COLOR=#ff0000] + 1[/COLOR]``

#### leaper1981

##### New Member
That's perfect!

I cant thank you enough for all your time and effort.

Dan

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

#### leaper1981

##### New Member
Hi again,

You did this brilliant macro for me a few weeks ago & I was just wondering if it was possible to make a small change (maybe not)

Basically the macro below applies itself to all the cells column D, I was wondering if it would be possible for it to only apply to selected cells in row D rather than all. Thanks again, Dan.

Sub Hours()
Dim Cl As Range
Dim Sp As Variant
For Each Cl In Range("A5", Range("A" & Rows.Count).End(xlUp))
If IsNumeric(Cl.Offset(, 2)) And Cl.Offset(, 2) <> "" Then
Sp = Split(LCase(Cl.Offset(, 14)), "x")
Cl.Offset(, 3).Value = Cl.Offset(, 7) * 1.1 / (Sp(0) * Sp(1)) / IIf(Cl.Offset(, 12) > 17, 5000, 7000) + 1
End If
Next Cl
End Sub

#### Fluff

##### MrExcel MVP, Moderator
Change this
Code:
``For Each Cl In Range("A5", Range("A" & Rows.Count).End(xlUp))``
to
Code:
``For Each Cl In Selection``
But you need to select cells in Col A

#### leaper1981

##### New Member
You've done it again! Thanks thats perfect.

#### Fluff

##### MrExcel MVP, Moderator
Glad to help & thanks for the feedback