Percent and dollars in Same Column
 

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

Percent and dollars in Same Column
 

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

On Dec 19, 4:36 pm, Wanna Learn
wrote:
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

Percent and dollars in Same Column
 

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
"Pete_UK" wrote:

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

On Dec 19, 4:36 pm, Wanna Learn
wrote:
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

Percent and dollars in Same Column
 

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%")) ?
--
David Biddulph

"Wanna Learn" wrote in message
...
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
"Pete_UK" wrote:

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

On Dec 19, 4:36 pm, Wanna Learn
wrote:
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

Percent and dollars in Same Column
 

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


"Wanna Learn" wrote in message
...
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
"Pete_UK" wrote:

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

On Dec 19, 4:36 pm, Wanna Learn
wrote:
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

Percent and dollars in Same Column
 

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

Pete

On Dec 19, 5:52 pm, Wanna Learn
wrote:
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



"Pete_UK" wrote:
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

On Dec 19, 4:36 pm, Wanna Learn
wrote:
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- Hide quoted text -

- Show quoted text -