Hi, I have a table of numbers and I need to know the sets of data per row that has the greatest difference. eg Value1 Value2 Value3 Product1 39.00 42.00 28.50 Product2 52.00 56.00 65.20 Product3 46.00 60.00 34.00 Product3 90.00 110.00 80.00 Product4 72.00 60.00 90.00 Ideally the solution will tell me per Product which Values have the greatest difference and return the two Values. At the moment I'm using a chart to graphically tell me, but it's not an ideal solution. Thanks for any assistance.

Would not the =MAX() and =MIN() functions give you what your looking for? In Cell E2 thru E6 the formula =MAX(B2:D2) In Cell F2 thru F6 the formula =MIN(B2:D2) Value 1 Value 2 Value 3 Max Value Min Value Product1 39.00 42.00 28.50 42.00 28.50 Product2 52.00 56.00 65.20 65.20 52.00 Product3 46.00 60.00 34.00 60.00 34.00 Product3 90.00 110.00 80.00 110.00 80.00 Product4 72.00 60.00 90.00 90.00 60.00 MIKE http://www.skeptic.com/

Do you really only have 3 values? (I'm guessing there's more to this story than we've been told.) Anyway, Mike's Max-Min suggestion might work for you, or if you want one formula, try this, assuming your values are in B1:D1... =IF(ABS(B1-C1)>ABS(B1-D1),IF(ABS(B1-C1)>ABS(C1-D1),B1&" "&C1,C1&" "&D1), IF(ABS(B1-D1)>ABS(C1-D1),B1&" "&D1,C1&" "&D1)) That's all one formula, split for viewing purposes.

Hi, Reading your question, it appears that you want to identify the product that has the largest spread in values, and the two values for that product that are the maximum and minimum. Ideally the solution will tell me per Product which Values have the greatest difference and return the two Values In the data you supplied, Product1 for instance has a spread of 13.50 (max 42.00, min 28.50) The last two products in the table both have the largest spread of values (30.00) If you want to identify the one or more products with the largest spread you could do the following: A B C D E F G H 1 Value1 Value2 Value3 Max Min Diff. Max diff. 2 Product1 39.00 42.00 28.50 42.00 28.50 13.50 3 Product2 52.00 56.00 65.20 65.20 52.00 13.20 4 Product3 46.00 60.00 34.00 60.00 34.00 26.00 5 Product4 90.00 110.00 80.00 110.00 80.00 30.00 Product4 6 Product5 72.00 60.00 90.00 90.00 60.00 30.00 Product5 7 Max diff. 30.00 The two products with the maximum difference are identified by their names. The formulas for row 2, cells E2 to G2 are: E2 =MAX(B2:D2) F2 =MIN(B2:E2) G2 =E2-F2 H2 =IF(G2=$G$7,A2,"") Note the $ signs in H2 Drag these formulas down as far as row 6 The formula in G7 is =MAX(G2:G5) The 'result' i.e., the products with the largest spread could be identified by using conditional formatting and the row of data or just the cells with the product name would be highlighted in colour, or bold etc. rather than having the Max diff. column. Regards

Great! thanks for all these responses. My table of data has hundreds of products and values to sift through (Which are also constantly changing, but that's a problem for another day). In the above example, I'd prefer Col H to return the actual Value name rather than the Product. But there's enough for me to play with now to try and get it working. thanks again.

Hi, Just change the cell reference 'A2' in cell H2, to 'G2' and it will return the difference rather than the name. If you want any of the cells to change colour or font when they are on a row with the maximum 'spread', use conditional formatting. Select cell A2 then select - Home, Styles, Conditional formatting, New Rule, and select Use a formula to determine ... and in the formula box enter =IF(G2=$G$7,TRUE,FALSE) Then select Format, and format as required. Click OK. Right click A2, and Copy, select the other cells in column A and right click and PasteSpecial - Formats, now any cell in column A that is on a row with the maximum 'spread' will show your selected format. Regards

that's awesome. Thanks