MIN formula query

[snjpverma]

I have a number in A10 which decides how many numbers should be considered for the below given MIN function.
For e.g. if A10 says 2 then the MININUM of first 2 numbers should be the output i.e MIN of A1 & C2).
If A10 is 3 then MININUM of first 3 numbers in the given formula i.e. MIN of A1 & C2 & D2.
How to do this?

=MIN(A1,C2,D2,D5)

 

[wideboydixon]

The cells in question don't follow a pattern so you'll probably need to brute force it using IF:

=IF(A10=2,MIN(A1,C2),IF(A10=3,MIN(A1,C2,D2),MIN(A1,C2,D2,D5)))

WBD

 

[snjpverma]

What if I get the cells in line like this A1:A6 ?

BTW, if I use Choose function it works. but I don't know how to put those values in the Choose function.

Like this
=MIN(CHOOSE({1,2,3},A1,C2,D2,D5))

Can the CHOOSE function be of any help?

Thanks for all the help

 

[wideboydixon]

If you have the values in A1:A6 and you have the number to consider in A10 then:

=MIN(A1:INDEX(A1:A6,A10))

WBD

 

[bosco_yip]

or…………

Array formula, where A10 =1, 2, 3, or 4

=MIN(CHOOSE(ROW(INDIRECT("1:"&A10)),A1,C2,D2,D5))

Regards
Bosco

 

[snjpverma]

Thanks for your reply Bosco.

However, I would like to know how to create the highlighted sequence through a formula instead of manually typing it.
As shown below, the value for Choose function has a pattern (See the RED highlighted part below) i.e. first A1, then A1+A2, then A1+A2+A3 then A1+A2+A3+A4.
Also, the number to be subtracted i.e. B1 has a pattern (see the BLUE highlighted part) i.e. first B1*1, then B1*2 then B1*3 then B1*4...and so on.
Formula:
=MIN(CHOOSE(ROW(INDIRECT("1:"&A10)),A1-B1,A1+A2-2*B1,A1+A2+A3-3*B1,A1+A2+A3+A4-4*B1)

When it is just A1 then the B1 is multiplied by 1
When it is A1+A2 then the B1 is multiplied by 2
When it is A1+A2+A3 then the B1 is multiplied by 3
This is how the pattern isPlease advise on the same. Thanks for all the help that is provided on this forum.

 

[wideboydixon]

Try this:

=MIN(OFFSET($A$1,0,0,$A$10,1)-ROW(INDIRECT("1:"&$A$10))*$B$1)

Entered as an array formula.

WBD

 

[wideboydixon]

Strike that; I just realised that's not quite what you were after. Sorry.

WBD

 

[snjpverma]

Wbd, thanks for the update.
However, I have got the solution