• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

retrieving the nth number in a row of numbers

S

slk213

New Member
I am trying to pull the nth number in a range of numbers.


http://dl.dropbox.com/u/71893801/Handicap%20program.xls


I have a range of scores from G3 to L3. I am trying to create a formula in cells B3 thru F3. In cell B3, I am looking for the 1st score in range G3 thru L3 excluding blank spaces which would be 45. In C3 I am looking for the 2nd number in range G3 thru L3 excluding blanks spaces which would be 44. This would continue thru finding the 5th number.


Thank you for any help you can provide.
 
Slk213


Firstly, Welcome to the Chandoo.org Forums


In B3: Put

=INDEX($A$3:$L$3,,SMALL(IF($G3:$L3<>"",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)))

This is an array formula so it has to be entered with Ctrl Shift Enter


Now copy across
 
Hui


Thank you so much. The formula works great, just took me a little while to understand the array aspect and the Ctrl Shift Enter. I look forward to to your help in the future.
 
Hui,


I may have jumped the gun when I said the formula works great. I guess I don't understand the formula very well. If I inserted a blank column to the left of B and used the same formula, the first number would have been 44 instead of 45, even though I am still looking for the first number in the range from week 6 to week 1. What would change in the formula and can you explain why?
 
Slk123

In the original solution a formula in B3 would have looked up the 1st value based on

COLUMN()-COLUMN($A$1) which evaluates to 2-1 = 1

Now That cell is C3

which evaluates to

COLUMN()-COLUMN($A$1) = 2


Now in C3: Put

=INDEX($A$3:$L$3,,SMALL(IF($G3:$L3<>"",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)-1))

This is an array formula so it has to be entered with Ctrl Shift Enter

Then copy across
 
Hui,


That worked and I can now solve what I need.


Could you explain the part of the equation "small(if($G3:$L3<>"",Column($G3:$L3) works? I just can't seem to follow. I know it has to do with the blank spaces but I am pulling my hair out trying to figure it out.
 
I am looking at this problem in the Formula Forensic series this Thursday!

I hope you can wait until then.
 
You must log in or register to reply here.
Back
Top