Hi y'all,
How do I write a formula that chooses only the cells with data in them? I know you can use =count() to return the number of cells with data, but is there a way to choose the whole array? The number of data points in each array also varies, so the formula would have to be flexible in that way.
Thanks,
Alex
What exactly is this formula doing with these chosen cells?
For example, If you =Average(A1:A50), it will ignore empty cells and cells with text and only "choose" cells with numbers in them
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I'm using it with the linest function, which doesn't act in the same way as the average function. It returns errors if cells don't have values.
Hi All,
an attempt: in linest function instead of
=LINEST(A1:A100...
=LINEST(A1:INDEX(A1:A100,COUNT(A1:A100)),...
Hope it helps
Edit: COUNT (wrong traduction from my Italian settings)
Last edited by canapone; 10-01-2014 at 10:15 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
There should be a direct way but I don't know it. What I would do is use formulas to make the x's and y's congruous.
See the attached.
In A and B is the original data.
In G and H is the joined data using Array Functions
Then I use Dynamic Defined names to select only G1:G18 (Formula's tab>Name Manager)
I can explain it more fully if you need and if this fits your needs.
Hi.
You could use an array formula**:
=LINEST(INDEX(A1:A10,N(IF(1,SMALL(IF(ISNUMBER(A1:A10),ROW(A1:A10)-MIN(ROW(A1:A10))+1),ROW(INDIRECT("1:"&COUNT(A1:A10))))))))
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks