How to return the first value in a range of values that equals a specific condition

Ryan Williams

I am trying to develop a formula to display when the last billing was sent based on a series of rows that show the amount that was billed for a specific month.

I have two columns: Billing Month and Billing Amount

I would like the formula to return the last month, in a list of Months (Jan-Dec), where the billing amount is greater than 0.

I.e.

Billing Month Billing Amount

January $535.28

February $673.18

March $1250.25

April $0.00

May $10.35

June $0.00

My hope is to have the formula return the very last month that has been billed. In this case May.

Is this even possible?

Paul Newcome

I would suggest inserting a helper text/number type column that can later be hidden. In this column, you would assign a number to each of the monthly text values using a nested IF statement.

=IF([Billing Month]@row = "January", 1, IF([Billing Month]@row = "February", 2, IF([Billing Month]@row = "March", 3, ....................................................................................., IF([Billing Month]@row = "December", 12))))))))))))

Then to pull the month, you would use...

=MAX(COLLECT([Helper Column]:[Helper Column], [Billing Amount]:[Billing Amount], @cell > 0))

Finally you would us another nested IF statement to convert the number pulled by the MAX/COLLECT back into the month text.