Fairly Easy Formula Question....

......the answer to which is eluding me!

I am using this formula =VALUE(C1&" "&A1) to return a date (month in col A
and day in col C). Can someone tell me how to modify the formula to avoid
#VALUE! being returned when col C contains an empty cell.

TIA
Zorro


0
zorro (10)
12/7/2003 9:32:15 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
597 Views

Similar Articles

[PageSpeed] 53

What exactly do you have in C1.
How is C1 formatted and what did you enter  (type in) for a "month"
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Zorro" <zorro@nexia99.freeserve.co.uk> wrote in message news:lsCAb.5842$SV1.1768@news-binary.blueyonder.co.uk...
> .....the answer to which is eluding me!
>
> I am using this formula =VALUE(C1&" "&A1) to return a date (month in col A
> and day in col C). Can someone tell me how to modify the formula to avoid
> #VALUE! being returned when col C contains an empty cell.
>
> TIA
> Zorro
>
>


0
dmcritchie (2586)
12/7/2003 9:40:47 AM
Zorro,

It's not an empty C1 causing the problem, it's trying to make a value of
text.

Why are you using value? Why not just use =C1&" "&A1?

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Zorro" <zorro@nexia99.freeserve.co.uk> wrote in message
news:lsCAb.5842$SV1.1768@news-binary.blueyonder.co.uk...
> .....the answer to which is eluding me!
>
> I am using this formula =VALUE(C1&" "&A1) to return a date (month in col A
> and day in col C). Can someone tell me how to modify the formula to avoid
> #VALUE! being returned when col C contains an empty cell.
>
> TIA
> Zorro
>
>


0
bob.phillips1 (6510)
12/7/2003 9:57:08 AM
C1 contains a number (1 to 31, formatted as a number). The month column is a
formula which returns the sheet name. There is a sheet for each month named
Jan, Feb, Mar etc. Thus the formula I'm having trouble with returns the
combination of the month and day cell which works fine except when the day
cell is empty. I know that by putting ,"" somewhere, it might help, but I'm
lost!

Cheers


"David McRitchie" <dmcritchie@msn.com> wrote in message
news:#ydGkaKvDHA.2304@TK2MSFTNGP12.phx.gbl...
> What exactly do you have in C1.
> How is C1 formatted and what did you enter  (type in) for a "month"
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Zorro" <zorro@nexia99.freeserve.co.uk> wrote in message
news:lsCAb.5842$SV1.1768@news-binary.blueyonder.co.uk...
> > .....the answer to which is eluding me!
> >
> > I am using this formula =VALUE(C1&" "&A1) to return a date (month in col
A
> > and day in col C). Can someone tell me how to modify the formula to
avoid
> > #VALUE! being returned when col C contains an empty cell.
> >
> > TIA
> > Zorro
> >
> >
>
>


0
zorro (10)
12/7/2003 10:35:52 AM
"VALUE" function returns a windows date value rather han text. I need to
sort the Date column.

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uchZ5hKvDHA.2220@TK2MSFTNGP09.phx.gbl...
> Zorro,
>
> It's not an empty C1 causing the problem, it's trying to make a value of
> text.
>
> Why are you using value? Why not just use =C1&" "&A1?
>
> --
>
> HTH
>
> Bob Phillips
>     ... looking out across Poole Harbour to the Purbecks
> (remove nothere from the email address if mailing direct)
>
> "Zorro" <zorro@nexia99.freeserve.co.uk> wrote in message
> news:lsCAb.5842$SV1.1768@news-binary.blueyonder.co.uk...
> > .....the answer to which is eluding me!
> >
> > I am using this formula =VALUE(C1&" "&A1) to return a date (month in col
A
> > and day in col C). Can someone tell me how to modify the formula to
avoid
> > #VALUE! being returned when col C contains an empty cell.
> >
> > TIA
> > Zorro
> >
> >
>
>


0
zorro (10)
12/7/2003 12:51:36 PM
If you want no value when C1 is empty you could use

=IF(C1<>"",VALUE(A1&" "&C1),"")

If you want a valid date, this assumes this month

=IF(C1<>"",VALUE(A1&" "&C1),VALUE(A1&" "&TEXT(TODAY(),"mmm")))

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Zorro" <zorro@nexia99.freeserve.co.uk> wrote in message
news:fnFAb.6748$SV1.2436@news-binary.blueyonder.co.uk...
> "VALUE" function returns a windows date value rather han text. I need to
> sort the Date column.
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:uchZ5hKvDHA.2220@TK2MSFTNGP09.phx.gbl...
> > Zorro,
> >
> > It's not an empty C1 causing the problem, it's trying to make a value of
> > text.
> >
> > Why are you using value? Why not just use =C1&" "&A1?
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >     ... looking out across Poole Harbour to the Purbecks
> > (remove nothere from the email address if mailing direct)
> >
> > "Zorro" <zorro@nexia99.freeserve.co.uk> wrote in message
> > news:lsCAb.5842$SV1.1768@news-binary.blueyonder.co.uk...
> > > .....the answer to which is eluding me!
> > >
> > > I am using this formula =VALUE(C1&" "&A1) to return a date (month in
col
> A
> > > and day in col C). Can someone tell me how to modify the formula to
> avoid
> > > #VALUE! being returned when col C contains an empty cell.
> > >
> > > TIA
> > > Zorro
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
12/7/2003 1:07:47 PM
Another way:

