Excel Complex formula questions 2

[satinsilhouette]

hi,

I want to check the value of two cells and then do a count if they are true:

Cell one has a class number in it. Cell two needs to be in a date range, for instance, in January, February, etc. So 1/1/2006 to 1/31/2006 range.

Then if both of these are true, I need to count a different field.

SO far I have:

=IF(Data!A2:A400 = "CR 10 RD1 and Data!C2:C400 between #01/01/2006# and #01/31/2006#,COUNTIF(Data!A2:A400,Data!A2,0))

But I am getting an error. Can someone help?

 

[SkipVought]

Zack,

I don't understand.

"OFFSET is a volitile function." EXACTLY! So what's the problem with that?

"COUNTA will not give you accurate results if you have a non-contiguous range or blanks in your data data." Very true. But in a properly designed table, one must have at least one column that is fully populated.

I do appreciate the link to xldynamics -- GOOD STUFF! Great white paper on NAMES! But where's the alternative? I did not see any discussion on Dynamic Named Range.

Many techniques have caveats that experienced Excel users ought to be aware of. Pros and cons of various approches. The more full your toolbag is, and the more understanding you have about each tool, the better choices you can make.

So lay it on me! If you, "recommend against the OFFSET/COUNTA method," what DO you recommend instead?

I've used the OFFSET Function sucessfully for a number of years (sometimes in conjunction with MATCH & COUNTIF, and other times with COUNTA) and never had a problem.

Naturally, we are focusing completely on a spreadsheet solution.

Where am I going wrong?


Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[firefytr]

I'm not necessarily saying anybody is wrong. It's like you say, more tools in the toolbag. My preference is for a dynamic named range not using COUNTA. There are a couple difference reasons I would use an OFFSET/COUNTA named range. One is if I am using it for Pivot Tables; the method I use doesn't seem to work on it and I don't know why. Second is if I don't know the nature of the column value (textual or numerical).

For text:
Named range:
BigStr
Refers to: =Rept("z",255)

For numbers:
Named range:
BigNum
Refers to: =9.99999999E+307

Then say we're looking at column A of sheet1 which is all numerics, my dynamic named range would be ..

=Sheet1!$A1:Index(Sheet1!$A:$A,Match(BigNum,Sheet1!$A:$A))

With text in column A ..

=Sheet1!$A1:Index(Sheet1!$A:$A,Match(BigStr,Sheet1!$A:$A))

There are other ways with LOOKUP to get the last range of a column and setup your dynamic named range that way, but I find it too taxing and cumbersome for maintenance and trouble shooting.

All ways will work (except for those reasons lined out above) and basically boil down to matter of preference. As for the Offset function being volatile, I consider that a bug. It's usually not an issue on smaller spreadsheets, but for those who have very large spreadsheets and where recalc time is a serious issue, staying away from volatile functions becomes a real issue. A couple other examples of volatile functions include =Now(), =Today(), =Rand(), etc; they will recalculate on every level of calculation, not just when they (or one of their dependents) are updated.

I would say that choosing which solution works for you is slightly dependent on the spreadsheet need and largely based on preference. And I didn't mean to insinuate that anybody else was wrong, just my strong opinion.

Take care!

-----------
Regards,
Zack Barresse

 

[SkipVought]

Either you have a column with a value in every row of data or you don't.

If you don't, like a row value in a pivot table, then you have to find a column that does. In a pivot table there IS a column with a value in each row of data.

I still do not understand. Using the BigNum approch, that only finds the Biggest Number not the LAST cell reference. Please explain that to me.



Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[firefytr]

It's more of an anomoly than finding what you'd actually think it finds. It's not finding the largest value in a column, but in fact the last value in the column. This was first found by Aladin Akyurek on (I believe) the newsgroups. It basically works on a binary level, and getting into it more than that would either confuse me or bore everyone else.

With that method not working on a pivot table is an anomoly to me. I believe it to be a bug, but then again that method isn't exaclty standard to begin with, not native anyway.

-----------
Regards,
Zack Barresse

 

[SkipVought]

Zack,

I guess I have some learning to do. Thanx for the info.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[firefytr]

Check out some supporting cases...

http://www.mrexcel.com/archive/Formulas/12938.htm

http://www.dailydoseofexcel.com/archives/2004/12/02/getting-the-last-value/

http://forums.techguy.org/business-applications/472952-solved-first-last-across-range.html

http://tinyurl.com/rxa8h

http://tinyurl.com/lwbty

http://tinyurl.com/s69jw

http://tinyurl.com/q4qv7

http://tinyurl.com/p4wna

and probably the most comprehensive link ..

http://tinyurl.com/mt7aq

HTH

-----------
Regards,
Zack Barresse