Finetune formula to get currency format?

I've managed to put together two different formulas from two different
sources and it seems to be working okay except in terms of the format
of the numbers.

The formula is this:

=IF(G2="?",TEXT(G2,"$0")&" ttl unknown",G2*I2)

The "$0" in there is a remnant from the original.  I don't know what
to do with it so I've left it in.

The purpose of this formula is to deal with situations where a list
price is unknown and a user puts a question mark in G2.  This formula
goes into the totals column but there is no total known so the result
will say:

"? ttl unknown"

Yet when there _is_ a price in G2, then the formula should return a
product, derived from G2 x I2.
And when that is the case, rather than see "? ttl unknown" in the cell
(without quotation marks, of course) it should see a dollar amount
with 2 decimal places.

A second column deals with a total after a discount and it looks like
this:

=IF(G2="?",TEXT(G2,"$0")&" ttl price unknown",J2*(O1/100))

Once I know how to display currency in the formula when there is a
dollar amount, I can then modify this second formula.

How can I achieve this through modifying the formula above?

Thanks!  :oD
0
StargateFan
5/26/2010 8:34:48 PM
excel 39879 articles. 2 followers. Follow

1 Replies
912 Views

Similar Articles

[PageSpeed] 51

Do you want to be able to calculate with the results?

I would use  =IF(G2="?","$0 ttl unknown",G2*I2)

Then format the cell as Currency.

For a Text only result use

=IF(G2="?","$0 ttl unknown",TEXT(G2*I2,"$#,##0.00"))


Gord Dibben  MS Excel MVP

On Wed, 26 May 2010 13:34:48 -0700 (PDT), StargateFan
<StargateFan@mailinator.com> wrote:

>I've managed to put together two different formulas from two different
>sources and it seems to be working okay except in terms of the format
>of the numbers.
>
>The formula is this:
>
>=IF(G2="?",TEXT(G2,"$0")&" ttl unknown",G2*I2)
>
>The "$0" in there is a remnant from the original.  I don't know what
>to do with it so I've left it in.
>
>The purpose of this formula is to deal with situations where a list
>price is unknown and a user puts a question mark in G2.  This formula
>goes into the totals column but there is no total known so the result
>will say:
>
>"? ttl unknown"
>
>Yet when there _is_ a price in G2, then the formula should return a
>product, derived from G2 x I2.
>And when that is the case, rather than see "? ttl unknown" in the cell
>(without quotation marks, of course) it should see a dollar amount
>with 2 decimal places.
>
>A second column deals with a total after a discount and it looks like
>this:
>
>=IF(G2="?",TEXT(G2,"$0")&" ttl price unknown",J2*(O1/100))
>
>Once I know how to display currency in the formula when there is a
>dollar amount, I can then modify this second formula.
>
>How can I achieve this through modifying the formula above?
>
>Thanks!  :oD

0
Gord
5/26/2010 10:59:59 PM
Reply:

Similar Artilces:

HELP: Need macro for excel to get data from server database and use results to populate an Access Database
Hi all, Is there a quick way to do a query on an SQL Server database and have the output put direct into an Access Database? I know how to do it by grabbing the records and then inserting each result one by one, but this takes forever to complete and I am after a quick solution, if it exists. Is anybody able to assist ? is it possible? Thanks in advance for any help! Regards Clint you could link to the SQL Server table(s) from Access, write a SELECT query to pull the records you want, and then turn the query into either an Append query to add the data to a native table in the Acces...

need a nice price letter format
http://officeupdate.microsoft.com/templategallery/default.asp I would suugest you search for Word Templates. Excel is not a very good word processor. Gord Dibben MS Excel MVP On Tue, 27 Apr 2010 13:09:01 -0700, mdixon <mdixon@discussions.microsoft.com> wrote: ...

How to get Cell contents in scrollbar mouseover, not cell#
Hello all - I have a large Xcel 2000 file with last names in column 'A'. I'd like to use the scroll bar and see the actual cell value rather than the cell# in the mouseover, as the cell# is not terrifically valuable information. Any hints or insights would help. Thanks and regards, Bob ...

colour formula
I have two column where I want to apply a formula. I want=20 to check A1 if there is a specific colour in a1 then it=20 should automatically put person=E8s name with who is going=20 to do the report. I have give different colours to=20 different people eg. light yellow to A, green to B etc. I=20 want if client XYZ is there an if its in green colour it=20 should automatically put B with green colour in b1. Any help appretiated. Hetal You'll need a UDF like the one on Chip Pearson's site: http://www.cpearson.com/excel/colors.htm If you're new to macros, you may want to read David...

How to get the value of the prompt environment variable
I need a way to get the value of the %prompt% environment variable. I would prefer it actually if I could get the value from a more reliable source as this is a console app I'm writing. -- ClassicVB Users Regroup! comp.lang.basic.visual.misc Free usenet access at http://www.eternal-september.org Leo formulated on Wednesday : > I need a way to get the value of the %prompt% environment variable. I would > prefer it actually if I could get the value from a more reliable source as > this is a console app I'm writing. Scrap the more reliable way section as i...

