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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
570
smozgur
smozgur
B
  • Question Question
average based on time
Replies
1
Views
387
baqer
B
K
  • Solved
Return Column header for MAX/MIN Value
Replies
3
Views
266
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
571
Greenbehindthecells
G
earthworm
  • Question Question
Split Data into separate worksheet based on Cell values
Replies
8
Views
593
earthworm
earthworm

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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