Hi Everyone,

I have a problem today. 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

Hi

This is my take on what I have, others may have something quicker and
cleaner.

Sub FindSum()

Dim mySht As Worksheet
Dim myCrng As Range, myNum As Range
Dim c As Range

Set mySht = Worksheets("Sheet1")
Set myCrng = mySht.Range("B2:I10")
Set myNum = mySht.Range("A1")

For Each c In myCrng
If Not c < myNum Then
With c
.Cells.ClearContents
End With
End If
Next c

End Sub

Change the ranges to suit.

The above range is assumed as Columns("B:I").
The answer Cells are assumed in Column("A") and relative to each row of
numbers and would be e.g "=Sum("A2:I2")"
Cell A1 can be set to any number of your choosing which gives you more
flexibility than using a definitive value.

Essentially, this will delete any number that is not less than the
number you set in Cell A1, which in your example was 50 and returned a
total of 142.

If the numbers that exceed your limit are not important then, deleting
them won't be too stressful, conversely you may want to wait until
someone else comes up with a better solution.

HTH
Mick.

tikrit wrote:

I have a problem today. 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.

Try this:

Function ClosestTo(lowerThan As Range, what As Range, _
Optional ByVal numResults As Long)
If numResults < 1 Then numResults = 3
Dim cell As Range, L0 As Long, L1 As Long
ReDim closest(numResults - 1) As String

'Find the desired cells.
For Each cell In what
If (cell.Value < lowerThan.Value) Then
For L0 = 0 To numResults - 1
If Len(closest(L0)) Then
If (cell.Value Range(closest(L0)).Value) Then
For L1 = numResults - 1 To L0 + 1
closest(L1) = closest(L1 - 1)
Next
closest(L0) = cell.Address
Exit For
End If
Else
closest(L0) = cell.Address
Exit For
End If
Next
End If
Next

'Add them up.
For L0 = 0 To numResults - 1
If Len(closest(L0)) Then outP = outP + Range(closest(L0)).Value
Next

ClosestTo = outP
End Function

The first argument is the cell containing the value to check against. The
second arg is the range to check. The third (optional) arg is how many
results you want. (If not specified, or less than 1, defaults to 3.)

ClosestTo can be called either from VBA or within a cell. In VBA, call it
like this:
x = ClosestTo(Cells(1, 1), Range("A2:H2"))
x = ClosestTo(Cells(1, 1), Range("A2:H2"), 2)

In a cell, call it like this:
=ClosestTo(A1,A2:H2)
=ClosestTo(A1,A2:H2,2)

To check cells that aren't continguous, enclose the addresses with an
additional set of parentheses:
=ClosestTo(A1,(A2,C2,E2,G2))
=ClosestTo(A1,(A2,C2,E2,G2),2)

--
Nationality doesn't determine beauty.

Auric__ wrote:

The first argument is the cell containing the value to check against.

Forgot to mention, if the first arg is a multi-cell range, you'll get an
error. (Type Mismatch in VBA; #VALUE! in a cell.)

--
The time is now, the now is here.

On Sat, 16 Jun 2012 11:42:24 +0000, tikrit wrote:


Hi Everyone,

I have a problem today. 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

This formula must be **array-entered**:

=SUM(LARGE(IF(Rng<=50,Rng),{1,2,3}))

Rng Refers To the cells containing your values.
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.