A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do I get a value instead of an error or txt in an Excel cell?



 
 
Thread Tools Display Modes
  #21  
Old August 23rd 09, 03:39 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,912
Default How do I get a value instead of an error or txt in an Excel ce

Post some samples of the data you're working with and show before and after
examples.


Gord Dibben MS Excel MVP

On Sat, 22 Aug 2009 19:25:01 -0700, Frazzled Rob <Frazzled
> wrote:

>Hello,
>
> I am sort of new to Excel 2007.
>
> I am having trouble trying to get the "total" of multiplying currency with
>quantity. I don't really understand the help box or the answers on the
>message boards.
>
> What I need is someone to show me an example, that way I can get this
>taken care of.
>
> In disparate need of help.
> Rob
>
>"NBVC" wrote:
>
>>
>> Have you confirmed the formula with CTRL+SHIFT+ENTER not just ENTER so
>> that { } brackets auto appear around the formula?
>>
>> Also, you can use this instead:
>>
>> =SUMIF(A3:A456," S Texas",C3:C456)
>>
>> and only use ENTER to confirm as normal.
>>
>>
>> --
>> NBVC
>>
>> Where there is a will there are many ways.
>>
>> 'The Code Cage' (
http://www.thecodecage.com)
>> ------------------------------------------------------------------------
>> NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
>> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126531
>>
>>


Ads
  #22  
Old April 27th 10, 10:05 PM posted to microsoft.public.excel.misc
Rodrjua
external usenet poster
 
Posts: 2
Default How do I get a value instead of an error or txt in an Excel ce

Hi I am trying to figure out a formula that allow me to avoid sign
carachters such "#< $ % &" being enter as data what can I do? Help me!

"Shane Devenshire" wrote:

> Hi,
>
> 1. First let me simplify one of the formulas
>
> =IF(B1,A1/B1,0)
>
> 2. If the cells really contain trailing spaces you can remove all of them
> with a single command - highlight all the offending cells and press Ctrl+H,
> enter three spaces in the Find what box and leave the Replace with box
> empty. Then click Replace all. The cells will still be text most likely,
> but they will not contain 3 trailing spaces.
>
> 3. You can use the VALUE function to convert text numbers followed by
> trailing spaces to numbers =VALUE(A1) However, this might not work if the
> trailing spaces were not really spacebar spaces. In which case you could
> try =VALUE(TRIM(CLEAN(A1)))
>
> 4. However, maybe the following solution will be best:
>
> Problem:
> When numbers are enter as text they may not calculate within formulas as
> they should. A few formulas will work fine despite the numbers being
> entered as text. Numbers can be stored as text by 1. preformatting the cell
> to Text and entering the number, 2. Typing an apostrophy in front of the
> number '123, 3. Because the data was downloaded from a soure inwhich it was
> stored as a text number, 4. Because you used the Text to Columns command and
> converted it to text., and....
>
> There is no sure indicator that a number is stored as text, although numbers
> are usually right aligned and text left aligned, this may not be the case.
> If you are using a later version of Excel, Error Checking green triangles
> may appear at the top left corner of these cell, but this feature may be off
> or the version of Excel may not support it. (2000 and earlier).
>
> You can find out what data type the entries are by using the =ISTEXT(A1) or
> =ISNUMBER(A1) functions. You can not tell by checking the Format. If a
> number was entered in a cell preformatted as General or as a number, then it
> will be a number, even if it's current format is Text. Likewise a number
> entered in a cell preformatted as Text will be text even if it's current
> format is Number, General, Date, Currency and the like.
>
> Solution:
> 1. Change the format to one that is numeric and then reenter the numbers
> (too slow and error prone.)
> 2. Select the cells and open the Error Checking options and choose Convert
> to Numbers.
> 3. Select an empty cell and copy it. Select the text number cell and choose
> Edit, Paste Special, Add (or Subtract). This method is ~100 times faster
> than #2.
>
> Dates are numbers, and if they are stored as text, you will not get an Error
> Checking triangle, so method #3 is obligatory if there is a substantial
> number of dates to convert.
>
> If this information is helpful, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
> "lypolintan74" > wrote in message
> ...
> >
> >
> > "T. Valko" wrote:
> >
> >> > You can install this macro, select the import and run the macro
> >>
> >> You forgot the macro!
> >>
> >> Did you mean this one?
> >>
> >> http://www.mvps.org/dmcritchie/excel/join.htm#trimall
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Peo Sjoblom" > wrote in message
> >> ...
> >> > It's not a good way of handling DIV errors because it will hide other
> >> > errors
> >> >
> >> > It's better to use
> >> >
> >> > =IF(B1=0,0,A1/B1)
> >> >
> >> >
> >> > You can install this macro, select the import and run the macro
> >> >
> >> > --
> >> >
> >> >
> >> > Regards,
> >> >
> >> >
> >> > Peo Sjoblom
> >> >
> >> > "LHW" > wrote in message
> >> > ...
> >> >>
> >> >> I understand how the ISERROR() function handles division by zero in
> >> >> your
> >> >> example; =IF(ISERROR(A1/B1),0,A1/B1) converts the value in cell B1 to
> >> >> the
> >> >> numeral 0. However, the problem I have encountered is that numbers
> >> >> are
> >> >> downloaded as text with three trailing spaces. I have to manually
> >> >> remove
> >> >> these trailing spaces to use these numbers in the calculation. This
> >> >> is a
> >> >> time consuming task.
> >> >>
> >> >> Is there a better way to automatically convert numbers stored as text
> >> >> to
> >> >> numeric format? I suppose I could write a macro to automatically
> >> >> remove
> >> >> three characters from the end of each item in a one-dimensional array
> >> >> (in
> >> >> my
> >> >> case), but I've never written a macro in Excel 2007. Can the VALUE
> >> >> function
> >> >> not be used to convert text to numbers? I have had no luck using it
> >> >> to
> >> >> do so.
> >> >>
> >> >> Any help would be greatly appreciated.
> >> >>
> >> >> Best wishes,
> >> >>
> >> >> Lewis Williams
> >> >>
> >> >> "JLatham" wrote:
> >> >>
> >> >>> "wrap" your formula inside of an IF statement with a test to see if
> >> >>> it
> >> >>> creates an error or not. Consider this
> >> >>>
> >> >>> A B C
> >> >>> 1 5 0 =A1/B1
> >> >>> will give you a #DIV/0 error
> >> >>>
> >> >>> But if you do this at C1:
> >> >>> =IF(ISERROR(A1/B1),"invalid",A1/B1)
> >> >>> you would be shown 'invalid' in C1 but if B1 is a non-zero value
> >> >>> then
> >> >>> you'd
> >> >>> get the true answer. You can substitute a value for "invalid" such
> >> >>> as
> >> >>> zero
> >> >>> or one, like:
> >> >>> =IF(ISERROR(A1/B1),0,A1/B1)
> >> >>>
> >> >>> The ISERROR() test will handle all errors, there are other options
> >> >>> such
> >> >>> as
> >> >>> ISERR() handles any error except #N/A
> >> >>> while ISNA() only handles #N/A - good for use with VLookup and
> >> >>> Hlookup
> >> >>> operations.
> >> >>>
> >> >>> "SkiRJB" wrote:
> >> >>>
> >> >>> > Is there a way to get a formula to return a value instead of an
> >> >>> > error
> >> >>> > (#DIV/0, #N/A, etc...) so when I usr that cell in a chart I can get
> >> >>> > a
> >> >>> > value
> >> >>> > at some where other than zero?
> >> >
> >> >
> >>
> >>
> >>

>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
resetting last cell jagdish.eashwar Excel Discussion (Misc queries) 11 March 31st 06 02:06 AM
missing cell borders in Excel Nak Excel Discussion (Misc queries) 7 March 9th 06 03:54 AM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM
Paste rows of numbers from Word into single Excel cell BecG Excel Discussion (Misc queries) 1 December 8th 04 04:55 PM


All times are GMT +1. The time now is 10:58 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.