Formula to return nth cell in a sequence with criteria

graemestown

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

1ABCDEFGHIJKLMNOPQRSTU
2WeekWeekWeekWeekWeekWeekWeekWeekWeekWeekWeekWeekWeekWeekWeekWeekWeekWeekWeek
312345678910111213141516171819
4Bread 969883638494112750683986273390993566465127=Formula
5Cake 3205083068331003615336432384487311357596341566557=Formula
6Pies 32072048057420360754900796807774=Formula
7Rolls 602404801340977767583933731285335833462858872360932=Formula
8Donuts121620239212171752310518026398923138470516460518266=Formula
Criteria for formula:
1Ignores blank cells and allows for an 'if greater than' statement. For example it ignores all the bold cells because they are less than 50
2Then counts 8 cells from the start of the valid sequence
3Formula returns the value in the 8th cell, the result I want returned is italic & underlined
4Formula would be in column u which I could drag down and would work regardless of where the sequence starts

<colgroup><col width="64" style="width:48pt"> <col width="64" span="20" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

<colgroup><col width="64" span="20" style="width:48pt"></colgroup><tbody></tbody>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Array formula

{=OFFSET(A3,0,MIN(IF(B3:T3<>"",IF(B3:T3>50,COLUMN(B3:T3)-2)))+8,,)}

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
Hi, could you help me again.

How would I sum the first 8 cells after criteria have been met - the criteria would be to ignore the first blank cells or cells less than 50.
So in the donuts row it would ignore values 12,16,20,23 and then sum the next 8 cells thereafter, 92+121+717+523+105+180+263+98=2,099

Thanks!
 
Upvote 0
Hi, could you help me again.

How would I sum the first 8 cells after criteria have been met - the criteria would be to ignore the first blank cells or cells less than 50.
So in the donuts row it would ignore values 12,16,20,23 and then sum the next 8 cells thereafter, 92+121+717+523+105+180+263+98=2,099

Thanks!


It is an array formula

{=SUM(OFFSET(A3,0,MIN(IF(B3:T3>50,COLUMN(B3:T3)-1)),,8))}
 
Upvote 0
Hi Again

The spreadsheet you are helping me build is incredible.

Could you help me write a formula that would do this:
For example in the Bread row, I need a formula that would return the week that the sales first started. Sales were first recorded in week 6 with 969 units.
So the criteria would be to ignore the first blank cells or cells less than 50, find the first cell, in this case 969 units, then the answer would be (week) 6.
In the cake row the answer would be (week) 3.
In the Donuts row the answer would be (week) 5.
 
Upvote 0
Hi Again

The spreadsheet you are helping me build is incredible.

Could you help me write a formula that would do this:
For example in the Bread row, I need a formula that would return the week that the sales first started. Sales were first recorded in week 6 with 969 units.
So the criteria would be to ignore the first blank cells or cells less than 50, find the first cell, in this case 969 units, then the answer would be (week) 6.
In the cake row the answer would be (week) 3.
In the Donuts row the answer would be (week) 5.

It is an array formula



{=INDEX($B$2:$T$2,,MIN(IF(B3:T3>50,COLUMN(B3:T3)-1)))}
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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