Formula to identify negative#

[jbryngelson]

Thanks for considering to help me. I have a table that includes an AGE row and a FUNDS row. If my FUNDS row goes negative, I want to report the AGE at which the FUNDS go negative. It is a large spreadsheet with many calculations and random returns, so I seek to know the time when the funds go negative.

[JohnTopley]

There are instructions at the top of the page explaining how to attach your sample workbook.

[jbryngelson]

Sorry, but when I tried to attach a sample, I received an IP Error, stating I needed to Whitelist my IP (VERY ODD). So in the most basic form: Row A = Age, Row B = Funds. I want to create a cell with a formula that inspects the FUNDS row looking for a negative (out of funds) number. I want it to tell me the AGE of the negative number. Age is 60-65, Funds is 10, 6, 2, -2, 0, 0. I would want the results of the formula to find 63 as the result. I hope this helps.

[AliGW]

Never heard of that before! Please explain exactly what steps you took to get to that message. Is there anything odd about your workbook? Did you prepare a sample, or were you trying to attach the entire dataset?

[jbryngelson]

I created a very small two row, 5 column spreadsheet and attached it per the process listed. When I hit submit to my Edit, I got a message..... Not sure if I can replicated. Just need this simple excel - vlookup or other formula help

[AliGW]

What name did you use for the workbook?

I appreciate you need help, and I and others will be willing to do so as soon as we have the information we need. I cannot visualise from your description what you want. Under normal circumstances, I would not ask for a screenshot, but if you can attach one, and really can''t attach the workbook, then please do so.

I'd still like to get to the bottom of the issue in case it happens again to another member. I am sure you will be willing to help us on this, too. Thanks.

[menem]

Maybe

=Lookup( 1 , --( Range of Funds < 0 ) , Range of Age )

for example your age in A5:A10 , fund in B5:B10 the formula will
=Lookup( 1 , --( B5:B10 < 0 ) , A5:A10 )

Regards.

[bsalv]

Please Login or Register  to view this content.

-6 >>>>> G7 = 1st cell with a negative value, so that row is 7 minus 1 = 6
In Excel2016 Shift+Ctrl+Enter instead of Enter (matrixfomula)

[jbryngelson]

Still struggling with this (Brain Fog - Damn you COVID). Cant upload a spreadsheet so I will try to explain better.
ROW A = Ages: I have a row of ages starting at 60 through 95.
ROW B = FUNDS: I have a row of calculated numbers that will generate either a positive amount, or a negative amount.
If a number in ROW B (FUNDS) is negative, I want the output to be the first instance to present the AGE from ROW A as my output. It needs to ignore the remaining numbers. For example, if the third calculation is a negative number, I want it to present 63, even if there are positive numbers after the initial negative number.

I will create this formula in Row D of a different Tab (not that it matters, it could be a different cell on the same sheet).

Thanks again for helping this Brain Fogged newbie.

[GeoffW283]

With your data arranged per post #9:

funds.png

I think the following in B5 works:

Please Login or Register  to view this content.

This is an array formula and needs to be committed via CTRL-SHIFT-ENTER

See the attached workbook

Let us know if this helps.

[jbryngelson]

That did not work unfortunately.

[AliGW]

Explain "did not work" - in what way? Details, please.

Are you still unable to attach a workbook? Are you still getting the error message you talked about? Just following up post #6 ...

[jbryngelson]

With your data arranged per post #9:

I think the following in B5 works:

Please Login or Register  to view this content.

This is an array formula and needs to be committed via CTRL-SHIFT-ENTER

See the attached workbook

Let us know if this helps.

OUTSTANDING - I did finally get this formula to deliver exactly what I needed. many thanks European Jeff

[AliGW]

Before you go, would you please reply to post #12 (regarding adding an attachment), please? Thanks.

[jbryngelson]

Thanks Ali for checking in, Post #10 from Geoff worked via an array. All Good

[AliGW]

That doesn't answer my question! Please don't ignore it ...

When you've done that, please mark the thread as solve (Thread Tools top right).

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[GeoffW283]

European Jeff

Well observed. And before Ali asks, yes, my current profile location is correct

Thanks for the added reputation jbryngelson