Microsoft Excel 2007

Hi, I have a table of numbers and I need to know

the sets of data per row that has the greatest

difference. egValue1 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.00Ideally 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.00MIKE

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 6The 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

You're welcome.

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History