maximum difference
 

I have two columns of data, say in column A and B. I would like to find the
maximum relative difference between the values in A1 & B1 vs. A2 and B2 vs.
A3 and B3 etc... Each column may have up to 300 entries. My simplistic way
to do this is to subtract the two numbers in a third column and then find the
max of this column. Is there another way to do this with a single formula or
function without an intermediate step such as creating the extra column?
Thanks.

Answer: maximum difference
 

Finding Maximum Relative Difference Between Two Columns

Yes, there is a way to find the maximum relative difference between two columns without creating an extra column. You can use the

Formula:

---

MAX 


---

function along with the

Formula:

---

ABS 


---

and

Formula:

---

DIVIDE 


---

functions to achieve this. Here are the steps:

1. In a blank cell, enter the following formula:
   Formula:

   ---

   =MAX(ABS(DIVIDE(A1,B1)-DIVIDE(A2,B2)), ABS(DIVIDE(A2,B2)-DIVIDE(A3,B3)), ABS(DIVIDE(A3,B3)-DIVIDE(A4,B4)), ...) 


   ---

2. Replace A1, B1, A2, B2, A3, B3, A4, B4 with the cell references of your data in columns A and B.
3. Continue the pattern of
   Formula:

   ---

   ABS(DIVIDE(A,B)-DIVIDE(C,D)) 


   ---

   for as many rows as you have data.
4. Press Enter to calculate the maximum relative difference between the values in each row.

This formula calculates the relative difference between the values in each row by dividing the values in column A by the values in column B, then subtracting the result of the second row from the first row, the result of the third row from the second row, and so on. The

Formula:

---

ABS 


---

function ensures that the result is always positive, and the

Formula:

---

MAX 


---

function returns the highest value of all the relative differences calculated.

maximum difference
 

Use =MAX(A1:A300-B1-B300) but completed with CTRL+SHIFT+ENTER (not just
ENTER)to make it an array formula. Unclear from question if you want A1-B1
or B1-A1, so adjust formula as needed.
If you want the absolute max (regardless of sign) use
=MAX(ABS(A1:A300-B1-B300))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dan" wrote in message
...
I have two columns of data, say in column A and B. I would like to find
the
maximum relative difference between the values in A1 & B1 vs. A2 and B2
vs.
A3 and B3 etc... Each column may have up to 300 entries. My simplistic
way
to do this is to subtract the two numbers in a third column and then find
the
max of this column. Is there another way to do this with a single formula
or
function without an intermediate step such as creating the extra column?
Thanks.

maximum difference
 

Thanks so much. That's exactly what I want to do.

"Bernard Liengme" wrote:

Use =MAX(A1:A300-B1-B300) but completed with CTRL+SHIFT+ENTER (not just
ENTER)to make it an array formula. Unclear from question if you want A1-B1
or B1-A1, so adjust formula as needed.
If you want the absolute max (regardless of sign) use
=MAX(ABS(A1:A300-B1-B300))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dan" wrote in message
...
I have two columns of data, say in column A and B. I would like to find
the
maximum relative difference between the values in A1 & B1 vs. A2 and B2
vs.
A3 and B3 etc... Each column may have up to 300 entries. My simplistic
way
to do this is to subtract the two numbers in a third column and then find
the
max of this column. Is there another way to do this with a single formula
or
function without an intermediate step such as creating the extra column?
Thanks.

maximum difference
 

CLR pointed out a type on my formulas. Please correct to
=MAX(A1:A300-B1:B300)
and
=MAX(ABS(A1:A300-B1:B300))

I had wrong sign between B1 and B300
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dan" wrote in message
...
Thanks so much. That's exactly what I want to do.

"Bernard Liengme" wrote:

Use =MAX(A1:A300-B1-B300) but completed with CTRL+SHIFT+ENTER (not just
ENTER)to make it an array formula. Unclear from question if you want
A1-B1
or B1-A1, so adjust formula as needed.
If you want the absolute max (regardless of sign) use
=MAX(ABS(A1:A300-B1-B300))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dan" wrote in message
...
I have two columns of data, say in column A and B. I would like to find
the
maximum relative difference between the values in A1 & B1 vs. A2 and B2
vs.
A3 and B3 etc... Each column may have up to 300 entries. My
simplistic
way
to do this is to subtract the two numbers in a third column and then
find
the
max of this column. Is there another way to do this with a single
formula
or
function without an intermediate step such as creating the extra
column?
Thanks.