=IF(C1<>"",VALUE(CONCATENATE(A1," ",C1)),VALUE(CONCATENATE(A1," ",1)))

If C1 is empty, this will use the first day of the month. 



On Sun, 7 Dec 2003 13:07:47 -0000, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> wrote:

>If you want no value when C1 is empty you could use
>
>=IF(C1<>"",VALUE(A1&" "&C1),"")
>
>If you want a valid date, this assumes this month
>
>=IF(C1<>"",VALUE(A1&" "&C1),VALUE(A1&" "&TEXT(TODAY(),"mmm")))

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup 

To e-mail me, remove nospam from the address
0
mikeargy (62)
12/7/2003 2:09:35 PM
Reply:

Similar Artilces:

Formula #27
I am created a table containing weekly total of overtime for a number of employees (input table tab). I now want to create a chart (weekly overtime tab) for the weekly overtime. I want to be able to enter a date for a specific week and only create that week's chart. Is there a function that can lookup a date in the input table and use the numbers in the below rows to create the chart? Any information is appreciated. Thanks in advance Yes, you could do this, but you need to supply a bit more detail. What does your overtime sheet look like? A list of names in column A and overtime in ...

Convert Excel formula to Access
Guys, I need to convert the following excel formula into Access =PROPER(LEFT(MID(A19,FIND("CN=",A19)+3,255),FIND("/OU=GB",MID(A19,FIND("CN=", A19)+3,255))-1)) Can anyone help as I am having a 'thick' moment!, it would be really appreciated, Thanks Ozzie -- Message posted via http://www.accessmonster.com Try... strconv(Left(Mid(A19,InStr(A19,"CN=")+3,255),InStr(Mid(A19,InStr(A19,"CN=")+3,255),"/OU=GB")-1),3) Note that InStr replaces Find and its parameters are reversed. Also, I have left "A19" in the expres...

Complex AND OR Formula
HI I need to express something in a formula , and am having trouble with it. I need to say this : IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 , OTHERWISE PUT J2 Can someone assist with some code to make this happen , please? Grateful for any help. Sorry for double post. Best Wishes =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message news:WY5OXLAG...

Can't edit SQL question in MS Excel/Query
Hello, I'm running Win2k SP3, Excel 2000 SP-1 and have created SQL questions that fetch data from an Oracle 8i DB via ODBC. Sometimes (or actually quite often) I can't edit the SQL questions in MS Query. The MS Query icon just flashes in the taskbar and disappears. Any wiz got any idea what can be done about it? Regards Hans Hans I'm not sure about the MS Query icon you are talking about in the taskbar but the way I can get to MS Query using Excel 2000 and connectiong to Oracle database is choosing 'Data' followed by 'Get External Data', and then choosing '...

Comment faire parler les cellules avec Speech et ses propri�t�s et m�thodes
Bonjour, Je voudrais faire parler des cellules avec Speech et ses m�thodes. Je pensais (en regardant mes livres) que l'instruction suivante suffisait : Application.Speech.Speak (et l"adresse de la cellule) J'ai essay� avec Set Set MaCellule = Range("A1") Mais cela ne marche pas. Y a t-il autre chose � faire sur l'ordinateur (XP) ou avec VBA Excel (2002) D'avance merci, Jean-michel Hi! Peutetre voyez http://support.microsoft.com/default.aspx?scid=kb;en-us;287120 (mais c'est...

Formula editor
Everyone gets to see formules like this one sooner or later: A2: =IF(AND(A1=0;B3=1);IF(C17=C18;2;3);IF(OR(D12=1;D12=D13);4;5)) When jou put a remark to a cell, the remark will show when crossing the cell with your mouse. Why not something like that with a kind of Excel formula editor? So, moving the mouse over cell A2, the cell with the formula, and a window will show like this: IF (A1=0 AND B3=1) THEN BEGIN IF C17=C19 DAN BEGIN CELL=2 END ELSE BEGIN CELL=3 END END ELSE BEGIN IF (D12=1 OR D12=D13) THEN BEGIN CELL=4 END ELSE BEGIN CELL=5 END END I th...

