formula to return difference in values

[it's an excel world]

Hello all,

I have a file that I need to pull back the difference in values in one column based on the values in a different column.

I have order numbers running down column A and the number of days it took to ship the each line on the orders in column B. So I may have order number ABC listed ten times down column A, but various numbers ranging from 1 - 20 running down column B on the same rows as order number ABC. I need a formula to identify the smallest number in column B for that particular order number in column A and return a zero. Then any number with a higher value than the smallest number would return the difference between that number and the smallest value.

Example: Rows 1-2 for order ABC, column B for rows 1-2 are 3 and 6. I would like a 0 to be returned for the 3 and a 3 for the 6.

I have dozens of order numbers running down column A, but only want to look at the number in column B for the within the same order number.


Thanks in advance!

 

[T. Valko]

Hello all,

I have a file that I need to pull back the difference in values in one column based on the values in a different column.

I have order numbers running down column A and the number of days it took to ship the each line on the orders in column B. So I may have order number ABC listed ten times down column A, but various numbers ranging from 1 - 20 running down column B on the same rows as order number ABC. I need a formula to identify the smallest number in column B for that particular order number in column A and return a zero. Then any number with a higher value than the smallest number would return the difference between that number and the smallest value.

Example: Rows 1-2 for order ABC, column B for rows 1-2 are 3 and 6. I would like a 0 to be returned for the 3 and a 3 for the 6.

I have dozens of order numbers running down column A, but only want to look at the number in column B for the within the same order number.


Thanks in advance!

Maybe this...

Book1
	A	B	C
2	ABC	3	0
3	ABC	6	3
4	DEF	1	0
5	DEF	8	7
6	DEF	17	9
7	GHI	2	0
8	JKL	5	0
9	JKL	5	0
10	JKL	6	1
Sheet1

Enter 0 in C2.

Enter this formula in C3 and copy down as needed:

=IF(A3=A2,B3-B2,0)

 

[CharlesChuckieCharles]

Surely C6 should be 16

 

[it's an excel world]

T. Valko,

Charles is correct.


Thanks!

 

[T. Valko]

Surely C6 should be 16

17 - 8 = 9

I must have misunderstood what the OP wanted.

 

[T. Valko]

T. Valko,

Charles is correct.


Thanks!

OK, I misunderstood what you were looking for.

Try this...

Enter 0 in C2

Enter this formula in C3 and copy down as needed:

=IF(A3=A2,B3-INDEX(B$2:B2,MATCH(A3,A$2:A2,0)),0)

 

[CharlesChuckieCharles]

Took me a little longer but

=IF(A2<>A1,0,B2-MIN(IF($A$2:$A$10=$A2,$B$2:$B$10,FALSE)))

entered as an array formula (Ctrl+Shift+Enter)
does the same job

 

[it's an excel world]

Worked like a charm T. Valko!

I'll try yours too Charles


Thanks again to everyone!!

 

[T. Valko]

Worked like a charm T. Valko!

I'll try yours too Charles


Thanks again to everyone!!

You're welcome. Thanks for the feedback!