Menu
Sample data
A B
1 Date Amount
2 Apr 1 $6,000
3 May 1 $4,250
4 June 1 $2,750
5 July 1 $1,000
6 Aug 1 -$0.075 <- This Cell/Row
7 Sept 1 -$0.2500
In a column of numbers (in reality 100-200 rows), when the value changes to negative, e.g. if these we're amounts owed on a loan, when the loan would be paid off by. Note the real difference between the numbers fluctuates based on interest, taxes, one-off payments etc. So I can't just count (total / payment) = number of months.
Is there a way to use Excel's formulas to determine this? This may be a case of requiring VBA (which is fine) but if I can avoid it, I'd like to.
942
1. If you want to count the number of negative values only, please apply this formula =COUNTIF($A$1:$D$7,"<0"). 2. And in the above formula, A1:D7 is the data range that you want to use.
Using IF functionThe IF function in Excel can be just as effective in making numbers negative. For example, if column A in Excel contains your list and you want column B to contain the negative values, the following IF formula can be used in column B: =IF(A2>0,A2*-1,A2).
If you need to find the last positive or negative number in a list of values, you can also apply array formula. Note: To get the last negative number of the list, you just need this formula: =LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 < 0, $A$2:$A$18)), and remember to press Ctrl + Shift + Enter keys together.
We can count cells that contain a negative number within a specified range of cells with the COUNTIF function. We will walk through the procedure by following the simple steps below.
The match function returns a range index
=MATCH(matchValue, range, matchType: 0=exact, 1=greater than, -1=less than
=MATCH(0, B2:B7, -1)
Match the first cell that is less than 0 in range B2:B7. From your sample data this would return 5
Use the Offset function to return a particular cell based on the index value
144
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
