Last N Postion

[bloubul]

Help please.

I'm using from time to time the following formula's:
=LastNPosition(C$4:C$300,Draws??)
=LastNPosition(D$4$300,Draws??)
=LastNPosition(E$4:E$300,Draws??)
=LastNPosition(F$4:F$300,Draws??)
=LastNPosition(G$4:G$300,Draws??)
=LastNPosition(H$4:H$300,Draws??)
But it is very time consuming to type it over and over for each draw that you want. Is there a other way that I must write the formula so that it can be copied,and that it count downwards to the number of draws I want, or can a macro to it more quickly and faster for me.
Sorry for the face in one formula, but I can't get rid of it.

BlouBul

 

[Frank]

Help please.

I'm using from time to time the following formula's:
=LastNPosition(C$4:C$300,Draws??)
=LastNPosition(D$4$300,Draws??)
=LastNPosition(E$4:E$300,Draws??)
=LastNPosition(F$4:F$300,Draws??)
=LastNPosition(G$4:G$300,Draws??)
=LastNPosition(H$4:H$300,Draws??)
But it is very time consuming to type it over and over for each draw that you want. Is there a other way that I must write the formula so that it can be copied,and that it count downwards to the number of draws I want, or can a macro to it more quickly and faster for me.
Sorry for the face in one formula, but I can't get rid of it.

BlouBul

It is not clear from you example what you are trying to achieve, since you use Draws?? in the cells without explaining what in reality Draws?? actually is. Have you got several rows like this one, have the rows below got reducing draw numbers ?
What do you want to happen ? If copying downwards, the range being acted upon by your custom function is locked in its dimensions downwards by the dollar signs, that part of the formula will not change. Is that what you wanted ?
The only thing free to change when copying down is the draw number. Where is that coming from ?

Can you not have another column containing the reducing draw numbers which you can link to ?

If the formulas you quote were on row 5, and you had the draw number you require in cell A5
then in column C formula =LastNPosition(C$4:C$300,$A5) would complete your formula, which you could copy down, provided column A downwards contained descending draw numbers. Copy across to be the same and amended appropriately for the other formulas in cols DEFG and H.

Did this solve the problem ?

 

[time*treat]

Help please.

I'm using from time to time the following formula's:
=LastNPosition(C$4:C$300,Draws??)
=LastNPosition(D$4$300,Draws??)
=LastNPosition(E$4:E$300,Draws??)
=LastNPosition(F$4:F$300,Draws??)
=LastNPosition(G$4:G$300,Draws??)
=LastNPosition(H$4:H$300,Draws??)
But it is very time consuming to type it over and over for each draw that you want. Is there a other way that I must write the formula so that it can be copied,and that it count downwards to the number of draws I want, or can a macro to it more quickly and faster for me.
Sorry for the face in one formula, but I can't get rid of it.

BlouBul

Formulas can be automated. It's easier if they are in R1C1 notation. Is Draws?? a custom function?

 

[bloubul]

Frank / time*treat.

I do not know what went wrong with my copy and paste, the formula should have been =LastNPosition(H$4:H$300,Number of Draws??).

time*treat
The "Draws" is not a custum funtion.

Frank
Thanks for your advise it works great.

BlouBul