Nearest one or two or three numbers in a row of data

[tikrit]

Hi Everyone,

I have a problem tody. I tried in different ways but could not solve it.

Lest's say I have the following row of Data:

61 49 55 45 48 69 79 51

I need a code (Excel formula) that find the closest two or three numbers to 50 but lower than 50 (which in this case are 49 48 and 45)

I will then add them to each other and put the result in a single cell

Notes : the data must be in Row. Mine is Excel 2010.

Thanks in Advance for your help

[martindwilson]

=SUM(LARGE(A1:H1,COUNTIF(A1:H1,">=50")+1),LARGE(A1:H1,COUNTIF(A1:H1,">=50")+2),LARGE(A1:H1,COUNTIF(A1:H1,">=50")+3)) maybe
or even
=SUM(LARGE(A1:H1,{1,2,3}+COUNTIF(A1:H1,">=50")))

[martindwilson]

cross posted at mrexcel
http://www.mrexcel.com/forum/showthr...-a-row-of-data

[oeldere]

With several helpcolumn I come to this solution.

See the golden cells.

[Cutter]

How about an amendment to Martin's formula:

=SUM(LARGE(A1:H1,COUNTIF(A1:H1,">=50")+{1,2,3}))

[martindwilson]

@cutter been there done that lol

[tikrit]

With several helpcolumn I come to this solution.

See the golden cells.

Thank you so much, But I need it in one step only.

[tikrit]

How about an amendment to Martin's formula:

=SUM(LARGE(A1:H1,COUNTIF(A1:H1,">=50")+{1,2,3}))

I am trying it .... looks great.

[tikrit]

The problem solved by Martin's Formula.

=SUM(LARGE(A1:H1,{1,2,3}+COUNTIF(A1:H1,">=50")))


Thank you Martin.