how can I get a cell to change color by date to indicate expiery t
I'm trying to get the cell color to change from green to yellow to red as a date entered in the cell gets closer to that date plus 12 months or 36 months. ie some task was completed on 1 Jan 08 and will last for 24 months so I want the cell to read green if the date is still good turn yellow a month or 2 before it expires and turn red and clear the date if expired. So if the cell is empty it should show red. But I want to be able to update the date the task was completed ie it was completed again on the 15 Dec 10 before it expired so I want the countdown to expiration to restart f...

Cell Formatting #12
Entering a bank sort code into a cell i.e 16-00-04, however it keeps changing to a date range. I have tried converting the field to text and then change the format, doesn't work. I have also tried copying the format from the cell above and then overtyping it, this doesn't work. The only way I could get round it was to start the string with a '. Thanks for the help. Hi try formating the cell PRIOR to entering data in it as 'Text' >-----Original Message----- >Entering a bank sort code into a cell i.e 16-00-04, >however it keeps changing to a date range....

Need to get a file please
I need to update Office 2002, and the installation stops, asking for the file 'pubret.msi'. I don't have the Publisher CD anymore, can anyone please send me the file, or direct me to a link. Thanks. -- Magnus Jungbeck Best Computer Hi Magnus Jungbeck (MagnusJungbeck@discussions.microsoft.com), in the newsgroups you posted: || I need to update Office 2002, and the installation stops, asking for || the file 'pubret.msi'. I don't have the Publisher CD anymore, can || anyone please send me the file, or direct me to a link. Thanks. You cannot update without the CD. In ...

Paste Formula #2
I am looking for a keyboard equivalent to Paste Special Formula and Paste Special Value Alt-e,s,v for Paste Special>Values Alt-e,s,f for Paste Special>Formulas "Spikesmom" wrote: > I am looking for a keyboard equivalent to Paste Special Formula and Paste > Special Value ...

Help with formulae
BHi All, I am new to excel, so any help would be great :) I want to automatically insert a set text string for instance, ABC into A1 only if there is a number inserted into cell B1 for instance 12345. So if I insert 12345 into cell B1, A1 would then show ABC, if I insert 6789 into B2, A2 would then again return ABC. However if there is no value in B1 or B2 then I need A1 and A2 to be blank and not return N/A# etc =IF(B1=12345,"ABC","") put that formula in A1 and copy to A2 -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Racing...

Date format 06-02-06
Hi; Is it possible to manipulate the date format? Actually, I want to be able to put july, 15 2006 instead of 07/15/2006. Can anyone help me? Thax! Mehdi Hi MA, Where do you want to do this? In the UI? If so, you can add some script to format the date. You'll need some help from a script like this one at CodeProject: http://www.codeproject.com/jscript/dateformat.asp HTH -- Michael Sanford SynchedUp Solutions http://www.synchedup.com "MA" <mamor@NOPSPAM.com> wrote in message news:871f874a343947d29d6611d45ae77cc9@ureader.com... > Hi; > > Is it possible...

Database Queries/Date Formats
I am importing columns from an Access table using MS Database Query. The date format in the database is YYMMDD, so the query brings the date into the spreadsheet as the same. (ex. 880321). When I attempt to format the cells as a date in Excel, the formatting doesn't work, and the date stays in the same format. Why? And how can I get around it using Excel? Thanks. Dave Check the Access table, is the Date field set to a Date/Time datatype? My guess is not. You can remedy this in Excel or in Access with a date formula. P "daver676" <daver676@yahoo.com> wrote in me...

Conditional Formating #21
I am trying to conditional format cells with a value that comes from a formula say mid(e6,5,4) = 2004 I want all 2004 values to show bold and red. But can't seem to get the hang of it. -- Delaina ------------------------------------------------------------------------ Delaina's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24151 View this thread: http://www.excelforum.com/showthread.php?threadid=392324 Something like: =MID(E6,5,4) ="2004" But if that formula is really returning a date: =year(e6)=2004 would work better. Delaina wrote: > ...

i get a script error every time i open outlook
every time i start outlook i get a script error message for internet explorer. it then asks me if i wish to continue using scripts on this page and i have to select yes or no. i dont use internet explorer - i use firefox as my browser ...

Sync parts of two worksheets including formats
I am looking for a way to have a primary and secondary worksheet. I will make changes to the primary and want those mirrored on the secondaary sheet. I want o mirrow all changes, formatting, adding, deleing rows, columns, etc., not just the data. I know how to use the change event to mirror the data but no help for the other things. I also know how to use the Sheets(array(....)).select to link the two but this mirrors the entire sheets and I just want to mirror a section of the sheets say A1:F1000 and the rest is unique to each sheet. Anyone have a solution. ...

formula help.. text
I am trying to get a series of cells to fill with a color if the word "insert" is inserted into the first cell. I.E. cells A2-A5 and cell A2 has "insert" typed in so up to A5 will change color. How do I go about doing this? Try Format | Conditional Formatting - after selecting cells A2:a5 Then use "Formula is" and type in =$A2 = "insert" Then click on the Format button and select the color you want from the Patterns tab "Murph" wrote: > I am trying to get a series of cells to fill with a color if the word > "insert&qu...

