How do I create a formula that tells me if a negative number appears in a column.
for example, the following numbers are in cell A1:A5
10
15
-1
12
8
I need a formula in B2 that tells me if A1:A5 contains a negative number.
i have the volume of a reservoir in column A for lets say, 2 years, with monthly data (so 24 numbers in my list)
I also have some variables that feed into this list such as rainfall (fixed), and population (i.e water use, which i can manually change)
if i increase the population too much, the volume of the reservoir will at certain times of the year, fall into negative digits.
what i'm looking for, is a cell which works out the maximum value (in whole numbers) for my population, without any of the numbers in the reservoir volume list falling below zero.
I go negative in one column I want it to show up in the next column as a positive number because when I add my income and subtract my bills and the negative from last month to get the what cash I'll have left it screws up because negative subtract a negative is a positive. So how do i get the sum of say B9 to from -$5.00 to +$5.00 into C4 so when i subtract C4 From my income it wont add to it.
View 1 Replies View RelatedI would like to add to this formula.... if the result of "$L$6-Q15", from below, is 0 or a negative number, then the desired result would be 0 for this formula.
Possibly wrapping IF AND somewhere in this...
=IF((O15+(K16*$L$5))>=$L$6,$L$6-Q15,K16*$L$5)
This may sound simple, but is not (it might be easy for you hopefully)...... I have a string of numbers and the first few numbers can be either postive, negative or zero....Later on, the numbers all become negative......
I need to find the the cell which contains the first negative number in the consequtive negative numbers.....The difficult part is the numbers before the consequtive negative numbers can be anything......
is there a way to format a cell that will make the number a negative number regardless of what number I type; whether I type a positive or negative.
What i'm trying to accomplish is enter a number and the number will be negative. I don't want to have to type a negative in front of the number.
i need a self correcting formula to solve the following case:
In column A in 7 rows:
5
-9
3
2
-4
-7
1
I want to extract the positive numbers to column B in the amount of positive number rows(no skipped row/space):
5
3
2
1
So it would look somthing like this:
A B
5 5
-9 3
3 2
2 1
-4
-7
1
Is there a simple formula to do this? I have been doing IF functions but it is taking too long. And I have got the results I wanted.
=IF(A1>0,A1,"") ==> works
but I want the numbers to be one right after the other (no blank rows in between).
Is there a way to a formula that would show the following? A plus B = C but if A is below 0, stop the value at 0 then add column B:
Columns:
A B C
-2 5 5
1 5 6
0 5 5
How can I make excel to recongnize this: 123456- as a negative number?
View 7 Replies View RelatedHow would I write an IF statement that would tell me if a number in one cell is more negative than another?
For Example,
Cell L23 has a number of -10mm
Cell M23 has -20mm
How would i write an if statement in another cell saying that if cell 23 goes beyond -20mm "warning".
I keep getting an error saying #NAME?
VBA code to change a cells value in a column and the adjacent column to the right of it. If the column has a negative number in it, I need to put the absolute value of that number into the next cell over on the right. I then need to change the original column values to 0. I've added a spreadsheet that shows an example and the desired output.
View 3 Replies View RelatedI keep getting the correct answer, but it returns it as negative and I need a positive answer.
View 1 Replies View RelatedHere it is i needed to get the total of all negative and positive value.
View 2 Replies View RelatedI want to subtract column B from column A and put the result in column C. If the result is a negative I want a formula to turn it to 0.
View 4 Replies View RelatedI have numbers in range B1 to B100. How do I search for the first negative number (not the smallest negative number) within the range? Then, retrieve the Date indicated along A1 to A100?
View 8 Replies View RelatedI need formula for if cell value = negative number e.g.(-2202) change cell value to 0
View 5 Replies View RelatedI have this report that comes as a text file.
It makes negative numbers look like 1245.35-putting the - sign at the end. I need some way to make these negative numbers look like -1245.35. Only these negative numbes need to be re-formatted.
In one column, I have a formula that will produce a positive or negative number. If the number is negative, I want it to be automatically transfered to the adjacent cell/column. I don't want a positive number transfered though.
View 9 Replies View RelatedMy bank sends me my statement data as: Description, Date, Amount
...where the amount is text as a number followed by either a plus or minus.
Does anybody know how quickly convert the column of data into proper numbers?
I've been playing around with LEN, RIGHT and SEARCH, but not getting very far because I don't know how to extract the text to the left given the numbers vary in the number of digits...
I currently have the following in a cell:
=BDH(AHD!A3,"px_last","-5cm","-5cm")
This currently displays as -32 pulling this number out of bloomberg.
I now want to write a formulae in this cell that will display this as 32c if it is negative... and 32b if it is positive.
So if the number pulled out is negative I want it to have a c after it (and please have it delete the minus sign) and if positive, then have a b after it.
I came across this expression going through an Excel VBA code. Can someone plz explain what the argument (-1,1) points to ?
View 9 Replies View RelatedI have row of dates and underneath those dates I have a row of numbers. The numbers count down. For example:
[Code] .....
At the end of the row I want a cell to return the date that the row first started going negative at. In this example, it should display 10/31/2014 (for when it went to -60).
I am trying to write code that will locate either a 0 or the smallest negative number in a range, eg,
example 1,
8, 6, 2, 0, -0.5, -2
the result would be 0
example 2,
8, 6, 2, -0.5, -2
the result would be -0.5
I'm trying to come up with a formula to seach through an array backwards, and tell me where the first negative number is located.
Example
A1
1
B1
1
C1
-1
D1
1
[Code] ..........
If you fill out the cells A1:J1 like so, and enter
=MATCH(TRUE,A2:T2
I need to have a formula or code so that when a number is entered in cell E12 or F12 or L12, or M12 would treat a positive number as a negative and a negative number entered would be a positive in that respective cell.
View 9 Replies View RelatedI am wanting to finish a worksheet for my boss. What I have done so far is taken the " forecast" of vehicles shown and used a formula of subtract from "actual" vehicles sold to get "how many we have left to go". what he wants to do is have the "to go" number change from red when a negative to green when it reaches a positive. example:
forecast = 30
actual = 27
to go = -3
that "to go" needs to be without a negative sign but in RED to negate a negative. Is that possible with a formula??
I am using a regular expression in a function to extract numbers from strings. the code I am using is below.
Function ExtractNumber(rCell As String)
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Pattern = "d*.d*"
If RegEx.Test(rCell) Then
ExtractNumber = CDbl(RegEx.Execute(rCell)(0))
Else
ExtractNumber = vbNullString
End If
End Function...
Can I use Conditional formatting (highlights duplicate values) but highlight the number even if the number is an Positive or Negative number.
It must highlight the number if it's -300 or 300 in both instances.
I'm trying to figure out a formula to use that will exclude negative numbers. I have two dates. I'm trying to find the difference between the two dates assuming there is a date in the first column. At times there will be a situation where the date in A1 is before the date in A2 and it returns a negative number. I'd like to return a blank if there is a blank cell in column A and also return a blank if the return number is a negative.
Example:
A1 = 1/15/2013 B1= 1/10/2013 C1(formula) =IF(A1="","",(A1-B1) C1 will then reflect 5
A2 = (cell is blank) B2= 1/18/2013 C2 (formula) =IF(A2="","",(A2-B2) C2 will then reflect a blank cell
A3 = 1/5/2013 B3 = 1/15/2013 C3 (formula) =IF(A3="","",(A3-B3) C3 will then reflect -10
I'm trying to figure out a formula that would also allow C3 to reflect a blank cell since the formula returns a negative.
Let's say I have a column A with the following values.
30
40
30
60
-20
-10
-50
-60
-70
120
320
20
-40
-30
40
How can I have 2 cells display:-
i)highest streak of positive numbers = 4
ii)highest streak of negative numbers = 5?
Also, how can I have another 2 cells display:-
iii)the sum of the highest streak of positive numbers = 160
iv)the sum of the highest streak of negative numbers = -210