Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Using the Indirect function with a sheet number instead of a sheet name | Excel Worksheet Functions | |||
Add sequences of positive then negative numbers | Excel Discussion (Misc queries) | |||
Change Number to a negative in VBA | Excel Discussion (Misc queries) | |||
#VALUE in cell but pop up function box show right number | Excel Discussion (Misc queries) |