Re: Maybe VLOOKUP?

What does the "--" after the equal sign mean?

Without those "double minus" signs, the formula would return either TRUE or
FALSE. The "double minus" coerces the TRUE to 1 and FALSE to 0.

Also, when I drag to fill subsequent cells with the formula, it changes
the
"A2:A3712" range to "A3:A3713", etc
=--ISNUMBER(MATCH(C2,A$2:A$3712,0))

That's what the "$" do. They "lock" the range rows from changing when
copied.

If this formula doesn't work then you have data "problems". One column may
be TEXT and the other may be true Excel date/time values (which are really
just numbers formatted to look like a date/time). There may be leading and
or tailing spaces or some other unseen characters in one or both columns.

Biff

"Ashley" <Ashley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9D8695D8-1841-4DF6-B80E-F9C327E2157D@xxxxxxxxxxxxxxxx
Thanks for the suggestion...
But I tried:
=--ISNUMBER(MATCH(C2,A$2:A$3712,0))
but it did not work. "A" is the original data (I'm using a smaller data
set
for practice), "C" is all possible date + hour combinations over the data
span, and "B" is a value that I'd like to be returned in the case of a
match,
but I'd happy with a TRUE or FALSE.

Also, when I drag to fill subsequent cells with the formula, it changes
the
"A2:A3712" range to "A3:A3713", etc. (But I do want it to change "C2" to
"C3".)

So I tried:
=--ISNUMBER(MATCH(C2,A:A,0))
Which didn't work either.

What does the "--" after the equal sign mean?

Thanks again
Ashley

"T. Valko" wrote:

Try this:

=--ISNUMBER(MATCH(B1,A$1:A$10000,0))

Biff

"Ashley" <Ashley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:23EB96BC-A4A3-4F19-8780-E6ECD26E80FA@xxxxxxxxxxxxxxxx
Hi
I have a huge dataset, 10,000+ rows many times over, of date + hour.
(i.e. 7/1/2004 16:00)
The point of my data is that there is not a value for each consecutive
date
+ time, some are missing. I need to format my data (for a statistical
analysis program) so that one column shows every possible date + hour
comnbination for a 2-year period, and another column shows a "1" or "0"
for
whether or not that date exists in my original dataset or not,
respectively.
For example, my data looks like this:
7/1/2004 1:00
7/1/2004 2:00
7/1/2004 4:00
7/1/2004 5:00
And I want it look like this:
7/1/2004 1:00 1
7/1/2004 2:00 1
7/1/2004 3:00 0
7/1/2004 4:00 1
7/1/2004 5:00 1

It seems like VLOOKUP is the answer, but I can't get it to work. Am I
on
the
right track, but maybe have a formatting problem?

Thanks for considering!!
Ashley





.

Relevant Pages

  • Re: Maybe VLOOKUP?
    ... Valko" wrote: ... Without those "double minus" signs, the formula would return either TRUE ... I need to format my data (for a ... whether or not that date exists in my original dataset or not, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Maybe VLOOKUP?
    ... Valko" wrote: ... The "double minus" coerces the TRUE to 1 and FALSE to 0. ... I need to format my data (for a statistical ... whether or not that date exists in my original dataset or not, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Trendlines
    ... the Power. ... All it means is that it could have been a minus instead. ... Just right click on the trendline equation, select "Format Data Labels" and make the required changes. ...
    (microsoft.public.excel.charting)
  • UNION ALL and MINUS
    ... INSERT INTO format ... SELECT 2, 'melody','melody' FROM dual UNION ALL ... But in SyBase I don't found the solution, because I read MINUS ...
    (comp.databases.sybase)