Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 11th 06, 11:53 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 53
Default Preserve negative numbers in following macro

Hi,

Ron gave me this Macro which does exactly what I want, except for one
small point I have just noticed.

////////////////////////////////////////////////////////////////////

Sub Progressive_Error()

Dim KeyCell As Range, AnswerCell As Range
Dim SuffixCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String
Dim Suffix As String


Set KeyCell = [q16]
Set AnswerCell = [r18]
Set SuffixCell = [r14]


'Note the quotes within the quotes.
Suffix = """" & SuffixCell.Text & """"


'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat


DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply
more DP


'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) & Suffix


'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0" & Suffix


acFmt = "+" & acFmt & ";-" & acFmt & ";0"


'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt


End Sub

///////////////////////////////////////////////////////////////////////////////////////////



In the following cells are the numbers I want to find the largest error
from.

0
10
15
-5
-3
-18

In the above numbers, 15 is the largest number, but -18 is the highest
error.

I use the MAXA function to remove the signs (as this is the only way I
know how to) and then I have 18 as my Answercell. The macro above, then
assigns a + sign to this Answercell value all the time, (quite rightly
because it is a positive number) but I have realised now that I need it
to recognise it as a negative number (as it sometimes is for the
highest error) and then the Answercell will reflect this as either a -
or + prefix.

Currently the macro formats the cell as +18.00g as Suffixcell is "g"
and Keycell is "0.00" DP but I need it to format it is -18.00g


Any advice greatly appreciated.

Cheers,

Aaron.


  #2   Report Post  
Old September 12th 06, 12:39 AM posted to microsoft.public.excel.worksheet.functions
JMB JMB is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,062
Default Preserve negative numbers in following macro

I assume you are using a formula in cell R18. The formatting macro worked
fine for me, you have to modify your formula to return -18. I used

=IF(ABS(MIN(P3:P6))MAX(P3:P6),MIN(P3:P6),MAX(P3:P 6))

Change the range to wherever your data is.


"Slashman" wrote:

Hi,

Ron gave me this Macro which does exactly what I want, except for one
small point I have just noticed.

////////////////////////////////////////////////////////////////////

Sub Progressive_Error()

Dim KeyCell As Range, AnswerCell As Range
Dim SuffixCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String
Dim Suffix As String


Set KeyCell = [q16]
Set AnswerCell = [r18]
Set SuffixCell = [r14]


'Note the quotes within the quotes.
Suffix = """" & SuffixCell.Text & """"


'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat


DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply
more DP


'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) & Suffix


'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0" & Suffix


acFmt = "+" & acFmt & ";-" & acFmt & ";0"


'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt


End Sub

///////////////////////////////////////////////////////////////////////////////////////////



In the following cells are the numbers I want to find the largest error
from.

0
10
15
-5
-3
-18

In the above numbers, 15 is the largest number, but -18 is the highest
error.

I use the MAXA function to remove the signs (as this is the only way I
know how to) and then I have 18 as my Answercell. The macro above, then
assigns a + sign to this Answercell value all the time, (quite rightly
because it is a positive number) but I have realised now that I need it
to recognise it as a negative number (as it sometimes is for the
highest error) and then the Answercell will reflect this as either a -
or + prefix.

Currently the macro formats the cell as +18.00g as Suffixcell is "g"
and Keycell is "0.00" DP but I need it to format it is -18.00g


Any advice greatly appreciated.

Cheers,

Aaron.


  #3   Report Post  
Old September 12th 06, 12:57 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 53
Default Preserve negative numbers in following macro

Hi JMB,

Thats the solution I wanted. It works 100% how I needed it to.

Thanks alot.

Aaron.
JMB wrote:
I assume you are using a formula in cell R18. The formatting macro worked
fine for me, you have to modify your formula to return -18. I used

=IF(ABS(MIN(P3:P6))MAX(P3:P6),MIN(P3:P6),MAX(P3:P 6))

Change the range to wherever your data is.


"Slashman" wrote:

Hi,

Ron gave me this Macro which does exactly what I want, except for one
small point I have just noticed.

////////////////////////////////////////////////////////////////////

Sub Progressive_Error()

Dim KeyCell As Range, AnswerCell As Range
Dim SuffixCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String
Dim Suffix As String


Set KeyCell = [q16]
Set AnswerCell = [r18]
Set SuffixCell = [r14]


'Note the quotes within the quotes.
Suffix = """" & SuffixCell.Text & """"


'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat


DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply
more DP


'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP) & Suffix


'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0" & Suffix


acFmt = "+" & acFmt & ";-" & acFmt & ";0"


'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt


End Sub

///////////////////////////////////////////////////////////////////////////////////////////



In the following cells are the numbers I want to find the largest error
from.

0
10
15
-5
-3
-18

In the above numbers, 15 is the largest number, but -18 is the highest
error.

I use the MAXA function to remove the signs (as this is the only way I
know how to) and then I have 18 as my Answercell. The macro above, then
assigns a + sign to this Answercell value all the time, (quite rightly
because it is a positive number) but I have realised now that I need it
to recognise it as a negative number (as it sometimes is for the
highest error) and then the Answercell will reflect this as either a -
or + prefix.

Currently the macro formats the cell as +18.00g as Suffixcell is "g"
and Keycell is "0.00" DP but I need it to format it is -18.00g


Any advice greatly appreciated.

Cheers,

Aaron.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
negative numbers jaxstraww Excel Discussion (Misc queries) 6 September 1st 06 09:40 PM
convert negative numbers to positive numbers and vice versa bill gras Excel Worksheet Functions 4 December 7th 05 01:39 AM
Add sequences of positive then negative numbers judoist Excel Discussion (Misc queries) 6 November 26th 05 05:51 AM
How can I change positive numbers to negative, i.e. change 50 to - godwingi Excel Discussion (Misc queries) 5 February 28th 05 05:41 PM
Converting negative numbers in a range of cells to zero Dede Excel Discussion (Misc queries) 3 January 14th 05 06:23 PM


All times are GMT +1. The time now is 04:40 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017