How to use formula to the Ans

[Sky]

Hi,

Is there a way or formula in the excel that could help to add the nearest
month to get the figure for Column F.
The month added up can be greater than or equal to Column F.
Column A to E is the month.
Column F is the Qty.
Column G is the column that I wish to get the month and Qty.

Jan-09 Feb-09 Mar-09 Apr-09 May-09 Qty Ans
100 100 100 100 300 May(100) Apr(0)
Mar(100) Feb(100)
100 100 100 100 250 May(100) Apr(100)
Feb(100)
100 100 100 100 300 May(0) Apr(100)
Mar(100) Feb(100)
100 100 100 100 50 May(0) Apr(100)
100 100 100 50 May(0) Apr(0)
Mar(100)

 

[Gary Brown]

I think only a user-defined-function (UDF) will do what you want.

 

[Sky]

Hi Gary,

Could you kindly provide to provide me the formula.

Thanks a lot
Kang Yong

 

[Gary Brown]

'/=============================================/
' Function Purpose:
' Add nearest month(s) to get Qty
' Months must be dates NOT text / Months must be in Row 1
' Function must select Qty column and range to be reviewed
' Example:
' A B C D E F G
'1 Jan Feb Mar Apr May Qty Ans
'2 100 100 100 200 =QtyMonthFind(A2:F2)
' will show: May() Apr() Mar(100)
Feb(100)
'/=============================================/
'
Public Function QtyMonthFind(rng As Range) As String
Dim dblQty As Double, dblResult As Double
Dim lCount As Long
Dim rngCell As Range
Dim sResult As String

On Error GoTo err_Function

Application.Volatile

sResult = ""
dblResult = 0

'set current cell for each function call
Set rngCell = Application.Caller

'if a quantity and at least one month have not been
' selected, then stop calculation
If rng.Count < 2 Then
GoTo exit_Function
End If

'get qty to compare to months
dblQty = rngCell.Offset(0, -1).Value

'get month values
For lCount = 2 To rng.Count
'if value is still too small, keep looking
If dblResult < dblQty Then
'add next month to the answer
sResult = sResult & " " & _
Format(Cells(1, rngCell.Offset(0, _
-lCount).Column).Value, "mmm") & _
"(" & rngCell.Offset(0, -lCount).Value & ")"
dblResult = dblResult + rngCell.Offset(0, -lCount).Value
End If
Next lCount

QtyMonthFind = sResult

exit_Function:
On Error Resume Next
Set rngCell = Nothing
Exit Function

err_Function:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Function: QtyMonthFind - Module: " & _
"Module1 - " & Now()
GoTo exit_Function

End Function
'/=============================================/

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

 

[Sky]

Hi Gary,

Sorry, I still cannot get the ans.
Could you kindly me an email with an excel file with the formula.
My email is (e-mail address removed)

Thanks a lot in advance