CODEWITHSUNDEEP
excelexcel-formula
user2804258
user2804258

Reputation: 1

Use ADDRESS function to define range in MIN

I'm a newby to this forum. I have a spreadsheet which predicts bank balance based on future events. I want to know the minimum balance in the future, to ensure I don't overspend.

I can find the minimum in the account over whole range of dates by using MIN, but this includes past dates. I want to vary the range in the MIN argument based on the TODAY function.

I can return the cell reference I need to use for today's date using =ADDRESS(MATCH(DATEVALUE(TEXT(TODAY(),"dd mmm yy")),C:C,0),COLUMN(E2),4), where Column C contains dates, and Column E the bank balance. However, I cannot then use the value of the cell in the MIN formula.

So, at present, MIN(E10-E121) works for past and future levels, but if the ADDRESS routine for todays date returns E90 in cell A1, I cannot reference A1 within the MIN function to get the range E90:E121.

I have tried INDIRECT, but this gives the value of the cell at E90.

Upvotes: 0

Views: 1704

Answers (2)

Doug Glancy
Doug Glancy

Reputation: 27478

Assuming your data is in rows 2 to 100, this will give you the smallest amount anywhere in column E where the date in column C is today or later. It's an array formula, so must be entered with Ctrl-Shift-Enter:

=MIN(IF(C2:C100>=TODAY(),E2:E100))

EDIT:

In response to your comment about how to do it using one cell in a range, this uses Match to find the last cell in column C that is less than or equal to Today() and then uses that as the Index into the first half of a range specification in column E. It requires the column be sorted, ascending, by date, and if today isn't in column C it will start with the highest row in column C with a date before today. You could fiddle with the last argument in Match - 1, 0, or -1, but if C always contains today's date this will work:

=MIN(INDEX(E2:E100,MATCH(TODAY(),C2:C100,1)):E100)

Upvotes: 2

Ioannis
Ioannis

Reputation: 5388

Another solution that does not involve array formulas (but with a little more involved formula):

=SUMPRODUCT(MIN((C2:C100<TODAY())*(1+MAX(E2:E100))+(E2:E100)*(C2:C100>=TODAY())))

Upvotes: 0

Related Questions