Bring number format with a formula

[kdibai]

Hello guys, someone knows how to do this?


I'm doing a sales dashboard that has a buttom to choose between two scenarios: currency and percentage. When the button of currency is activated, I have a formula that identifies that and bring the result in currency, the same happens for the percentage scenario when the other buttom is hitted.


=IF(Organized!$X$2=1,Organized!C107,IF(Organized!$X$2=2,Organized!C128))


This two has different number format and I cannot format the cell that is used by both. How do I keep them as numbers? (cause I could put a &"$" and &"%" but I would not have numbers anymore cause it would be transformed into text).


Many thanks in advance!

 

[SirJB7]

Hi, kdibai!


Analyzing your formula, I see this:

[pre]

=IF(Organized!$X$2=1,Organized!C107,IF(Organized!$X$2=2,Organized!C128))
--------------/ ------------/ ---------------------------------/
condition         if true                  if false
--------------/ ------------/
condition          ???

[/pre]
That's to say, first of all your second condition lacks one of the two parts, true or false.


Please specify all involved cells (missing included) with formats and contents (values not formatted), and include desired output formatting.


Regards!


PS: from a slighty southern place

 

[NARAYANK991]

Hi ,


I think what you are wanting to do , can be done by converting your values to text using the =TEXT function.


Instead of directly referring to the two values in C107 and C128 , use two helper cells , say E107 and E128 , which convert the numeric values in C107 and C128 using the following formulae :

[pre]

=IF(Organized!$B$2=1,Organized!E107,IF(Organized!$B$2=2,Organized!E128))
In E107 , have the following formula :

[pre][code]=TEXT(C107,"$ #.00")

[/pre]
In E128 , have the following formula :

=TEXT(C128,"#.00 %&#34[/code][/pre]
Narayan

 

[kdibai]

Hi,


SIRJB7, ty for answering. I found this formula akward too. But it worked. it bring the right value with the right condition. There's no false condition.


NARAYANK, yeah, that would be a solution, but I need them as number. Can think of something else? I tried VBA, but this dashboard is for a non Excel expert person and it would have to be run manually (by a button) and it's not that good in this case.

 

[NARAYANK991]

Hi ,


A dashboard is supposed to be for presentation purposes ; you don't need to have values there.


If at all you need the original values , you can always use the values from C107 and C128. Just use your original formula :

[pre]

=IF(Organized!$X$2=1,Organized!C107,IF(Organized!$X$2=2,Organized!C128))

[/pre]
in another cell , and use the value from that cell.


Otherwise , use Conditional Formatting , with the following rules :


1. =$E$8>1 ; use the CF Currency


2. =$E$8<=1 ; use the CF Percentage , and check the box "Stop If True"


Here E8 is the cell which has your original formula ( =IF(Organized!$X$2=1,Organized!C107,IF(Organized!$X$2=2,Organized!C128)) ).


Of course , you can use this method only if you are sure that there will be a clear distinction between the values where the Currency format will be applied , and those where the Percentage format will be applied.


Narayan