Finding the first negative number in a series

**tracar11** · 01-14-2019, 10:15 AM

Trying to find the first negative number and showing that negative number and not showing the rest of the negative numbers using this format:
01/01/2016 2000.00
02/01/2016 1500.00
03/01/2016 1000.00
04/01/2016 500.00
05/01/2016 0.00
06/01/2016 (500.00)

So, using the chart above, I need something that will show the (500.00), but not the anymore in both columns. The date column is based off the number column and the formula is pasted down the rows by 50 rows for each row. I'll have A1 show the number we start at (i.e. 2000.00), A2 will have the number we reduce by (i.e. 500.00), A3 will show the starting date (i.e. 1/1/2016). Any suggestions?

**Aardigspook** · 01-14-2019, 10:56 AM

Welcome to the forum.

If you just want to hide the figures, not delete them entirely, then use Conditional Formatting. From what you say, it appears that your numbers start with 2000 in cell B3. Select B3:B52 (50 rows), click Conditional Formatting → New Rule → Use a formula to determine which cells to format. Enter this in the formula box:
=AND(B3<0,B2<0)
Click the Format button and choose to make the text white (or use the custom number format ;;; (three semi-colons) which will show the cell entirely blank).
The figures will still be there - you just won't see them anymore.

Does that do what you want?

**tracar11** · 01-14-2019, 04:18 PM

Yes, the conditional formatting worked. I had to adjust the formula you gave a little but it has so far worked for my instance. Thank you.

**Aardigspook** · 01-14-2019, 04:28 PM

You're welcome, glad I could help.

As you're new, you may not know that you can mark the thread as Solved if your original question has been taken care of so others know there's an answer here. Instructions to do it are in my sig - if you could take a moment to do it, that would be appreciated. Thanks.

Finding the first negative number in a series

LinkBack

Thread Tools

Rate This Thread

Display

Finding the first negative number in a series

Re: Finding the first negative number in a series

Re: Finding the first negative number in a series

Re: Finding the first negative number in a series

Thread Information

Users Browsing this Thread

Similar Threads

Finding the last negative number in a column

[SOLVED] finding a partial series of numbers within a number

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

finding number series in an array

Finding the next real number in a series

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

Finding the internal index number of a Chart Series.

2003= negative number&2004= negative number How Do I Calculate gro

Bookmarks

Bookmarks

Posting Permissions