Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
smoore
 
Posts: n/a
Default Force function to show positive or negative number?

I'm using the following function in a spreadsheet, and it works fine as
long as the number is a positive number. However, if the number is a
negative, I have to add a negative sign to the multiple criteria of the
MROUND function. Is there a work around that would make this function
show both positive or negative numbers?

=IF(ISERROR(MROUND(D2*0.5,0.01)," ",MROUND(D2*.05,0.01))

Changed =IF(ISERROR(MROUND(D2*0.5,-0.01)," ",MROUND(D2*.05,-0.01))


Thanks

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JE McGimpsey
 
Posts: n/a
Default Force function to show positive or negative number?

one way:

=ROUND(D2*5,0)/100

In article .com,
"smoore" wrote:

I'm using the following function in a spreadsheet, and it works fine as
long as the number is a positive number. However, if the number is a
negative, I have to add a negative sign to the multiple criteria of the
MROUND function. Is there a work around that would make this function
show both positive or negative numbers?

=IF(ISERROR(MROUND(D2*0.5,0.01)," ",MROUND(D2*.05,0.01))

Changed =IF(ISERROR(MROUND(D2*0.5,-0.01)," ",MROUND(D2*.05,-0.01))


Thanks

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
smoore
 
Posts: n/a
Default Force function to show positive or negative number?

Sorry, but I can't get this one to work for me.

When I posted my original question I mis-typed my function. This is a
corrected version.

=IF(ISERROR(MROUND(D2*0.5,0.01))," ",MROUND(D2*0.5,0.01))

Now assuming that D2 is $6897.75, my function results in $3448.88 which
is correct, but only works if D2 is a positve number.

=ROUND(D2*5,0)/100 yields $344.89




I tried =ROUND(D2*5,0)/10 but this tields $3448.90

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
smoore
 
Posts: n/a
Default Force function to show positive or negative number?

Looks, like we've gotten there now. What I finaly found to work stems
from your last suggestion.

=IF(ISERROR(D2/2,2)),"",ROUND(D2/2,2))

This leaves me a clean worksheet if there are no figures in D2 where
before if D2 was still empty I had #value errors. This works perfectly.
Thank you very much for your help.

Scotty

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
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
Using the Indirect function with a sheet number instead of a sheet name JDB Excel Worksheet Functions 5 December 31st 05 04:03 PM
Add sequences of positive then negative numbers judoist Excel Discussion (Misc queries) 6 November 26th 05 06:51 AM
Change Number to a negative in VBA Noemi Excel Discussion (Misc queries) 2 November 22nd 05 12:58 PM
#VALUE in cell but pop up function box show right number Ted Dalton Excel Discussion (Misc queries) 1 December 14th 04 04:15 PM


All times are GMT +1. The time now is 01:04 PM.

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

About Us

"It's about Microsoft Excel"