Sum in selected cell

leaper1981

New Member
Joined
Aug 16, 2019
Messages
14
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.

Thanks again for your Genius!
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
36,191
Office Version
365
Platform
Windows
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
Joined
Aug 16, 2019
Messages
14
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
Joined
Jun 12, 2014
Messages
36,191
Office Version
365
Platform
Windows
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
Joined
Aug 16, 2019
Messages
14
That's perfect!

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

Dan
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,191
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

leaper1981

New Member
Joined
Aug 16, 2019
Messages
14
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
Joined
Jun 12, 2014
Messages
36,191
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,191
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,090,552
Messages
5,415,232
Members
403,574
Latest member
stdar2

This Week's Hot Topics

Top