If A1 0 AND SUM(A2:A12) = 0 then A13 = Jan
If A2 0 AND SUM(A3:A12) = 0 then A13 = Feb
If A3 0 AND SUM(A4:A12) = 0 then A13 = March
etc.etc.

Rather than just using nested Ifs which is cumbersome I am trying:

Function Month(Integer)

Select Case Integer
Case a10,sum(a2:a12)=0
Month = "Jan"

etc....

help is much appreciated - thanks

try
Function mmm(rng As Range) As String
Dim numAry, monthAry, i As Long
numAry = Array(1, 2, 3): monthAry = Array("Jan", "Feb", "Mar")
For i = LBound(numAry) To UBound(numAry)
If rng(numAry(i), 1) < "" And _
Application.Sum(rng.Resize(rng.Rows.Count -
numAry(i)).Offset(numAry(i))) = 0 Then
mmm = monthAry(i): Exit For
End If
Next
Erase numAry, monthAry
End Function

use like

=mmm(A1:A12)

Thanks for this but cant get it to work exactly


"Seiya" wrote:

try
Function mmm(rng As Range) As String
Dim numAry, monthAry, i As Long
numAry = Array(1, 2, 3): monthAry = Array("Jan", "Feb", "Mar")
For i = LBound(numAry) To UBound(numAry)
If rng(numAry(i), 1) < "" And _
Application.Sum(rng.Resize(rng.Rows.Count -
numAry(i)).Offset(numAry(i))) = 0 Then
mmm = monthAry(i): Exit For
End If
Next
Erase numAry, monthAry
End Function

use like

=mmm(A1:A12)

Hi

Try this little thing in A13:

=TEXT(DATE(1,SUMPRODUCT(MAX(ROW(A1:A12)*(A1:A120) )),1),"MMM")

HTH. Best wishes Harald

"teresa" skrev i melding
...
If A1 0 AND SUM(A2:A12) = 0 then A13 = Jan
If A2 0 AND SUM(A3:A12) = 0 then A13 = Feb
If A3 0 AND SUM(A4:A12) = 0 then A13 = March
etc.etc.

Rather than just using nested Ifs which is cumbersome I am trying:

Function Month(Integer)

Select Case Integer
Case a10,sum(a2:a12)=0
Month = "Jan"

etc....

help is much appreciated - thanks

Harald - thats perfect
can you explain to me what the function below does - very grateful,
I understand each individual function but not working together

SUMPRODUCT(MAX(ROW(A1:A12)*(A1:A120)))

"Harald Staff" wrote:

Hi

Try this little thing in A13:

=TEXT(DATE(1,SUMPRODUCT(MAX(ROW(A1:A12)*(A1:A120) )),1),"MMM")

HTH. Best wishes Harald

"teresa" skrev i melding
...
If A1 0 AND SUM(A2:A12) = 0 then A13 = Jan
If A2 0 AND SUM(A3:A12) = 0 then A13 = Feb
If A3 0 AND SUM(A4:A12) = 0 then A13 = March
etc.etc.

Rather than just using nested Ifs which is cumbersome I am trying:

Function Month(Integer)

Select Case Integer
Case a10,sum(a2:a12)=0
Month = "Jan"

etc....

help is much appreciated - thanks

Hi

An array formula like that is like a package of individual formulas. These
formulas are
Row(A1)*(A10) ' returns 1 if A10, else 0
Row(A2)*(A20) ' returns 2 if A30, else 0
Row(A3)*(A30) 'and so on
Row(A4)*(A40)
....
MAX searches the highest of these results, and SUMPRODUCT wraps it up as an
array formula.

Then we use this result as month number in a date, and we convert that date
to text monthname. I guess you found out already, so just for the
archive....

Best wishes Harald

"teresa" skrev i melding
...
Harald - thats perfect
can you explain to me what the function below does - very grateful,
I understand each individual function but not working together

SUMPRODUCT(MAX(ROW(A1:A12)*(A1:A120)))

"Harald Staff" wrote:

Hi

Try this little thing in A13:

=TEXT(DATE(1,SUMPRODUCT(MAX(ROW(A1:A12)*(A1:A120) )),1),"MMM")

HTH. Best wishes Harald

"teresa" skrev i melding
...
If A1 0 AND SUM(A2:A12) = 0 then A13 = Jan
If A2 0 AND SUM(A3:A12) = 0 then A13 = Feb
If A3 0 AND SUM(A4:A12) = 0 then A13 = March
etc.etc.

Rather than just using nested Ifs which is cumbersome I am trying:

Function Month(Integer)

Select Case Integer
Case a10,sum(a2:a12)=0
Month = "Jan"

etc....

help is much appreciated - thanks