Hi there

I'm working in excel and I have a series of basic figures (rounded to whole numbers) that i need to have enclosed by brackets but also need a + sign to be included in the brackets for positive figures and a - for negative figures. I can manage to get brackets around everything by itself without the plus signs included (negative figures still have the - sign which is whats needed) or i can get the figures with the plus and minus sign but not the brackets........ but can't seem to get both to work together. Anyone have any suggestions?

Thanks in advance

Perhaps it would help if you posted an example of your data and what you are looking for. You say that you have

"basic figures (rounded to whole numbers) ".I don't know what a "basic figure" is.

Are they rounded via a formula? e.g. =ROUND(A1,0)

How are you currently getting the brackets around the numbers?

Do you need to use these "bracketed numbers" as

numbersto do further calculations with or is it OK if they are text values?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

hi there

thanks for replying

the figures are just small values i.e -9, 13, 7, -21 etc so there are no decimal points they are rounded to the nearest whole figure. Its not rounded with any formula just simple formatted with zero decimal places. The figures can either be negative or positive and i need the appropriate symbol to be shown as well i.e +7 or -21 etc which i have managed to do by itself using custom formatting (using code +0;-0 )however i also need each of these figures to automatically have brackets put around them whether they are positive or negative (which again I have achieved by itself by using the cell formula code ="("&U29&")" where u29 is the cell the information is linked to which basically just has figures like 7, or -21 in it (so the plusses don't show.) but can't seem to get the brackets and the symbols to both work at the same time ). - so what i really need is something like

(+7)

(-13)

(+3)

and so on.

When the figures have been formatted as required they won't be used for anymore calculations they are basically copied and inserted onto a graph and spaced out appropriately. So its basically just an automation of putting brackets round the figures and a sign to show whetehr its positive or negative as well that i need

Hope that explains it a bit more sorry if its a bit confusing its difficult to type into words exactly what i need to doCheers

=if(u29<0,"("&U29&")","(+"&U29&")") Assuming you want a + for a value of zero. I believe that since you are using formating to do your rounding, -0.00000001, would display as (-0). It may be worth yourwhile to substitute round(U29,0) as DerbyDad03 alluded to.

Its important to note that these are now Text entries and not numeric values. Which should be ok as you stated you are not doing any further caclculations with the information other than display.

Hi there

Thanks for the reply!

=if(u29<0,"("&U29&")","(+"&U29&")")That seems to have done the trick!!!!!! Thankyou ever so much - I wasted a whole morning trying to figure out the answer and would never have come to the correct formula - i'm so glad i stumbled across this website!!!!!!!

Just one final request before i leave you all in peace - in the above formula is there anyway to get the value of zero to come out without a plus or a minus - i.e just as 0? Not a problem if not but if that were possible it would completely automate the process that i need to doOnce again thankyou all for your time

Mark

=if(round(u29,0)=0,"("&round(u29,0)&")",if(round(u29,0)<0,"("&round(u29,0)&")","(+"&round(u29,0)&")")) I put the round in, as 0.0000001 is not equal to 0 until you round. This is simply an embedded if statement that first checks the zero condition then checks for positive or negative values.

OH gosh I need caffeine apparently. a simpler solution

=if(round(u29,0)<=0,"("&round(u29,0)&")","(+"&round(u29,0)&")"))

Simpler because your formating for the negative and zero case is actually the same as far as excel is concerned

=if(round(u29,0)=0,"("&round(u29,0)&")",if(round(u29,0)<0,"("&round(u29,0)&")","(+"&round(u29,0)&")"))

amazing! - Thankyou so much

- works perfectly

Cheers for all your help everyone and in particular kitty canine

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History