# Change Multiple Different Negative Numbers All Into One Specific Number?

Apr 5, 2014
How Do Change Multiple Different Negative Numbers All Into One Specific Number? Anither Words How do I change one number into another Number

Lets say I have list of numbers like this

-24

-19

-37

-22

-24

-42

-26

-20

-28

-23

-28

-42

-23

-53 etc.

and I want all the negative number at -21 and down to be changed into -20 and all other numbers higher then -20 or whole numbers to be left alone.

So numbers -21 -22 -23 -24 .... -36 -37 -38 .... -45 -46 etc are all changed into -20 and all other numbers like -19 -18 -17 -16 .... 0 1 2 3 4 etc are all left alone.

I tried using the filter tab and couldn't see. I tried the replace key but that took to long changing each and every number from -21 all the way back to -99.

Is there an easier way to change all negative numbers in a specific zone of numbers (-21 thru -99) into one simple number so -21 would change into -20 and -22 would change into -20 and -23 would change into -20 etc etc etc.

View 6 Replies
ADVERTISEMENT
Feb 1, 2007

I know ASAP has a feature to do this but I need the code in a bigger macro that I wrote.

EX: -1 needs to be 1

View 9 Replies
View Related
May 18, 2009

I understand how to only sum negative numbers but my problem is pulling only certain ones from a list. I have tried numerous IF and SUMIF statments with no solutions. I have attached a worksheet showing what I am trying to do.

View 5 Replies
View Related
Jan 3, 2012

I need formula for if cell value = negative number e.g.(-2202) change cell value to 0

View 5 Replies
View Related
Jun 2, 2008

My 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...

View 9 Replies
View Related
Dec 6, 2007

I 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??

View 2 Replies
View Related
Apr 26, 2012

I have a database of over 200,000 parts

In this database i have cost and sale price. 20,000 parts have the numbers backwards.

if you loop the rows you can subtract =SUM(AE2-Y2) if that number is a negative number I would need the value of AE copied to Y2 and the value of Y2 copied to AE2. the 2 being the row number which would always change with the loop.

View 3 Replies
View Related
Dec 18, 2013

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.

View 5 Replies
View Related
May 2, 2007

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

View 6 Replies
View Related
Nov 25, 2006

a spreadsheet in Excel. I have names with scores. Then I have the winning score. I need a formula to find the score closest to zero and to display the name of the winner.

Ex: Names A1:A4 and Scores B1:B4. Winning Score in B6 and list name in B7.

Ana 16

Bob 2

Charles 8

David 11

Winning Score 10

Answer should be 11 which is David, since David is only -1 away compared to the others.

View 9 Replies
View Related
Feb 7, 2014

How do I change a Positive number into a Negative number inside a cell formula?

I have a figure in Cell AE 101 which has the positive number 141

I have another cell W1 which has the positive number of 437

I want to create a formula in cell X1 that takes the number in cell AE 101 of 141 and convert the number inside the formula of 141 into a negative number of -141 and then subtracts that number from Cell W1 of 437 leaving an equation inside the formula to read. 437-141 = 296

Since the number in Cell W1 changes all the time and the number in AE101 Changes all the time the new formulated number in X1 will change all the time too as the cells get updated.

View 6 Replies
View Related
May 12, 2009

I have a large dataset (24000 rows) that requires me to multiply two different columns of integers. In some cases, the two integers are both negative and multiplying them results in a product that is positive. I actually need that product to be negative rather than positive. I can't quite seem to figure out the best way to accomplish this.

View 5 Replies
View Related
Aug 1, 2007

I have data that comes from a subsytem that places the negative sign at the right of the number, so it is recognized as text. I can get around this using find and replace and then a second step to multiply that by -1, but is there a formula that can do this for me?

I was trying if(right(A1,1)="-",TBD,A1)

View 4 Replies
View Related
May 19, 2009

I have a workbook in Excel 2003 with two spreadsheet, a data sheet and a summary sheet. The data sheet contains account numbers in column A and monthly values from column B to M (April to March) for each account. Account numbers occur more than once in column A.

The summary sheet contains each account number as in the data sheet, but only once with a month column and a YTD (year to date) column. Part of my function is to report on a given month number as well as a year to date number as at that given month. For example if it is May, I need to report the results for May as well as April and May totals. Cell C 1 contains the value of the particular month (say month 1 equals April and month 12 equals March).

I need a formula or function to return the year to date results given a certain month (cell C1) for each account number. The sumif function does not help since it only returns values for 1 column. I need the formula to perform a sumif function but if I put in 2 in cell c1 I need the formula to add the values in columns B and C, if I put a 3 in cell C1 I need the formula to add the values in columns B, C and D.

View 8 Replies
View Related
Apr 27, 2007

I have a text field which contains multiple numbers ( reference #s and phone #s). I need to extract the reference # which will be either a 7 or 8 digit number. That number will not fall in any particular place in the string.

View 9 Replies
View Related
Nov 3, 2011

my worksheet has a range (AN2:AN10000), and I want to find the total number of occurrences that specific numbers occur.

Example:

I want to find out how many times in this range above the numbers from 11 to 15 occur (11,12,13,14 & 15).

View 6 Replies
View Related
Dec 17, 2009

I have a column of variances, these contain both negative numbers and positive numbers. I want to gather a sum of all the negative numbers and positive numbers separtely. Basically saying all the positive overeages = this amount And all the negative shortages = this amount. you can see the attached sample.

View 3 Replies
View Related
Sep 20, 2009

Usually this question is asked the other way around, but I have a somewhat unique problem. A certain website gives out tables filled with numbers. Positive numbers show in black font and negative numbers show in red font, but unfortunately, negative numbers do not include the minus sign -- the font is red and that's it!

I need a macro (or any other solution) that will turn the red font numbers to negative ones and would possibly format the cell to show negative numbers in red (I guess the last part is easier). The main problem is searching for the red font numbers and turning them negative.

View 9 Replies
View Related
Apr 2, 2007

How can I get the sum of a range excluding any negative numbers?

I've thought about using a new column and using if statement to separate but it would be easier if i could use the same column.

View 9 Replies
View Related
Aug 21, 2014

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 Related
Sep 23, 2009

I'm trying to sum negative numbers using the following formula, however for some reason it's not working in the way that it would if I flipped the < to a > sign.

=SUMPRODUCT((Data!$AB$10:$AB$3222=$C$58)*(Data!$L$10:$L$3222=D57)*(Data!$W$10:$W$3222

View 9 Replies
View Related
Oct 19, 2008

I have a very simple formula which is basically one cell minus another "=A1-B1".

When the values are equal, the cell this formula is in will display a zero. However, when the subtracted figure is greater than the first figure, i will then get a minus figure E.g. 10-11 = -1.

How can i make it that when the formula result is less than zero (i.e. a minus number) the figure displayed stays at zero but still using the minus funciton? Basically once it hits zero i need it to stop subtracting. This may be really simple but i cant figure it out.

View 4 Replies
View Related
Feb 24, 2009

I want to key-in positive numbers, which I really want Excel to interpret as being NEGATIVE numbers. In other words, I want to avoid repeatedly striking the MINUS key. How do I:

Format these Data-Entry cells to this?

Format these Data-Entry cells to return RED displays?

I'm using Excel 2000

View 2 Replies
View Related
Apr 24, 2009

I have constructed an inventory formula, subtracting Current Inventory (CI) from Required Inventory (RI), to generate a number of how many new products must be Ordered: RI-CI=O. The problem is that if CI exceeds RI, I get a negative number for "O".

Question: How can I structure the formula to give a zero for any sum less than 1?

View 31 Replies
View Related
Aug 17, 2009

I used this code to extract the value on the text: "11/09/09 backyard $400"

=IF(ISNUMBER(SEARCH("back",A4)),RIGHT(A4,LEN(A4)-FIND("$",A4))+0,"")

and return "400" but i've encountered a value which is negative (could be

-$400, or ($400); and i got an error of #value, could anyone resolve this problem?

View 6 Replies
View Related
Feb 10, 2006

How can I find the inverse function of sine and also how do you find powers

to negative numbers

View 10 Replies
View Related
Jul 19, 2006

IF formula

=IF(D9="","",IF(AND(G9>0,G10>0),"",IF(G9>=G10,G9,G10))+IF(E10=0,"",E10))

G9 and G10 can be positive or negative. If G9 or G10 is a negative number,

I want the greater of the two to be used and then to be added to E10. But

right now, Excel is saying -20 (which is G10) is greater than zero. I'm

getting a VALUE error.

View 14 Replies
View Related
Aug 7, 2006

I have been working on an excel sheet:

I am working with stock options. Therefore I must subtract the current stock price Cell C2, which has a value of 23 with the Options price which is D9, has a value of 34.79 then muliply it with the shares which is cell F9 which is 4000. So the formula that i have is (C2-D9)*F9 that gives me the sum which resides in cell I9(total outstanding profit).......This turns out to be a negative number. I want to make it so that if the sum of that formula produces a negative number, i want that negative number to just become a "0". Ive tried the IF function but could not produce any results.

View 14 Replies
View Related
Feb 4, 2012

I would like to see that I am continually saving on a per-day basis (as well as overtime). I allowance myself an allowance each day, but some days, I need to spend more money than that. The best solution I have to is keep a running total for myself, so that each day is affected by the preceding days. Therefore, if I spend too much, I now have a small defecit that needs to be corrected. This method keep me on track as far as saving and spending.

Question (and I copied the actual data from my spreadsheet):

Without having to manual enter the final calculations (because this is just a simple part of an overall larger, more complex spreadsheet), can I generate the values in my Value Change row, based on the information from my Running Total row?

For example, Day 1 to Day 2, I was able to incease my running total from 233 to 354 (meaning that I had saved +121 that day). Of course, this is an easy subtraction problem (Day 2 - Day 1 = Value Change). From Day 2 to Day 3, I spent way too much. The running total fell to -1198, meaning that I spent 1552 too much (that day's value change is -1552). Again, this an easy subtraction (Day 3 - Day 2 = Value Change, just like the first example). From Day 3 to Day 4, I was able to reduce my running total -1063, meaning that I was able to save 135 that day. This is my problem. Day 4 - Day 3 does not equal the value change of + 135. To get this value, the preceding formula now has to change to ABS(Day 3) - ABS(Day 4).

This is the issue. I need a formula that can work normally when the numbers are postive or when the second value is larger (in terms of absolute value). On the other hand, it needs to recognize the special cases like Day 3 - Day 4. Is this some form of a conditional function?

Running Total 233354-1198-1063-878-658-933-807-657

Percentage Change51.93%-438.42%11.27%17.40%25.06%-41.79%13.50%18.59%

Value Change121-1552135185220-275126150

View 5 Replies
View Related
Jul 23, 2008

Is there a way to select only negative #'s on a spreadsheet? I wasn't able to do it with "go to" ctrl-g.

View 9 Replies
View Related