A macro for converting formulas to formula results only
I need a macro that will convert a cell's formula to reflect its formula result only based on the following: Range is A10:C200 Only when formula's result > 0 Execute upon closing the file Thanks for any help you can provide. Michael For Each cell In Range("A10:C200") If cell.Value2 > 0 Then cell.Value2 = cell.Value2 End If Next cell -- HTH Bob "Michael Lanier" <michaelrlanier@gmail.com> wrote in message news:4e59c63e-158f-4f39-9310-52511fec6ae2@k33g2000yqc.googlegroups.com... >I need a macro that w...

Increase years by one formula
Hello all! I need help with a formula... I have a date (1/1/04 but displayed as Jan-04) in cell E4. I would like to have a formula that looks at E4 and returns the sam date but increase the year by one 1. Every formula I have trie doesn't work or increases the month by one. Any help would b appreciated! Cind -- Message posted from http://www.ExcelForum.com I'm sure there's a better way, but one way is: =E4+366 >-----Original Message----- >Hello all! > >I need help with a formula... > >I have a date (1/1/04 but displayed as Jan-04) in cell E4. >I wo...

Workflow question: Upon opening a new case notify team
I am trying to configure CRM so that it will notify a team (all team members) when a case is opened. How would I accomplish this? I have created a team, called tech support, added members, and was setting up a workflow rule when I noticed that you can set up notifications only to users, not teams. This just doesn't seem right. Oh yeah, I also created a new email template. Same thing, 1 owner.... What am I missing? David M Afraid there is no easy way to do this. You could use a Post-URL action to send the message and it could expand out the team members. Also note that you can&#...

Selecting Formula
I have set of pre determined formulae. the condition for selecting a particular formula is the number of characters entered in one particular cell. In other words, a formula should select from a table of formula depending upon the characters in the data entry cell. Have tried IF formula, but does not work since nesting IF formula limited to 7. Help Required.. -- forever ------------------------------------------------------------------------ forever's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28526 View this thread: http://www.excelforum.com/showthread.p...

SQL Format question
I have the following question trying to format a calculated column with a sql stmt. I basically have extcost / unitcost as poqty. in the results it carries the decimal out 15 or so places. How can I format this to only carry out to 4 decimals? Thanks for you assistance -- Craig convert(numeric(19,4), extcost/unitcost) as poqty Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "C Dunn" wrote: > > I have the following question trying to format a calculated column with a > sql stmt. I basical...

Formula help #41
Xl2000 Been trying to get this right, and I think it is simple but I am not getting it. What I have is: Column B “amount Rec’d”, Column C “Purchases”, Column D “Cash out”, Column E “Balance”. I am trying to put together a formula in Column E that will only give a result when data is entered into either B, C, or D. Then be able to copy it all the way to row 36, and not have anything in Column E until data is entered into the corresponding row. I know this is easy but I am not getting it. Please Help Mike R. Hi! Will you always enter data in B1, C1 and D1 as a group? If=20 so, yo...

Returning the formula in a cell
Hi I have umpteen sheets in my excel, so I think it would be very useful if I could have hyperlinks to each of these sheets on the summary page. Let us say I have, in Sheet 1, Cell A1 the formula "=Sheet2!A2". Lets also say that Cell Sheet2!A2 contains "Hello" In colum 2 (B1), i want to be able to use the HYPERLINK formula - if I click on this cell, it shud take me to Sheet 2 I want to pick up the text "Sheet2!A2" from A1 and not the value returned by "=Sheet2!A2"(which is "Hello"). Which formula would I use to pick up the formula in cel...

MSFlexGrid question
In an MSFlexGrid, how can I detect that the user clicked on what I'll call the "header row" (I think it's row 0) as opposed to row #1? If he clicks on the header row, I want to do one thing but, if he clicks on row 1, I want to do another. I find that clicking on the header row sets the .Row property to 1 (instead of 0) just as clicking on row #1 also set the .Row property to 1. "Martin" <ironwoodcanyon@gmail.com> wrote in message news:v6hgm51oae82hr7kcu9oc58b0b5kvske84@4ax.com... > In an MSFlexGrid, how can I detect that the user cl...

OLAP cube and formula
Hi, I created an OLAP cube by data->pivot table report->... from a relational database. Then I chose "Create offline OLAP cube". The cube was created, but it doesn't seem possible to add a formula based on the fact fields to the OLAP cube. The fact are 'sales' and 'revenues' and I need to see the ratio '=sales / revenues'. Is it possible at all?? If not - can I buy any update or smth. to get this functionality? This is a normal functionality for pivot tables. I don't understand why can I not do this in OLAP cube? Thanks for your time. J ...

