If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
find a sequence
I want to get excel to look through a series of numbers and words, find the
words that match, calculate the difference between the two numbers associated with these words and then sum all of the differences from the list, e.g. fred 10 bill 21 ben 34 bob 21 fred 23 bill 23 fred 67 I want excel to tell me the sum of differences for fred as 57, bill as 2 and the rest all 0. I have about 200 lines to sort with about 100 combinations of words. Any ideas much appreciated. Thanks |
#2
|
|||
|
|||
find a sequence
Sort by 'words' ascending then 'numbers' descending.
Assume 'words' is Col A and 'numbers' is Col B In Col C, enter the formula... =IF(A2=A3,B2-B3,0) Copy down 200 rows and sum Col C. -- Hope this helps. Thanks in advance for your feedback. Gary Brown "LiAD" wrote: I want to get excel to look through a series of numbers and words, find the words that match, calculate the difference between the two numbers associated with these words and then sum all of the differences from the list, e.g. fred 10 bill 21 ben 34 bob 21 fred 23 bill 23 fred 67 I want excel to tell me the sum of differences for fred as 57, bill as 2 and the rest all 0. I have about 200 lines to sort with about 100 combinations of words. Any ideas much appreciated. Thanks |
#3
|
|||
|
|||
find a sequence
LiAD wrote:
I want to get excel to look through a series of numbers and words, find the words that match, calculate the difference between the two numbers associated with these words and then sum all of the differences from the list, e.g. fred 10 bill 21 ben 34 bob 21 fred 23 bill 23 fred 67 I want excel to tell me the sum of differences for fred as 57, bill as 2 and the rest all 0. I have about 200 lines to sort with about 100 combinations of words. Any ideas much appreciated. Thanks Assuming there will be a maximum of 2 entries for each word (unlike your sample data where there are three fred's)... With words in A2:A201 and numbers in B2:B201, array-enter the following in C2 and copy down to C201: =(LARGE(IF($A$2:$A$201=A2,$B$2:$B$201,0),1)-LARGE(IF($A$2:$A$201=A2,$B$2:$B$201,0),2))*(COUNTI F($A$2:A2,"="&A2)-1) Then you can SUM column C as needed. |
#4
|
|||
|
|||
find a sequence
I guess my question is are you really wanting the difference of the highest
value minus the lowest value? What if there are intermediary values? In your example, you give 3 values of Fred, 67, 23, and 10, and state a difference of 57. This is actually the high minus the low, which can be portrayed easily in the array** formula of: =MAX(IF(A$1:A$100=C12,B$1:B$100))-MIN(IF($A$1:$A$100=C12,$B$1:$B$100)) Where C12 is who you are looking for the difference for (such as Fred). However, if you want the overall difference of all the numbers, then in your example, it would be 114 for Fred, as 67-23=44, 67-10=57, 23-10=13, and subsequently 44+57+13 = 114 -- John C "LiAD" wrote: I want to get excel to look through a series of numbers and words, find the words that match, calculate the difference between the two numbers associated with these words and then sum all of the differences from the list, e.g. fred 10 bill 21 ben 34 bob 21 fred 23 bill 23 fred 67 I want excel to tell me the sum of differences for fred as 57, bill as 2 and the rest all 0. I have about 200 lines to sort with about 100 combinations of words. Any ideas much appreciated. Thanks |
Thread Tools | |
Display Modes | |
|
|