Additions of Round Function and Minus Sign at Once

[Khalil]

Hello Experts:

I have a data than contains more than 900 rows. All these data contain
two function / and *. Out of these around 4-5 hundred rows consist
decimals values.
I need to do two things with this data

1... Addition of round function to decimals values simultaneously
without using copy/Paste.

2...Addition of (- ) minus sign to rounded values at once without
using copy/Paste.

How couple of these things possible please advise


Thanks in advance

Khalil

 

[Bernard Liengme]

This needs explaining, please
In column A you have 900 numbers
What does this mean: "All these data contain two function / and * "
Does it mean these numbers result from formulas rather than typed values?
What does this mean: "Out of these around 4-5 hundred rows consist decimals
values"
Does it mean some values are integer, others are real (in the mathematical
sense of having fractional parts)?

What do you want to sum?
To sum all negative numbers in A1:A900, =SUMIF(A1:A900,">0")

best wishes

 

[Billy Liddel]

Khalil

You need to change each formula to include the ROUND function (2 dec
places?) then multiply each formula by -1. The only way I know is by macro.
To use the code below:

Press ALT + F11, choose Insert Module and Copy the macro. The return to the
sheet and select the formulas to be changed. Press ALT + F8, select the Macro
and click Run.

Make a copy of your workbook before running this in case I misunderstood.

Sub RoundFunctions()
' Rounds Selection function to 2 decimal places
' and multiplies the result by - 1

Dim c, frm As String
Dim frm2 As String
Dim frm3 As String
Dim frm4 As String

frm2 = "=Round("
frm3 = ",2)*-1"

For Each c In Selection

If c.HasFormula Then
' get the formula
frm = c.Formula
'remove "=" at start of formula
frm = Right(frm, Len(frm) - 1)
frm4 = frm2 & frm & frm3
'change the formula
c.Formula = frm4

End If

Next c

End Sub


Regards
Peter Atherton

 

[Bernard Liengme]

And =SUMIF(A1:A900,"<0") will sum all the negative numbers
To round (shown her rounding to 2 places) the answer use
=ROUND(SUMIF(A1:A900,"<0"),2)

 

[Shane Devenshire]

Hi,

As you can see from your answers we are not clear about your question. why
not show us a small sample of data and what result you would like from it.

Let's suppose you want to round all the number in the column to 2 decimal
places and then to sum the results and show it as negative? If so you can
use the following formula:

=-SUMPRODUCT(ROUND(A1:A900,2))

 

[joeu2004]

I need to do two things with this data

It sounds like you are asking for a way to edit formulas over a large
range without having to resort to manual steps. In other words, you
want to change:

=expression

to

=-round(expression,0)

Select the cells to be changed (see comments below), then execute the
following macro:

Sub EditIt()
Dim form As String
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Selection
If Application.IsNumber(cell) Then
form = cell.formula
If Left(form,1) = "=" Then form = Right(form,Len(form)-1)
cell.formula = "=-round(" & form & ",0)"
End If
Next
Application.ScreenUpdating = True
End Sub

To enter the macro, press alt-F11 to open the VBE, then click Insert >
Module. Copy-and-paste the text of the macro above into the window
that should open on the right. Be sure the desired cells are selected
in the worksheet, then in the VBE, put the cursor within the macro and
press F5.

Some comments, if I may ....

Out of these around 4-5 hundred rows consist
decimals values.

I think you are trying to say that 400-500 cells display values that
have decimal fractions, whereas the remaining 400-500 cells appear to
have integer values.

The operative word is "appear". If all of the cells have formulas,
not constants, the actual value might not be exactly an integer value,
even if it appears to be an integer when formatted to the maximum
number of decimal places for 15 "significant" digits.

For example, the value 3.01 might appear as 3.0 because of cell
formatting. And the value 3+2^-51 will appear as "3." followed by 14
zeros, but its internal representation is not identical to 3. The
latter may or may not cause problems in some circumstances.

So it is prudent to round all formulas that might result in non-
integer values with the some numbers. For example, if the formula is
=A1/A2, and that results in exactly 2 only because A1 is 16 and A2 is
8, it would be more robust and prudent to change the formula to =round
(A1/A2,0) so that you get the desired result even if you change A1 or
A2.

For this reason, I suggest that you apply the editing macro above to
all 900 cells.


----- original posting -----

 

[joeu2004]

PS ....


If you want to avoid results like =-ROUND(3,0) and if you literally
want to prefix "-" only to formulas where ROUND(...,0) was added (that
is, you do not want to change 3 to -3), the following might do a
better job for you.


Sub EditIt()
Dim form As String
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Selection
If Application.IsNumber(cell) Then
form = cell.formula
If Left(form,1) = "=" Or Int(cell) <> cell Then
If Left(form,1) = "=" Then form = Right(form,Len(form)-1)
cell.formula = "=-round(" & form & ",0)"
End If
End If
Next
Application.ScreenUpdating = True
End Sub


If you want to change 3 to -3 as well, the following might work for
you.


Sub EditIt()
Dim form As String
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Selection
If Application.IsNumber(cell) Then
form = cell.formula
If Left(form, 1) = "=" Then
form = Right(form, Len(form) - 1)
cell.formula = "=-round(" & form & ",0)"
ElseIf Int(cell) <> cell Then
cell.formula = "=-round(" & form & ",0)"
Else
cell.formula = "-" & form
End If
End If
Next
Application.ScreenUpdating = True
End Sub


----- original posting -----

 

[Khalil]

Dear All:

My problem have been sold through below macros. I am thankful to all
of you who helped me in solving my problem.

kind regards
Khalil