Percent and dollars in Same Column

  • Thread starter Thread starter Wanna Learn
  • Start date Start date
W

Wanna Learn

Hello
I have column M, in this column there are only 2 numbers either a 5 or a 1
.. Column P has numbers/values. Now if the value in column M is a 5 that
means that the value in column P should equal to dollars. If the value in
column M is a 1 then the value in column P should equal % e.g Column P =
5 then column O should read $125.10 -Column P = 1 then value in column O
= 5% Whatever value is in column P should either be a dollar value or a %
value. Is there a formula to automate this ? I have about 300 rows and
it is taking forever, and this is a monthly report thank you in advance
 
You can try this formula in O1, but it will result in values that are
Text:

=IF(M1=5,TEXT(P1,"$0.00"),TEXT(P1,"0.0%"))

Format the cell as General, then copy this down as required. The
format strings could be more complex, depending on how large your
dollar values are going to be.

Hope this helps.

Pete
 
Thanks Pete It works with the dollar values but if it is a percent I get
the wrong %example column M = 1and column P has a value of 10 with this
formula the answer is 1000.00% it should read 10% thanks
 
So presumably you realise that you need to change Pete's formula to
=IF(M1=5,TEXT(P1,"$0.00"),TEXT(P1*1%,"0.0%")) ?
 
It's because you are not using percentage but rather 10 instead
Maybe

=IF(M1=5,TEXT(P1,"$0.00"),TEXT(P1/100,"0.0%"))


--


Regards,


Peo Sjoblom
 
Thanks for feeding back - I see you have two replies on how to correct
the percentages.

Pete
 
Back
Top