Number formating
I am downloading data from SAP into excel and all negative numbers are read as (12345-). What do I need to do to get the negative sign on the left side of the number and format it correctly? thanks for the help Data>Text to Columns>Next>Next> check "general" >Finish. Gord Dibben Excel MVP - XL97 SR2 & XL2002 On Wed, 9 Jul 2003 14:41:45 -0700, "Sulaiman" <sulaiman.arsala@vitalms.com> wrote: >I am downloading data from SAP into excel and all negative >numbers are read as (12345-). What do I need to do to get >the negative sign on ...

Excel Formula to colourfill cells when condition met
I daily populate a sheet sent to me with answers (time & dates) of 1,2 or 3 star items, the 3 star items I manually fill with a certain colour. Is there any way of filling a row that contains a cell with 3 stars automatically with a designated colour. In this example colour filling the whole row because one of the cells contains *** 15-May bx3008 aaaaaaaaaaaa *** bbbbbbbbbbbbbbbb ccccccc Select all the data - I will assume each row foes fro A to G and that the data starts on row 1 In the conditional formatting dialog use this formula =COUNTIF($A1$:G1,&qu...

How to get the stack trace when application crashes ?
Hi, I have set a function to catch the exception when system crashes using the API SetUnhandledExceptionFilter. I used AfxDumpStack(AFX_STACK_DUMP_TARGET_DEFAULT) inside the exception handler function but didnot get the stack trace as shown by Dr.Watson. In Dr.Watson, i see funtion address, i want to know what is the funtion name. I am finding difficult to manage the PDB and corresponding binary, so thought of writing the stack trace in the exception handler which the Dr.Watson write when the crash occur. How to get the stack trace when the application has crashed similay to what the Dr.Wat...

Get/Change first operation error
I am getting this error message when entering MBS: A get/change first operation on table 'PA_Timesheet_SETP' failed accessing SQL Data. More Info: Could not find stored procedure 'TEST.dbo.zDP_PA41801F_1' Invalid column name 'PA_Update_Periodic_Opt' Version 8.0 thanks -- Doug Hi Its looks like you have not run the PAUtils to Install PA for Great Plains. The Project Accounting module in Great Plains cannot find any tables - stored procs on the Test Company / Database. -- Regards James[MVP] Visit MBS Blog Central http://mbscentral.blogs.com "Doug&quo...

Conditional Format-should be an easy answer
I have a spreadsheet with two columns of numbers. I want to highlight the number in the second column if it is greater than the number in the first column. e.g. If c3 is greater than a3, highlight, and then if c4 is greater than a4 and so on. I was able to do this quite easily in XL 2003, but have been stymied in XL2007. I realize this is a rudimentary question, but I just can't seem to figure it out, and have searched many help sites. Thanks for any help. Select the range of cells in column C Home Tab>Conditional Format>New Rule>Select a Rule Type>Use a Formula =$C1>$A1...

Outlook XP. incoming mail now only in text format
Something happened yesterday that had all of my incoming mail appear in text format rather than the HTML (layed out with photos and art included as it isued to). I cannot think of anything that I may have done to affect this. Does anyone know of a setting change that may correct this? Many thanks, Patrick ...

formatting sql-statement causes left outlining in MSHlexgrid
Hello I use the following SQL-statement to fill a MSHFlexgrid This causes the column to left-outlining nomatter how I try SQL = "select Reken as Reken,format(Bedragv,'#0.00') as Lasyear when I remove the format, the column is right-outlined. How come, please help. I want it Right outlined, but allsod formatted to 2 decmials thanks Catharinus van der Werf csvanderwerf@planet.nl "catharinus" <csvanderwerf@planet.nl> wrote in message news:3577f3bb-2264-44af-b9de-f085d1581055@21g2000yqj.googlegroups.com... > Hello > I use the following SQL...

Imported Date Format
I have imported data from an accounting program and the date format is 20070101 for January 1, 2007. I have looked at various sites that would use Left(),Mid(),Right() to break the numbers and then put them back into 2007/01/01 but was wondering if anyone had a simpler solution. Thanks, Lee Coleman =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Look familiar? "Lee" wrote: > I have imported data from an accounting program and the date format is > 20070101 for January 1, 2007. I have looked at various sites that would use > Left(),Mid(),Right() to break the numbers and ...

Excel 2003
I need to format some cells in MINUTES that can be counted. Entries will be something like +25 min _15 min And then there will be a cell that tallies the two for the day and then a cell that tallies for the week. I see no preset formatting that I can use for this. How would I do this? Just format it,Format,Cells,Custom, as +0"min";-0"min" -- HTH RP (remove nothere from the email address if mailing direct) "windsong" <windsong@discussions.microsoft.com> wrote in message news:7D080C28-E244-4B1C-AF16-AC4B221E0E08@microsoft.com... > I need to f...