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

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Khalil" wrote in message
...
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

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




"Khalil" wrote:

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

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)
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Khalil" wrote in message
...
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

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))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Khalil" wrote:

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

On Mar 1, 2:28 am, Khalil wrote:
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 ....

You wrote:
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 -----

On Mar 1, 2:28*am, Khalil wrote:
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

PS ....

On Mar 1, 12:50 pm, I wrote:
On Mar 1, 2:28 am, Khalil wrote:
2...Addition of (- ) minus sign to rounded values

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 -----

On Mar 1, 12:50*pm, joeu2004 wrote:
On Mar 1, 2:28 am, Khalil wrote:

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 ....

You wrote:
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 -----

On Mar 1, 2:28*am, Khalil wrote:



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

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


On Mar 1, 6:38*pm, Billy Liddel
wrote:
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



"Khalil" wrote:
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- Hide quoted text -

- Show quoted text -