Finding a number based on a criteria

[Henrik]

y brain is still on vacation....

If somebody could be kind to help out with this little issue I have with a
formula.

What formula do I use If I want to have a retun value that is the year in
which the last row number (7) is greater that 130 000kr ?

I want to have the result in an other sheet than this table is done. I've
tried IF and Hlookup formulas but in vain.

A B C D
1 Year 1 Year 2 Year 3
Year 4
2
3 7 106 890 kr 7 249 027 kr 7 394 008 kr 7 541 888 kr
4 6 316 330 kr 6 442 656 kr 6 571 509 kr 6 702 940 kr
5 350 000 kr 344 732 kr 339 201 kr 333 393 kr
6 350 000 kr 350 000 kr 350 000 kr 350 000 kr
7 90 560 kr 111 639 kr 133 298 kr 155 556 kr

Greatful for any kind of help!

Thanks

/Henrik

 

[Bob Phillips]

I assume the amounts are numbers formatted in that way.

=INDEX(A11,MIN(IF(A77>130000,COLUMN(A77))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Henrik]

To Bob Phillips;

Many Thanks!!

/Henrik

I assume the amounts are numbers formatted in that way.

=INDEX(A11,MIN(IF(A77>130000,COLUMN(A77))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)