formula >0

**Martindelica** · 03-22-2007, 08:29 PM

Awesome site, thanks.
I have 4 columns of numbers. Column 1 minus column 2 = answer(col3). Then I add column 4 to col 3.
Sometimes the answer between col1 and col2 is a negative number. This will throw off everything. Is it possible to have excel subtract col2 from col1 or vice versa which ever way will always give a positive?

25 32 col1-col2=-7
I hope it can be;
25 32 col1-col2=7 without having to change the forumla around or the columns. sometimmes I have over 1500 cells in a column and the way i do it nopw is go through manually and change it when i get negative.

Cheers, looking forwRD to help.
Martin.

**Trebor76** · 03-22-2007, 08:58 PM

Hi there,

You'll have use an IF statement that checks if the answer is less than zero and if it is either change the formula around or multiply the result by minus one i.e.,

If (col1 - col2 < 0, col2 - col1, col1 - col2) or
If (col1 - col2 < 0, (col1 - col2)*-1, col1 - col2)

HTH

Robert

**mudraker** · 03-22-2007, 09:00 PM

in col c use

=MAX(A1-B1,B1-A1)

**Paul** · 03-22-2007, 09:31 PM

Or, simply use the ABS function, which will give you the absolute value of any number.

=ABS(A1-B1)

You don't have to change every formula, either. You can easily update all of your formulas in a few clicks:

Change the first formula in column C (say, C1) then double-click on the fill handle in the lower right corner of that cell and it will automatically fill that formula down as many rows as are in the preceding column (as long as there are no blank rows). Rather than double-clicking, you could also click on the fill handle and drag down as many rows as you need.

**Martindelica** · 03-23-2007, 09:09 PM

Thanks you guys, I will try all of them. I do like the ABS formula though, but will both of them return a positive result? and I dont mean just changing the negative to a positive. It know to must subtract one from the other or change it around to get the right positive result. (col1-col2) or (col2-col1).

Kindest Regards,
Martin.

**Martindelica** · 03-24-2007, 12:58 AM

I got the ABS to work, sweet. Thanks much. If I have a row of numbers like the following:
1
2
3
4
5
6
7
8
9
10
11
12
13
Is there a way I can get Exl to highlight the numbers in the row that could be ancored at say 3, then add 2. It would highlight 5, then add 2 highlight 7 add 2 highlight 9 etc.
or if I start the sequence at 4 and add 6 it would highlight 10, then 16 then 22 then 28 then 34 etc.
Is this possible?

Kindest regards,
Martin.

**Bryan Hessey** · 03-24-2007, 01:53 AM

Originally Posted by Martindelica

I got the ABS to work, sweet. Thanks much. If I have a row of numbers like the following:
1
2
3
4
5
6
7
8
9
10
11
12
13
Is there a way I can get Exl to highlight the numbers in the row that could be ancored at say 3, then add 2. It would highlight 5, then add 2 highlight 7 add 2 highlight 9 etc.
or if I start the sequence at 4 and add 6 it would highlight 10, then 16 then 22 then 28 then 34 etc.
Is this possible?

Kindest regards,
Martin.

Hi,

select the column, Format, Conditional Format,
formula is
=IF(MOD((A1-4)/6,1)=0,TRUE)
set the Pattern required.

hth
---

**Martindelica** · 03-24-2007, 05:06 AM

Hi Bryan thanks, as usual worked like a charm. If I have a col of the following numbers:
23
25
26
28
29
34
32
30
29
26
23
22
The value goes up and down, is there a way to have excel list the numbers all going up? I dont mean sort. I mean result in the following;
23,25,26,28,29,34,36,38,39,43,46,47.
It would take the last number of the up slope and add the difference and so on. ie, 34-32=2, so it would be 34+2=36 then 32-30=2 add this to the 36=38...Exl would have to know when the number direction changes to go down and add the down numbers to the up going numbers to continue the sequence.

Warmest regards,
Martin.

**VBA Noob** · 03-24-2007, 05:23 AM

Hi,

In A2 enter = A1

Then in B2 enterthe below and drag down

=B1+ABS(A2-A1)

You will find the last 3 are different as you added 4 not 3

VBA Noob

**Martindelica** · 03-24-2007, 07:04 AM

This still gives me the values going up and then they go back down. I need them to contiue the pattern but going up.
23,24,25,26,27,28,27,26,25,24,23, Should the following
23,24,25,26,27,28,29,30,31,32,33,
or
15,17,19,22,36,48,39,37,29,28, Should be the following;
15,17,19,22,36,48,57,59,65,66,

Kindest regards,
Martin.

**Paul** · 03-24-2007, 01:36 PM

This still gives me the values going up and then they go back down. I need them to contiue the pattern but going up.
23,24,25,26,27,28,27,26,25,24,23, Should the following
23,24,25,26,27,28,29,30,31,32,33,
or
15,17,19,22,36,48,39,37,29,28, Should be the following;
15,17,19,22,36,48,57,59,65,66,

Martin, I just used VBANoob's formula and it worked perfectly. Are you sure you typed it correctly, or copy/pasted it in the right spot?

If your column of #'s are in A1:A10, in B1 put the formula, =A1
Then, in B2, put his formula, =B1+ABS(A2-A1)
Fill that formula down to B10. It should give you the correct results.

As Noob mentioned, the series you list in both of your last posts are not accurate.

15, 17, 19, 22, 36, 48, 39, 37, 29, 28 should become
15, 17, 19, 22, 36, 48, 57, 59, 67, 68 since there's a gap of 8 between 37 and 29, not a gap of 6

23, 25, 26, 28, 29, 34, 32, 30, 29, 26, 23, 22 should become
23, 25, 26, 28, 29, 34, 36, 38, 39, 42, 45, 46 since there's a gap of 3 between 29 and 26, not a gap of 4.

Also check to see that automatic calculation is turned on in Tools-Options-Calculation.

**Martindelica** · 03-25-2007, 11:59 PM

This site truely is awesome!. Having stumbled across it on the net, I have shaved countless hours of my work due to those members that have provided me the right formulas. Thanks sooooo much guys.
PS. Your absolutley right, the formula does work, I just typed in incorrectly. thanks again.

Martin.

formula >0

LinkBack

Thread Tools

Rate This Thread

Display

formula >0

Awesome

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions