#1   Report Post  
Posted to microsoft.public.excel.misc
dan dan is offline
external usenet poster
 
Posts: 866
Default 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.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default 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.



  #4   Report Post  
Posted to microsoft.public.excel.misc
dan dan is offline
external usenet poster
 
Posts: 866
Default 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.




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default 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.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to find the working days difference between to dates? Mudgeman Excel Discussion (Misc queries) 2 May 15th 06 04:26 AM
Difference between two months leitek.com Excel Worksheet Functions 5 March 13th 06 10:10 PM
Finding maximum of various ranges of data thekovinc Excel Discussion (Misc queries) 2 January 23rd 06 09:41 PM
Tricky 'Find Maximum' problem seeks formula tx12345 Excel Worksheet Functions 6 December 5th 05 11:26 PM
charting a difference of 2 columns' w/o adding a difference column Wab Charts and Charting in Excel 4 July 27th 05 02:37 AM


All times are GMT +1. The time now is 10:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"