英文:
How to return the date of the first month with a negative value in excel
问题
=IF(MIN(B2:D2)<0, INDEX(B1:D1, MATCH(TRUE, B2:D2<0, 0)), "none")
英文:
I am doing a valuation of a company using excel, and I am struggling with the following:
The layout of the sheet is as follows (simplified for obvious reasons)
| Jan '23. | Feb '23. | March '23. | |
|---|---|---|---|
| Cash bal | 200 | 300 | -100 |
| Runway end | x. |
I am trying to find a formula that will return the date when the runway ends (in this case x should be March '23) And if the runway never ends (i.e. the cash bal never goes negative) return "none"
I tried an if function:
= if(range of cash bal < 0, range of months, "none")
This didn't work as it just returned "none" due to the fact it would only search the first column.
I have tried an H-lookup and x-lookup and also not been successful.
Any help will be greatly appreciated.
答案1
得分: 1
使用INDEX/AGGREGATE：
=IFERROR(INDEX(月份范围，AGGREGATE(15,7,(列(现金余额范围)-MIN(列(现金余额范围))+1)/(现金余额范围<0),1)),"无")
或者在Office 365 Excel中：
=TAKE(FILTER(月份范围，现金余额范围<0),"",1)
[![输入图片说明][1]][1]
[![输入图片说明][2]][2]
英文:
Use INDEX/AGGREGATE:
=IFERROR(INDEX(range of months,AGGREGATE(15,7,(COLUMN(range of cash bal)-MIN(COLUMN(range of cash bal))+1)/(range of cash bal<0),1)),"NONE")
Or with Office 365 Excel:
=TAKE(FILTER(range of months,range of cash bal<0,"NONE"),,1)
答案2
得分: 0
你可以使用 XLOOKUP 函数：
=XLOOKUP(TRUE, range_of_cash_bal < 0, range_of_months, "none")
如果你没有 XLOOKUP 函数，你可以使用 IFERROR(INDEX/MATCH)：
=IFERROR(INDEX(range_of_months, MATCH(TRUE, range_of_cash_bal < 0, 0)), "none")
英文:
You can use XLOOKUP
=XLOOKUP(TRUE,range_of_cash_bal<0,range_of_months,"none")
If you do not have the XLOOKUP function, you can use IFERROR(INDEX/MATCH
=IFERROR(INDEX(range_of_months, MATCH(TRUE,range_of_cash_bal<0,0)),"none")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论