| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting dollar values in text
Hello, I have Excel 2003 and Windows XP Pro.
I use this formula and it works exactly like I want it to except:: =IF(AC8<AC7,"YES Credit for " & "$" & AC8,"NO Max Credit for " & "$" & AC7) AC8 and AC7 are both currency values and I get formatting of it like this $133 $133.1 $133.10 I would always like to return a full currency value like the $133.10 but can't figure it out. The two cells are both formatted in currency and display two decimal places Anyone have any ideas that it will format each time?? Thanks Bob R |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formatting dollar values in text
You may prefer to have a look in the help index for DOLLAR
-- Don Guillett Microsoft MVP Excel SalesAid Software "LabrGuy Bob R" wrote in message ... Hello, I have Excel 2003 and Windows XP Pro. I use this formula and it works exactly like I want it to except:: =IF(AC8<AC7,"YES Credit for " & "$" & AC8,"NO Max Credit for " & "$" & AC7) AC8 and AC7 are both currency values and I get formatting of it like this $133 $133.1 $133.10 I would always like to return a full currency value like the $133.10 but can't figure it out. The two cells are both formatted in currency and display two decimal places Anyone have any ideas that it will format each time?? Thanks Bob R |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formatting dollar values in text
Hi Bob,
=IF(AC8<AC7,"YES Credit for "&TEXT(AC8,"$#,##0.00"),"NO Max Credit for "&TEXT(AC7,"$#,##0.00")) -- Kind regards, Niek Otten Microsoft MVP - Excel "LabrGuy Bob R" wrote in message ... | Hello, I have Excel 2003 and Windows XP Pro. | | I use this formula and it works exactly like I want it to except:: | | =IF(AC8<AC7,"YES Credit for " & "$" & AC8,"NO Max Credit for " & "$" & AC7) | | AC8 and AC7 are both currency values and I get formatting of it like this | $133 | $133.1 | $133.10 | | I would always like to return a full currency value like the $133.10 but | can't figure it out. The two cells are both formatted in currency and | display two decimal places | | Anyone have any ideas that it will format each time?? | Thanks | Bob R | | | |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formatting dollar values in text
On Sat, 10 Nov 2007 09:10:52 -0500, "LabrGuy Bob R"
wrote: Hello, I have Excel 2003 and Windows XP Pro. I use this formula and it works exactly like I want it to except:: =IF(AC8<AC7,"YES Credit for " & "$" & AC8,"NO Max Credit for " & "$" & AC7) AC8 and AC7 are both currency values and I get formatting of it like this $133 $133.1 $133.10 I would always like to return a full currency value like the $133.10 but can't figure it out. The two cells are both formatted in currency and display two decimal places Anyone have any ideas that it will format each time?? Thanks Bob R Use the TEXT worksheet function to format the result: =IF(AC8<AC7,"YES Credit for " & text(ac8,"$#,##0.00"),"NO Max Credit for " & text(ac7,"$#,##0.00")) or similar --ron |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formatting dollar values in text
Many thanks to all of you for helping me out, my problem is solved once
again. Appreciate the assistance BOB "LabrGuy Bob R" wrote in message ... Hello, I have Excel 2003 and Windows XP Pro. I use this formula and it works exactly like I want it to except:: =IF(AC8<AC7,"YES Credit for " & "$" & AC8,"NO Max Credit for " & "$" & AC7) AC8 and AC7 are both currency values and I get formatting of it like this $133 $133.1 $133.10 I would always like to return a full currency value like the $133.10 but can't figure it out. The two cells are both formatted in currency and display two decimal places Anyone have any ideas that it will format each time?? Thanks Bob R |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formatting dollar values in text
=IF(A8<A7,"YES Credit for "&DOLLAR(A8,2),"NO Max Credit for "&DOLLAR(A7,2))
"LabrGuy Bob R" wrote: Hello, I have Excel 2003 and Windows XP Pro. I use this formula and it works exactly like I want it to except:: =IF(AC8<AC7,"YES Credit for " & "$" & AC8,"NO Max Credit for " & "$" & AC7) AC8 and AC7 are both currency values and I get formatting of it like this $133 $133.1 $133.10 I would always like to return a full currency value like the $133.10 but can't figure it out. The two cells are both formatted in currency and display two decimal places Anyone have any ideas that it will format each time?? Thanks Bob R |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formatting dollar values in text
=IF(AC8<AC7,"YES Credit for " & text(ac8,"$#,##0.00"),"NO Max Credit for " &
text(ac7,"$#,##0.00")) ac8-ac7,"$#,##0.00 =IF(AC8<AC7,"YES Credit for "&TEXT(AC8,"$#,##0.00"),"NO Max Credit for "&TEXT(AC7,"$#,##0.00")) AC8-AC7 =IF(A8<A7,"YES Credit for "&DOLLAR(A8,2),"NO Max Credit for "&DOLLAR(A7,2)) A8-A7 All three worked and very well, EXCEPT I now have formulas that use the addition or subtraction of A8-A7 OR A8+A7, as I indicated above. I can't get it to work probably because I would think I need to make the calculation in another cell and only reference that cell. I've tried to put it in the formula like I did above but it won't work. Probably because it's text and I can perform math on text... Any ideas. Thanks BOB |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formatting dollar values in text
I don't quite understand. You could still subtract AC7 from AC8 (or is
it A7 from A8 ?), as they are not affected by the formula. You can have something like: .... & TEXT(AC8-AC7,"#,##0.00") ... within your formula if you need to subtract them and build them into a text message as before. Hope this helps. Pete On Nov 10, 10:01 pm, "LabrGuy Bob R" wrote: =IF(AC8<AC7,"YES Credit for " & text(ac8,"$#,##0.00"),"NO Max Credit for " & text(ac7,"$#,##0.00")) ac8-ac7,"$#,##0.00 =IF(AC8<AC7,"YES Credit for "&TEXT(AC8,"$#,##0.00"),"NO Max Credit for "&TEXT(AC7,"$#,##0.00")) AC8-AC7 =IF(A8<A7,"YES Credit for "&DOLLAR(A8,2),"NO Max Credit for "&DOLLAR(A7,2)) A8-A7 All three worked and very well, EXCEPT I now have formulas that use the addition or subtraction of A8-A7 OR A8+A7, as I indicated above. I can't get it to work probably because I would think I need to make the calculation in another cell and only reference that cell. I've tried to put it in the formula like I did above but it won't work. Probably because it's text and I can perform math on text... Any ideas. Thanks BOB |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formatting dollar values in text
That's funny, just as I went to post that I found there was an issue with my
formatting and that the probelm wasn't really real you confimed what I have read other places. thanks for taking the time, it's appreciated. The issue was at my end so problem fixed.... Thanks BOB "Pete_UK" wrote in message ups.com... I don't quite understand. You could still subtract AC7 from AC8 (or is it A7 from A8 ?), as they are not affected by the formula. You can have something like: ... & TEXT(AC8-AC7,"#,##0.00") ... within your formula if you need to subtract them and build them into a text message as before. Hope this helps. Pete On Nov 10, 10:01 pm, "LabrGuy Bob R" wrote: =IF(AC8<AC7,"YES Credit for " & text(ac8,"$#,##0.00"),"NO Max Credit for " & text(ac7,"$#,##0.00")) ac8-ac7,"$#,##0.00 =IF(AC8<AC7,"YES Credit for "&TEXT(AC8,"$#,##0.00"),"NO Max Credit for "&TEXT(AC7,"$#,##0.00")) AC8-AC7 =IF(A8<A7,"YES Credit for "&DOLLAR(A8,2),"NO Max Credit for "&DOLLAR(A7,2)) A8-A7 All three worked and very well, EXCEPT I now have formulas that use the addition or subtraction of A8-A7 OR A8+A7, as I indicated above. I can't get it to work probably because I would think I need to make the calculation in another cell and only reference that cell. I've tried to put it in the formula like I did above but it won't work. Probably because it's text and I can perform math on text... Any ideas. Thanks BOB |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formatting dollar values in text
Okay, thanks for feeding back.
Pete On Nov 11, 12:41 am, "LabrGuy Bob R" wrote: That's funny, just as I went to post that I found there was an issue with my formatting and that the probelm wasn't really real you confimed what I have read other places. thanks for taking the time, it's appreciated. The issue was at my end so problem fixed.... Thanks BOB "Pete_UK" wrote in message ups.com... I don't quite understand. You could still subtract AC7 from AC8 (or is it A7 from A8 ?), as they are not affected by the formula. You can have something like: ... & TEXT(AC8-AC7,"#,##0.00") ... within your formula if you need to subtract them and build them into a text message as before. Hope this helps. Pete On Nov 10, 10:01 pm, "LabrGuy Bob R" wrote: =IF(AC8<AC7,"YES Credit for " & text(ac8,"$#,##0.00"),"NO Max Credit for " & text(ac7,"$#,##0.00")) ac8-ac7,"$#,##0.00 =IF(AC8<AC7,"YES Credit for "&TEXT(AC8,"$#,##0.00"),"NO Max Credit for "&TEXT(AC7,"$#,##0.00")) AC8-AC7 =IF(A8<A7,"YES Credit for "&DOLLAR(A8,2),"NO Max Credit for "&DOLLAR(A7,2)) A8-A7 All three worked and very well, EXCEPT I now have formulas that use the addition or subtraction of A8-A7 OR A8+A7, as I indicated above. I can't get it to work probably because I would think I need to make the calculation in another cell and only reference that cell. I've tried to put it in the formula like I did above but it won't work. Probably because it's text and I can perform math on text... Any ideas. Thanks BOB- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| convert text to dollar | Excel Discussion (Misc queries) | |||
| How do I assign dollar value to text | New Users to Excel | |||
| formatting text on chart for specific values | Charts and Charting in Excel | |||
| Force values-only when pasting text to keep formatting | Excel Discussion (Misc queries) | |||
| Dollar text | Excel Discussion (Misc queries) | |||
Linear Mode
