Link to home
Start Free TrialLog in
Avatar of Hannah
Hannah

asked on

Excel formula

Hello, in the attached excel file, in the tab "computer sales" I am trying to alter the formula in K31 (highlighted in yellow) so that whether or not the numbers are negative or positive, they will still show in the results. For example, I would like the formula in K31 to show results like K33 (highlighted in orange) instead of how the formula currently leaves out "South America: -67k" because of the negative number.

I would also like a similar formula in K21 that would not discriminate based on the number being positive or negative. Thanks.
EE-Example-Regions.xlsx
Avatar of Norie
Norie
Hannah

All you should need to do is get rid of the IF statements:

=J24&": "&TEXT(K24/1000,"+0k")&CHAR(10)&J25&": "&TEXT(K25/1000,"+0k")&CHAR(10)&J26&": "&TEXT(K26/1000,"+0k")
Avatar of Hannah
Hannah

ASKER

thanks, that returns the following, is there a way to get "+" or"-" only depending on if it is negative or positive? (rather than -+67k)

Europe: +67k
North America: +67k
South America: -+67k
Avatar of Norie
Norie
Ah, didn't quite catch that part.

To do that change the string used in the TEXT formula to provide an option for negative values:

=J24&": "&TEXT(K24/1000,"+0k;-0k")&CHAR(10)&J25&": "&TEXT(K25/1000,"+0k;-0k")&CHAR(10)&J26&": "&TEXT(K26/1000,"+0k;-0k")
Avatar of Hannah
Hannah

ASKER

Thanks, in the attached example, in the computer sales tab, I'd like a cleaner version of cell K10 (highlighted in yellow). I'd like it to have an output of E10 (highlighted in green) - is there a way to update the formulas in K10, K5 - K7, or E51 - E58?
EE-Example-Regions.xlsx
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.