Hi,
Even if it sounds a little freaky I'll appreciate an answer if it can be accomplished the way described here:
I want to declare a two series line chart relying on columns B+C values BUT without the need of those two helper columns.
Instead I like to use two array formulas if applicable.
The rest is explained inside the attached WB.
Thanks, Elm
Last edited by ElmerS; 11-25-2009 at 03:46 PM.
Hi,
A possibility is to code with VBA the creation of your named ranges with your rules ...
thus getting rid of the helper columns ...
HTH
Thank you.
I forgot to mention that I was "forced" to open a new post even though I was continuing to enquire on the same issue".
In the previous I explained that I do not want VBA a known issue in my questions which named me "the no vba man"
http://www.excelforum.com/excel-work...t-columns.html
Elm
Last edited by ElmerS; 11-21-2009 at 12:52 PM.
No VBA.....sounds good to me
Try this for COL_B
=IF(ISNUMBER(MATCH(ROW(A2:A24),SMALL(IF(A2:A24<>"",ROW(A2:A24)),ROW(INDIRECT("1:"& CEILING(COUNT(A2:A24)/2,2)))*2-1),0)),A2:A24,"")
Array entered
for COL_C just remove the highlighted -1
dll, OP wants to generate the series' via Names - ie no cells in use other than those in A. The B&C values have already been generated via formulae (in earlier thread), question has now been revised hence my request for (this) new thread.
EDIT: would appear again I've misinterpreted requirements... note to self, don't try and answer ElmerS' questions as we're invariably on different wavelengths!
Last edited by DonkeyOte; 11-21-2009 at 01:28 PM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
DaddyLongLegs,
Your next task is to teach Excel to prepare a cup of coffee...
(without helper-columns / without VBA)
Thanks a lot,
"The no VBA man"
Hi,
Just as a matter of interest why are the last two values 22 & 21 in columns B & C and not C & B respectively?
All the other column B values are on an even row and the col C values an odd row.
i.e What determines into which column the values belong?
Rgds
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
DaddyLongLegs,
I checked the two AF by selecting in the formula window and pressing [F9] and in fact they display the two ranges as expected.
However, I did was not successful in naming them and or using them as two chart series.
Please clarify.
Thanks, Elm
==================
Richard,
The values appear each in a different level.
First in B, second in C,
Third in B, fourth in C....
The 13th in B, 14th in C
(all empty cells are ignored)
If you'll delete the empty rows - only for demonstration - it will be much more clearer.
========================
Don't know about the chart series but you should be able to name them OK......you'll need absolute refs which I didn't include, i.e.
=IF(ISNUMBER(MATCH(ROW($A$2:$A$24),SMALL(IF($A$2:$A$24<>"",ROW($A$2:$A$24)),ROW(INDIRECT("1:"& CEILING(COUNT($A$2:$A$24)/2,2)))*2-1),0)),$A$2:$A$24,"")
Note the "array entered" part was misleading, you don't need that for naming ranges (force of habit....)
Thanks,
I figured out the abs. ref. before reading your last response - however.
I named the first formula as: BUY and the second as: SELL
(did not forget to erase the -1)
If I type in a cell: =BUY >> selecting it within the formula bar >> pressing F9 - I get this which is fine:
{1;"";3;"";"";"";"";"";7;"";"";"";13;"";12;"";20;"";"";"";"";22;""}
When doing the same for =SELL - I get:
{"";5;"";"";"";6;"";"";"";"";"";15;"";14;"";"";"";"";"";"";"";"";""} which is also as expected.
However:
I did not succeed using the name BUY as the values for series1 and SELL as the values for series2.
maybe because it is a result of an array formula ?
If I name the range B2:B24 as BUY and C2:C24 as SELL - I can use it as the series source.
What did I do wrong ?
Elm
=================== added ======================
I named the first array formula as BUY and the range B2:B24 as BBB
I declared a small line chart and changed in the formula bar from: =SERIES(,,Book3.xls!B2:B24,1) to: =SERIES(,,Book3.xls!BBB,1)
This works graet.
However - when I change the series into: =SERIES(,,Book3.xls!BUY,1) the chart becomes all zeros.
When I select, in the formula bar, the part: Book3.xls!BBB and hit F9 I get {1;"";3;"";"";"";"";"";7;"";"";"";13;"";12;"";20;"";"";"";"";22;""}
When I do the same to: Book3.xls!BUY I get: {"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}
Well I hope my problem is now fully understood.
Thanks in advance for any assistance.
Elm
Last edited by ElmerS; 11-21-2009 at 06:25 PM.
A little late but here is the chart solution using named ranges.
You need to fully qualify the ranges in the formula.
=IF(ISNUMBER(MATCH(ROW(Sheet1!$A$2:$A$24),SMALL(IF(Sheet1!$A$2:$A$24<>"",ROW(Sheet1!$A$2:$A$24)),ROW(INDIRECT("Sheet1!1:"& CEILING(COUNT(Sheet1!$A$2:$A$24)/2,2)))*2-1),0)),Sheet1!$A$2:$A$24,"")
Nice one Andy!
I went round in circles trying to make that work......
Yes, Andy.. very cool!
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Somebody on another forum answering a different problem pointed out that indirect was possible in charts if the reference was fully qualified.
I knew I had managed it before just did not know why.
Thank you, Andy,
It seems as if the only task Excel is not capable of is preparing a cup of coffee.
Elm
Place I pot of water on your computer case, let Excel run sum massive Array formulas for a while, and watch the pot boil... just add instant coffee granules.... now Excel made your coffeeOriginally Posted by ElmerS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks