Offset function I think

[jeremypyle]

I have the following values in cells:

b2:4
c2:7
d2:3
e2:1


I want to put a formula in cell f1 that I can drag down that will do the following:

f1:1
f2:2
f3:3
f4:4
f5:1
f6:2
f7:3
f8:4
f9:5
f10:6
f11:7
f12:1
f13:2
f14:3
f15:1


How would I do this? I think I would use offset formula somehow. It may not be offset. Please let me know what formula to put in f1:

Thanks so much

 

[caabyyc]

I will need a formula as this is part of another formula

assume your data source is B2:B20 and the maximum number is 99

F1=IFERROR(RIGHT(SMALL(IF($B$2:$B$20>=COLUMN(A:ZZ),ROW($2:$20)/0.1%+COLUMN(A:ZZ)),ROW(A1)),3)-0,"") C+S+E 3 keys then copy down

 

[caabyyc]

Sorry, I just messed up the data source in last reply. correct formula is as follow

assume the maximum number in B2:e2 is 99

F1=IFERROR(RIGHT(SMALL(IF($B$2:$E$2>=ROW($1:$99),COLUMN($B$2:$E$2)/0.1%+ROW($1:$99)),ROW(A1)),3)-0,"")

Press C+S+E 3 keys to run then copy down

 

[jeremypyle]

Thanks for the formula. This formula though doesn't seem to work?

 

[caabyyc]

it is array formula, you must press 3 keys (control,shift and enter) together to run, and make sure you are using excel version 2010 or later.

 

[jeremypyle]

Hi caabyyc, I've tried using array but still having a problem getting this to work. Not sure what I am doing wrong