hit counter question
I need a hit counter on Excel 2003 which counts the number of times a condition is met. This spreadsheet DDEs to RediPlus (trading software). The DDE fields would be "last price" and "day high price". When last = day high, I have it flash a green conditional format. What I would like to do is to not only flash green but also count the number of times last = day high. For example, A1 = DDE link to "last price" of IBM (constantly changing) B1 = DDE link to "day high price" of IBM (changes only when a new high is made otherwise stays the same) C1 = ...

Upgrade Questions #2
Currently GP 9.0 is running with SQL 2000 MSDE. We have a new computer to move GP to and run with SQL 2005 MSDE. I am debating how I should proceed with this task. The existing computer will not run SQL 2005 MSDE whic is one reason why we purchased a new computer. Should I install SQL 2000 MSDE and GP 9 on the new computer then upgrade to 2005 or is there a way to restore the database? -- Jean Jean, Take a look at KB article 878449 which explains in great detail the steps necessary to move GP from one server to another. I would suggest, if you haven't done so yet, to download ...

Complicated Pie Chart formula
I have a survey that I want to graph the answers to in a pie chart. However the particpates could choose more than one response to the questions. These means that each pie piece must be a percentage of the total surveyed not of the total answers. I don't know if this is even possible. If you know how to do this please let me know. I am having a hard time understanding what you want to do and how you want the data reported. How about an example that illustrates what you want? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Off...

Easy question for a noob
Well this is my first time using excel so i just need something basic as a grid and being able to input my information to it Question: how do i make a x and y colum to be custom with simple numbers and negative numbers that follow after that. say 1000 down to 0 then down to -1000 for the x column, then the same for y. -- Brian0icu812 ------------------------------------------------------------------------ Brian0icu812's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31669 View this thread: http://www.excelforum.com/showthread.php?threadid=513588 Assume you...

Fairly intricate array formula question.
I have the following array formula, works correctly for me in cases where both input values are nonblank, but i don't get the result i need if one or the other is blank. Here's the formula: {=SUM((data!A11:A61-AVERAGE(data!A11:A61))*(data!B11:B61-AVERAGE(data!B11:B61)))} [Actually if you are interested, it is the crossproduct term, the numerator of the formula given the help file for the Excel SLOPE function]. What i want the formula to do is skip any row in which a value in range A11:A61 OR B11:B61 is blank, and continue to return the crossproduct sum for the rest of the rows...

How can I create this it formula?
How can I create this it formula. :confused: +-------------------------------------------------------------------+ |Filename: FORMULA.doc | |Download: http://www.excelforum.com/attachment.php?postid=4763 | +-------------------------------------------------------------------+ -- HR157 ------------------------------------------------------------------------ HR157's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34458 View this thread: http://www.excelforum.com/showthread.php?threadid=542176 =C12*INDEX(B:B,MATCH(...

Combo Box question....
I have a client that wants this functionality in their combo boxes. They want to be able to type in an entry into their combo box and then when the control loses focus, if it doesn't match a value they want to be able to add it to the dropdown list. However, since the dropdown is based off a query so I can't add it directly to the list. And since there is a lot more data required than just the information being type (e.g. Customer Name also need other contact info for that customer). My question is how do you all handle requests like this? So, I was going to do On Lost Focus...

Formula "Change" Problem
Situation: In cell A1, I have a simple formula that sums the data in the following 6 horizontal cells. The formula is =SUM(B1:G1) I want this formula to never change under any circumstance. The problem is if I insert a column before in front of column B the formula changes to the following: =SUM(C1:H1) The spreadsheet is setup so that it is supposed to calculate a rolling 6 week total. Each new week a new column will be inserted and the data for the new week entered. How can i keep the formula from changing as the worksheet changes? Thank you in adnvance to anyone that may be able to h...

Filling a rota fairly
There are 5 staff members who want to park their cars at the office, but only 3 parking spaces. So the spaces need to be allocated by rotation. I looked at the past rotas, and they don't seem to have been fair. It would be good to do the allocation automatically, perhaps in Excel. If we use cols D-F for the parking spaces and a row for each week, is there a general method which will quickly fill in the rota? I know that this is a relatively trivial task to do manually, but it's a type of problem I haven't come across before, and it's interesting. I suspect the MOD fun...

How do I remove the sheet name from a named formula?
I would like to use the same name to refer to the same set of cells on different worksheets: SheetTitle=$A$1 but when I try this it reverts to SheetTitle=Sheet1!$A$1 Any ideas how to do it? I could use =IF(,,,) if I knew how to look up the currently active sheet; =IF(SheetName="Sheet1",TRUE,FALSE) Any ideas how to reference the name of the sheet? Well, you could do something like this: SheetTitle=!$A$1 The exclamation point means that the Name will refer to A1 on the active worksheet. Excel uses the SheetName to tell things apart. If I try to give to different A1'...