Hi guys,
I've been trying to sort out defined names for hours with little luck.
I have several columns filled with IF statements, pulling numeric values where they exist and returning the word "FALSE" where no data exists. There is a header row, and 99 rows of results in each column.
I want to make defined ranges I can then use in charts (SERIES formula) which select just the numeric values in each column (ie not the cells that return "FALSE"). I currently have the formula, =OFFSET('Middle Is'!$G$2,0,0,COUNT('Middle Is'!$G$1:$G$100),1)
This formula works when G2 is numeric, but not when it returns "FALSE" even though there are numeric results further down the column.
I'd be most appreciative of any help. Do I need a different formula? I'm stuck!
Cheers
Last edited by global_zoo; 08-16-2011 at 07:29 AM.
PS I'm using Excel 2007
Hi global_zoo and welcome to the forum,
It looks like you are trying to do a Dynamic Named Range. See if http://www.ozgrid.com/Excel/DynamicRanges.htm doesn't help.
One test is worth a thousand opinions.
Click the * below to say thanks.
Thanks MarvinP.
I had been looking at the Ozgrid site... it gives the basic formula I'm using [=OFFSET($A$1,0,0,COUNT($A:$A),1)] but it doesn't work. I'm guessing it senses my IF statement as numeric, however, I pasted values and changed the FALSE cells to text with no result.
Can you include a statement within the formula to specifically tell it to exclude cells with "FALSE"?
I've worked out that it is something to do with the reference cell experession in the OFFSET formula. I think I need to change this reference to a nested reference that gives the cell that is the first numeric value in the column. Can INDEX do this?
You have a worksheet demonstrating the range you're trying to analyze and a mockup of the results you're trying to achieve?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
I sure do, as attached.Originally Posted by JBeaucaire
In the file, you'll find the the named ranges I'm working on (ie _0Long, _0Lat, _1Long etc) which use the formula, =OFFSET(S_0Lat,0,0,COUNT('Middle Is'!$G$1:$G$100),1)
I believe I need to insert some statement to replaced the relative position of the range, currently defined manually by another range (S_0Lat, S_0Long etc).
Many thanks
I don't see why Sheet1 is needed.
With Sheet2
In A2
Drag/Fill Down=IF(B2="","",IF(B2<1,0,IF(B2<2,1,IF(B2<6,2,IF(B2<11,3,IF(B2<21,4,IF(B2<51,5,IF(B2<101,6,7))))))))
In F2
Drag Across to Column U then down as required.=IF(F$1="_"&$A2&"Long",$C2,IF(F$1="_"&$A2&"Lat",$D2,""))
For the Dynamic Names
e.g. "_0Lat"
Refers to:=
Where 0 (red) is the "Bin Class" to be matched and counted.=OFFSET('Middle Is'!$G$1,MATCH(0,'Middle Is'!$A:$A,0)-1,0,COUNTIF('Middle Is'!$A:$A,0),1)
Is this what you require?
Last edited by Marcol; 08-16-2011 at 02:30 PM. Reason: typos
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
Marcol - thank you! This is perfect!
Thanks for taking the time to help me out.
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks