Smallest in Range every 'th
- Thread starter tiong999
- Start date
Sorry...i think its not solved.
Please review deeper file attached.
Thanks Buddy.
Regards,
Tiong999
If you have a longer table, your can use this array formula instead.
=MIN(C3,N(OFFSET(F3,,ROW(INDIRECT("1:"&ROUNDUP((MATCH(9^9,3:3)-COLUMN(F3))/3,0)))*3-2)))
p.s. Array formula to be confirmed by pressing SHIFT+CTRL+ENTER 3 keystrokes together.
Regards
Bosco
To get the smallest value of every 3rd in "Table" range and single value in "Data"
In C5 array (CSE) formula :
{=MIN(C3,N(OFFSET(F3,,ROW(INDIRECT("1:"&ROUNDUP((MATCH(9^9,3:3)-COLUMN(F3))/3,0)))*3-2)))}
the red color portion MATCH(9^9,3:3) return the last numeric value position in Row no 3, this give 16
become >>
{=MIN(C3,N(OFFSET(F3,,ROW(INDIRECT("1:"&ROUNDUP((16-6)/3,0)))*3-2)))}
and,
ROW(INDIRECT("1:"&ROUNDUP((16-6)/3,0)))*3-2 returns you array {1;4;7;10}
>>
{=MIN(C3,N(OFFSET(F3,,{1;4;7;10})))}
>>
=1 (desired result)
Regards
Bosco
Hello Bosco,
Thanks, u lead me know deeper excell trick.
But, suppose 1 of long table range cell contain blank (example cell J3=empty)...it will return 0 instead 3 as the minimum result i expected.
And the fact many cell will contain blank cell in long run table.
How am i supposed to modified the formula?
Btw, why must /3 as for ROUNDUP((16-6)/3,0) ??
Regards,
Tiong999
Thanks, u lead me know deeper excell trick.
But, suppose 1 of long table range cell contain blank (example cell J3=empty)...it will return 0 instead 3 as the minimum result i expected.
And the fact many cell will contain blank cell in long run table.
How am i supposed to modified the formula?
Btw, why must /3 as for ROUNDUP((16-6)/3,0) ??
Regards,
Tiong999
Last edited:
If Data/Table Range cell contain blank or 0, you want result to exclude 0.
Then, in C5 array (CSE) formula :
=MIN(IF(CHOOSE({1,2},C3,N(OFFSET(F3,,ROW(INDIRECT("1:"&ROUNDUP((MATCH(9^9,3:3)-COLUMN(F3))/3,0)))*3-2)))>0,CHOOSE({1,2},C3,N(OFFSET(F3,,ROW(INDIRECT("1:"&ROUNDUP((MATCH(9^9,3:3)-COLUMN(F3))/3,0)))*3-2)))))
p.s. Array formula to be confirmed by pressing SHIFT+CTRL+ENTER 3 keystrokes together.
Regards
Bosco
Last edited:
To get every 3rd value position in "Table" range.
=ROW(INDIRECT("1:"&ROUNDUP((16-6)/3,0)))*3-2
>>
=ROW(INDIRECT("1:"&ROUNDUP(10/3,0)))*3-2
>>
=ROW(INDIRECT("1:"&ROUNDUP(3.33333333333333,0)))*3-2
>>
=ROW(INDIRECT("1:"&4))*3-2
>>
={1;2;3;4}*3-2
>>
={3;6;9;12}-2
>>
={1;4;7;10}
Regards
Bosco
Sorry, i just played this formula and i get N/A as result.
Will u review it. I appreciated for your contribution.
Regards,
Tiong999
@bosco_yip Interesting solutions and amazing explanation 
@tiong999 Here is one other formula to get the answer. I may have misunderstood the problem. But assuming you want the minimum of user input (C3) or every 3rd item in a range (G3 : P3), you can use below formula as well.
=MIN($C$3,IF(MOD(COLUMN($G$3:$P$3)-COLUMN($F$3),3)=1,IF($G$3:$P$3<>0,$G$3:$P$3)))
Press CTRL+Shift+Enter to get the answer.
@tiong999 Here is one other formula to get the answer. I may have misunderstood the problem. But assuming you want the minimum of user input (C3) or every 3rd item in a range (G3 : P3), you can use below formula as well.
=MIN($C$3,IF(MOD(COLUMN($G$3:$P$3)-COLUMN($F$3),3)=1,IF($G$3:$P$3<>0,$G$3:$P$3)))
Press CTRL+Shift+Enter to get the answer.
Both solution worked like charm and im pleased to have 2 approach and lead me deeper on excell programming.
Thanks for your contribution.
Regards,
Tiong999