Anyone know how I can add the row number to a MIN function output?

[DiagonalDog]

Hello,

So I'm trying to calculate breakeven for a capital budgeting template and I've got it to display the right number from the set of values using the MIN function, however I need to add the nper number or row number. Anyone know how to add the corresponding row number to an output like this?

Breakeven formula: =MINABS(L3:L8)

Breakeven numbers are in column L, row numbers are in column A.

[6StringJazzer]

You mean you want to show the row number of the value returned by MINABS? What do you mean "row numbers are in column A"? Are you talking about row numbers that are different than Excel row numbers? Based on your description, the row number is given by this formula, assuming your breakeven formula above is in cell M1, and you need the value in column A that is found in the same row as the value returned by MINABS:

Please Login or Register  to view this content.

[DiagonalDog]

Um sort of, but my row numbers are slightly different than the Excel row numbers because they're the number of cash flows. Hopefully this pic can help illustrate what I'm trying to do.
Excelp.jpg

Since .333 is in the row Cash Flow 3, I want to add the 3, but have it automated so no matter which row the breakeven is in it will add the corresponding row/cash flow number.

[6StringJazzer]

We need to start over again. There are two problems.

First, I need you to clarify what result you are looking for. What do you mean by "add the corresponding row number"? Using your screenshot as an example, what output do you want to see? 3.333? That's what "add" means but somehow it doesn't seem to make sense in this context.

Second, you need to describe what MINABS does. It is not a built-in Excel function. Your formula uses a range in column L but you are pointing us to a result that comes from column F.

Also, an image attachment has very little value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that. This allows us to see and experiment with your data, layout, formulas, code, and possibly attach a file with a completed solution.

[DiagonalDog]

You're correct, I'm sorry. This is my first time posting here so I'm not sure how much people are generally familiar with and what is expected of me.

1. Yes, I'd like to see 3.33 for that case, as the output is the number of cash flows until breakeven is reached. Since the breakeven number (.333) in this case can be in any row in the range, I need a function that can find the cash flow number it corresponds with and add them together.

2. MINABS does what those two functions do separately, just as one function. It converts the numbers in the range to the Absolute Values and then finds the minimum. Definitely should've clarified this as I had to "hack" Excel to do it (followed a simple online tutorial)

3. Didn't realize it was an option, though in retrospect it should've been obvious. I can definitely do so.

The problem in question is on Sheet 6: Book1.xlsx

[6StringJazzer]

The formula for MINABS that you showed in your first post is in cell O11, but in your screenshot you put the circle around O10. So either the screenshot is annotated wrong, or you plugged the wrong formula into your first post.

Based on your first post and ignoring the screenshot, you want this formula:

Please Login or Register  to view this content.

If, on the other hand, I should look at your screenshot and ignore the post, then you want

Please Login or Register  to view this content.

Hopefully one of those is what you need

[DiagonalDog]

Hmmm Both of these just gave me #NAME errors :/ Might just have to do some fiddling with the references or something. But I have actual homework I need to do first. Thank you!

[DiagonalDog]

Just tried it again and it worked perfectly! Thank you!

[6StringJazzer]

You're welcome! I got a NAME error also but that is because I don't have a function called MINABS defined. Your file doesn't have macros so I assume you must have built an add-in.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved.

Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."