Excell 2007 data comparison

Microsoft Excel 2007
October 30, 2009 at 19:23:27
Specs: Windows Vista
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.


See More: Excell 2007 data comparison

Report •


#1
October 30, 2009 at 20:16:17
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/


Report •

#2
October 30, 2009 at 20:57:39
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.


Report •

#3
October 31, 2009 at 05:46:21
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


Report •

Related Solutions

#4
October 31, 2009 at 11:40:03
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.


Report •

#5
October 31, 2009 at 14:04:05
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


Report •

#6
October 31, 2009 at 15:31:17
that's awesome. Thanks

Report •

#7
October 31, 2009 at 16:48:18
You're welcome.

Report •


Ask Question