Formula to return nth cell in a sequence with criteria

G

graemestown

New Member
Joined
May 10, 2019
Messages
20
Office Version
  1. 365
Platform
  1. Windows
https://www.dropbox.com/s/amwx4p2ofoau8w3/Example.xlsx?dl=0

[TABLE="width: 1408"]
<colgroup><col width="64" style="width:48pt"> <col width="64" span="20" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl71, width: 64"]1[/TD]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[TD="class: xl65, width: 64"]F[/TD]
[TD="class: xl65, width: 64"]G[/TD]
[TD="class: xl65, width: 64"]H[/TD]
[TD="class: xl65, width: 64"]I[/TD]
[TD="class: xl65, width: 64"]J[/TD]
[TD="class: xl65, width: 64"]K[/TD]
[TD="class: xl65, width: 64"]L[/TD]
[TD="class: xl65, width: 64"]M[/TD]
[TD="class: xl65, width: 64"]N[/TD]
[TD="class: xl65, width: 64"]O[/TD]
[TD="class: xl65, width: 64"]P[/TD]
[TD="class: xl65, width: 64"]Q[/TD]
[TD="class: xl65, width: 64"]R[/TD]
[TD="class: xl65, width: 64"]S[/TD]
[TD="class: xl65, width: 64"]T[/TD]
[TD="class: xl65, width: 64"]U[/TD]
[/TR]
[TR]
[TD="class: xl71"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD="class: xl66"]Week[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]6[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]11[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]16[/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"]4[/TD]
[TD="class: xl66"]Bread[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl65"]969[/TD]
[TD="class: xl65"]883[/TD]
[TD="class: xl65"]638[/TD]
[TD="class: xl65"]494[/TD]
[TD="class: xl65"]112[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]683[/TD]
[TD="class: xl72"]986[/TD]
[TD="class: xl65"]273[/TD]
[TD="class: xl65"]390[/TD]
[TD="class: xl65"]993[/TD]
[TD="class: xl65"]566[/TD]
[TD="class: xl65"]465[/TD]
[TD="class: xl65"]127[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"]5[/TD]
[TD="class: xl66"]Cake[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl65"]320[/TD]
[TD="class: xl65"]508[/TD]
[TD="class: xl65"]306[/TD]
[TD="class: xl65"]833[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]361[/TD]
[TD="class: xl65"]53[/TD]
[TD="class: xl72"]364[/TD]
[TD="class: xl65"]323[/TD]
[TD="class: xl65"]844[/TD]
[TD="class: xl65"]873[/TD]
[TD="class: xl65"]113[/TD]
[TD="class: xl65"]575[/TD]
[TD="class: xl65"]963[/TD]
[TD="class: xl65"]415[/TD]
[TD="class: xl65"]66[/TD]
[TD="class: xl65"]557[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"]6[/TD]
[TD="class: xl66"]Pies[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl65"]320[/TD]
[TD="class: xl65"]720[/TD]
[TD="class: xl65"]480[/TD]
[TD="class: xl65"]574[/TD]
[TD="class: xl65"]203[/TD]
[TD="class: xl65"]607[/TD]
[TD="class: xl65"]54[/TD]
[TD="class: xl72"]900[/TD]
[TD="class: xl65"]796[/TD]
[TD="class: xl65"]807[/TD]
[TD="class: xl65"]774[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"]7[/TD]
[TD="class: xl66"]Rolls[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl65"]602[/TD]
[TD="class: xl65"]404[/TD]
[TD="class: xl65"]801[/TD]
[TD="class: xl65"]340[/TD]
[TD="class: xl65"]977[/TD]
[TD="class: xl65"]767[/TD]
[TD="class: xl65"]583[/TD]
[TD="class: xl72"]933[/TD]
[TD="class: xl65"]731[/TD]
[TD="class: xl65"]285[/TD]
[TD="class: xl65"]335[/TD]
[TD="class: xl65"]833[/TD]
[TD="class: xl65"]462[/TD]
[TD="class: xl65"]858[/TD]
[TD="class: xl65"]872[/TD]
[TD="class: xl65"]360[/TD]
[TD="class: xl65"]932[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"]8[/TD]
[TD="class: xl66"]Donuts[/TD]
[TD="class: xl73"]12[/TD]
[TD="class: xl73"]16[/TD]
[TD="class: xl73"]20[/TD]
[TD="class: xl73"]23[/TD]
[TD="class: xl70"]92[/TD]
[TD="class: xl70"]121[/TD]
[TD="class: xl70"]717[/TD]
[TD="class: xl70"]523[/TD]
[TD="class: xl70"]105[/TD]
[TD="class: xl70"]180[/TD]
[TD="class: xl70"]263[/TD]
[TD="class: xl72"]98[/TD]
[TD="class: xl65"]923[/TD]
[TD="class: xl65"]138[/TD]
[TD="class: xl65"]470[/TD]
[TD="class: xl65"]516[/TD]
[TD="class: xl65"]460[/TD]
[TD="class: xl65"]518[/TD]
[TD="class: xl65"]266[/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl69, colspan: 2"]Criteria for formula:[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl68, colspan: 12"]Ignores blank cells and allows for an 'if greater than' statement. For example it ignores all the bold cells because they are less than 50[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl68, colspan: 5"]Then counts 8 cells from the start of the valid sequence[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl68, colspan: 6"]Formula returns the value in the 8th cell, the result I want returned is italic & underlined[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl71"][/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl68, colspan: 11"]Formula would be in column u which I could drag down and would work regardless of where the sequence starts[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1280"]
<colgroup><col width="64" span="20" style="width:48pt"></colgroup><tbody></tbody>[/TABLE]
 
Sort by date Sort by votes
Hi Dante

Hoping you see this question - not sure how else to get hold of you. I need your help again if that's ok.
I need to solve a 'forward cover' calculation. I work in retail.
Here is the problem
I end week 1 with 500 units of stock
If I sell 110 units in week 2 I end with 390 units of stock and so on.
My question is. How many weeks of future sales will each closing stock value service. Starting in week 1 I know the answer is 4 because there is enough stock for week 2, 3, 4 and 5 but how do I do this with a formula?

Weeks12345678910
Sales100110120130140150160170180190
Opening Stock500
Closing Stock3902701400
Weeks Cover??
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
B
  • Question Question
average based on time
Replies
1
Views
395
baqer
B
K
  • Solved
Return Column header for MAX/MIN Value
Replies
3
Views
268
kitm
K
G
  • Question Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
611
Greenbehindthecells
G
earthworm
  • Question Question
Split Data into separate worksheet based on Cell values
Replies
8
Views
595
earthworm
earthworm

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top