Finding Negative Numbers

**excelman13** · 09-30-2018, 02:42 PM

I have this sheet.

If there is/are negative number(s) between DATA cells written A Column, I want spot them writting YES or NO

As seen at the example:

- There is no negative number between A1:A3 so when DATA is written NO must be written just next cell (B3)

- There is no negative number between A3:A13, the result is same

- There is one negative number (A20) between A13:A26, YES is written next cell (B26)

I can arrange A column. The question is this:

What kind of function must be written at the B Column that;

When does Excel spot the "DATA", Excel must search the field between the last two DATA cells and if there is negative number or not, YES or NO must be written in the cell just next the last DATA cell ?

Thanks a lot for help.

DATA.jpeg

**mrice** · 09-30-2018, 02:59 PM

Try this user defined function

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a B1, enter =SpotNegativeNumbers(B1) and copy down

Remember to save the workbook as a macro enabled workbook .xlsm

**jason.b75** · 09-30-2018, 03:47 PM

User defined functions should be avoided when the task can be achieved efficiently with native functions.

In B2 and fill down

=IF(A2="Data",IF(MIN(INDEX(B$1:B1,IFERROR(MATCH("Data",B$1:B1,0),1)):B1)<0,"Yes","No"),"")

**excelman13** · 09-30-2018, 04:39 PM

mrice, Thank you for reply, it solved my problem.

joson.b75

=IF(A2="Data",IF(MIN(INDEX(B$1:B1,IFERROR(MATCH("Data",B$1:B1,0),1)):B1)<0,"Yes","No"),"")

with this solution I got this picture and it didn't detect the negative numbers. The picture is this:

DATA1.jpeg

**jason.b75** · 09-30-2018, 04:48 PM

Sorry, I just typed the formula in my reply and missed an error in the ranges, the formula should be looking at column A, not column B.

In B2 and filled down

=IF(A2="Data",IF(MIN(INDEX(A$1:A1,IFERROR(MATCH("Data",A$1:A1,0),1)):A1)<0,"Yes","No"),"")

**excelman13** · 09-30-2018, 06:32 PM

I think I make a mistake some where. But my result is this:

DATA3.jpeg

There must be NO next to the second DATA but no NO

**Phuocam** · 09-30-2018, 08:08 PM

Try:

=IF(A2="Data",IF(MIN(INDEX(A$1:A1,IFERROR(-LOOKUP(1,-ROW(A$1:A1)/(A$1:A1="DATA")),1)):A1)<0,"Yes","No"),"")

**jason.b75** · 09-30-2018, 11:29 PM

Have you changed the formula to match the ranges used in your data? It looks like you might have done it incorrectly.

I can't tell you what you have done wrong because I can't see the edited formula or the actual ranges used from your screen capture.

**excelman13** · 10-01-2018, 03:45 AM

It is OK now.
That is a kind of FOREX early warning sheet that will help too much.
Thanks a lot.

DATA4.jpeg

Finding Negative Numbers

LinkBack

Thread Tools

Rate This Thread

Display

Finding Negative Numbers

Re: Finding Negative Numbers

Re: Finding Negative Numbers

Re: Finding Negative Numbers

Re: Finding Negative Numbers

Re: Finding Negative Numbers

Re: Finding Negative Numbers

Re: Finding Negative Numbers

Re: Finding Negative Numbers

Thread Information

Users Browsing this Thread

Similar Threads

Percentile of only negative numbers in a row with both positive and negative numbers

Converting negative numbers with negative decimal numbers? (-32,-768)

[SOLVED] Finding average value for the positive and negative set of numbers

[SOLVED] Need help with finding first negative value in a list of numbers

[SOLVED] SUMIF positive and negative numbers - take double a negative number?

Calculating The Largest Negative Number within a Range of Positive & Negative Numbers

finding out pairs of positive & negative numbers!

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions