| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Converting currency from US Dollar into Canadian dollars from column A to Column B | Excel Worksheet Functions | |||
| How do I sum dollars in one column based on dates in another? | New Users to Excel | |||
| I wish to make a formula that adds dollars in 1 column if a date . | Excel Worksheet Functions | |||
| Adding total dollars based on specific data from another column | Excel Discussion (Misc queries) | |||
| converting #dollars into word dollars? | Excel Worksheet Functions | |||
Linear Mode
