| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Numeric Labels that have different Numeric Values
Hi All,
I would like to compare two sets of Numeric Values: if their values differ have their corresponding Numeric Label returned across a single row. I have 4 columns of data spanning 60 rows. The columns of data work in pairs: Column A Numeric Label "A17:A76" Column B Numeric Values "B17:B76" Column D Numeric Label "D17:D76" Column E Numeric Values "E17:E76" If a column "B" Numeric Label's value is (NOT equal) < to its corresponding Numeric Label's value in column "E", then return the Numeric Label of that Numeric Value. The Numeric Values in columns "B" and "E" are in descending order. All Numeric Labels should be returned across a single row. Sample Data Layout: Col "A" Col "B" Col "D" Col "E" Labels Values Labels Values 10 8 15 6 20 4 8 4 15 3 11 3 11 2 4 2 Required Results: Labels 11 and 15 from column "A" should be returned across a single row as their numeric values differ - column "B" Value is NOT equal to their corresponding Value in column "E". Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200612/1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Numeric Labels that have different Numeric Values
Try this (array entered: CTRL,SHIFT,ENTER):
=INDEX($A$17:$A$76,SMALL(IF(COUNTIF($D$17:$D$76,$A $17:$A$76),IF(SUMIF($A$17:$A$76,$A$17:$A$76,$B$17: $B$76)<SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76) ,ROW(A$17:A$76)-ROW(A$17)+1)),COLUMNS($A:A))) Copy across until you get #NUM! errors. If you want an error trap, maybe use conditional formatting to hide them. Biff "Sam via OfficeKB.com" <u4102@uwe wrote in message news:6a2b8a3ad4eec@uwe... Hi All, I would like to compare two sets of Numeric Values: if their values differ have their corresponding Numeric Label returned across a single row. I have 4 columns of data spanning 60 rows. The columns of data work in pairs: Column A Numeric Label "A17:A76" Column B Numeric Values "B17:B76" Column D Numeric Label "D17:D76" Column E Numeric Values "E17:E76" If a column "B" Numeric Label's value is (NOT equal) < to its corresponding Numeric Label's value in column "E", then return the Numeric Label of that Numeric Value. The Numeric Values in columns "B" and "E" are in descending order. All Numeric Labels should be returned across a single row. Sample Data Layout: Col "A" Col "B" Col "D" Col "E" Labels Values Labels Values 10 8 15 6 20 4 8 4 15 3 11 3 11 2 4 2 Required Results: Labels 11 and 15 from column "A" should be returned across a single row as their numeric values differ - column "B" Value is NOT equal to their corresponding Value in column "E". Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200612/1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Numeric Labels that have different Numeric Values
Hi Biff,
Thank you very much. Great Formula! Is it possible to have the Numeric Labels that are in columns "A" and "D" returned in ascending order across the row? Cheers, Sam T. Valko wrote: Try this (array entered: CTRL,SHIFT,ENTER): =INDEX($A$17:$A$76,SMALL(IF(COUNTIF($D$17:$D$76,$ A$17:$A$76),IF(SUMIF($A$17:$A$76,$A$17:$A$76,$B$17 :$B$76)<SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76 ),ROW(A$17:A$76)-ROW(A$17)+1)),COLUMNS($A:A))) Copy across until you get #NUM! errors. If you want an error trap, maybe use conditional formatting to hide them. Biff -- Message posted via http://www.officekb.com |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Numeric Labels that have different Numeric Values
That actually makes it easier (still array entered):
=SMALL(IF(COUNTIF($D$17:$D$76,$A$17:$A$76),IF(SUMI F($A$17:$A$76,$A$17:$A$76,$B$17:$B$76)<SUMIF($D$1 7:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17:$A$76)),COL UMNS($A:A)) Biff "Sam via OfficeKB.com" <u4102@uwe wrote in message news:6a2da75e7fa27@uwe... Hi Biff, Thank you very much. Great Formula! Is it possible to have the Numeric Labels that are in columns "A" and "D" returned in ascending order across the row? Cheers, Sam T. Valko wrote: Try this (array entered: CTRL,SHIFT,ENTER): =INDEX($A$17:$A$76,SMALL(IF(COUNTIF($D$17:$D$76, $A$17:$A$76),IF(SUMIF($A$17:$A$76,$A$17:$A$76,$B$1 7:$B$76)<SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$7 6),ROW(A$17:A$76)-ROW(A$17)+1)),COLUMNS($A:A))) Copy across until you get #NUM! errors. If you want an error trap, maybe use conditional formatting to hide them. Biff -- Message posted via http://www.officekb.com |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Numeric Labels that have different Numeric Values
You can clean that up and shorten it considerably (to where an error trap
could be added) if you use defined names: Counts: =COUNTIF($D$17:$D$76,$A$17:$A$76) Sum1: =SUMIF($A$17:$A$76,$A$17:$A$76,$B$17:$B$76) Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17: $A$76) Labels: =$A$17:$A$76 Then: =SMALL(IF(Counts,IF(Sum1<Sum2,Labels)),COLUMNS($A :A)) With an error trap: =IF(ISERROR(SMALL(IF(Counts,IF(Sum1<Sum2,Labels)) ,COLUMNS($A:A))),"",SMALL(IF(Counts,IF(Sum1<Sum2, Labels)),COLUMNS($A:A))) Biff "T. Valko" wrote in message ... That actually makes it easier (still array entered): =SMALL(IF(COUNTIF($D$17:$D$76,$A$17:$A$76),IF(SUMI F($A$17:$A$76,$A$17:$A$76,$B$17:$B$76)<SUMIF($D$1 7:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17:$A$76)),COL UMNS($A:A)) Biff "Sam via OfficeKB.com" <u4102@uwe wrote in message news:6a2da75e7fa27@uwe... Hi Biff, Thank you very much. Great Formula! Is it possible to have the Numeric Labels that are in columns "A" and "D" returned in ascending order across the row? Cheers, Sam T. Valko wrote: Try this (array entered: CTRL,SHIFT,ENTER): =INDEX($A$17:$A$76,SMALL(IF(COUNTIF($D$17:$D$76 ,$A$17:$A$76),IF(SUMIF($A$17:$A$76,$A$17:$A$76,$B$ 17:$B$76)<SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$ 76),ROW(A$17:A$76)-ROW(A$17)+1)),COLUMNS($A:A))) Copy across until you get #NUM! errors. If you want an error trap, maybe use conditional formatting to hide them. Biff -- Message posted via http://www.officekb.com |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Numeric Labels that have different Numeric Values
Ooops!
Made a mistake in copying/pasting: Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17: $A$76) Should be: Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76) Biff "T. Valko" wrote in message ... You can clean that up and shorten it considerably (to where an error trap could be added) if you use defined names: Counts: =COUNTIF($D$17:$D$76,$A$17:$A$76) Sum1: =SUMIF($A$17:$A$76,$A$17:$A$76,$B$17:$B$76) Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17: $A$76) Labels: =$A$17:$A$76 Then: =SMALL(IF(Counts,IF(Sum1<Sum2,Labels)),COLUMNS($A :A)) With an error trap: =IF(ISERROR(SMALL(IF(Counts,IF(Sum1<Sum2,Labels)) ,COLUMNS($A:A))),"",SMALL(IF(Counts,IF(Sum1<Sum2, Labels)),COLUMNS($A:A))) Biff "T. Valko" wrote in message ... That actually makes it easier (still array entered): =SMALL(IF(COUNTIF($D$17:$D$76,$A$17:$A$76),IF(SUMI F($A$17:$A$76,$A$17:$A$76,$B$17:$B$76)<SUMIF($D$1 7:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17:$A$76)),COL UMNS($A:A)) Biff "Sam via OfficeKB.com" <u4102@uwe wrote in message news:6a2da75e7fa27@uwe... Hi Biff, Thank you very much. Great Formula! Is it possible to have the Numeric Labels that are in columns "A" and "D" returned in ascending order across the row? Cheers, Sam T. Valko wrote: Try this (array entered: CTRL,SHIFT,ENTER): =INDEX($A$17:$A$76,SMALL(IF(COUNTIF($D$17:$D$7 6,$A$17:$A$76),IF(SUMIF($A$17:$A$76,$A$17:$A$76,$B $17:$B$76)<SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E $76),ROW(A$17:A$76)-ROW(A$17)+1)),COLUMNS($A:A))) Copy across until you get #NUM! errors. If you want an error trap, maybe use conditional formatting to hide them. Biff -- Message posted via http://www.officekb.com |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Numeric Labels that have different Numeric Values
Hi Biff,
Thank you very much for your assistance. Formula works Great! Cheers, Sam T. Valko wrote: That actually makes it easier (still array entered): =SMALL(IF(COUNTIF($D$17:$D$76,$A$17:$A$76),IF(SUM IF($A$17:$A$76,$A$17:$A$76,$B$17:$B$76)<SUMIF($D$ 17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17:$A$76)),CO LUMNS($A:A)) Biff -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200612/1 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Numeric Labels that have different Numeric Values
Hi Biff,
Abbreviated, error trapped version much appreciated. Brilliant! Counts: =COUNTIF($D$17:$D$76,$A$17:$A$76) Sum1: =SUMIF($A$17:$A$76,$A$17:$A$76,$B$17:$B$76) Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17: $A$76) Labels: =$A$17:$A$76 Then: =SMALL(IF(Counts,IF(Sum1<Sum2,Labels)),COLUMNS($ A:A)) With an error trap: =IF(ISERROR(SMALL(IF(Counts,IF(Sum1<Sum2,Labels) ),COLUMNS($A:A))),"",SMALL(IF (Counts,IF(Sum1<Sum2,Labels)),COLUMNS($A:A))) Cheers, Sam T. Valko wrote: Ooops! Made a mistake in copying/pasting: Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17: $A$76) Should be: Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76) Biff You can clean that up and shorten it considerably (to where an error trap could be added) if you use defined names: [quoted text clipped - 40 lines] Biff -- Message posted via http://www.officekb.com |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Numeric Labels that have different Numeric Values
You're welcome. Thanks for the feedback!
Biff "Sam via OfficeKB.com" <u4102@uwe wrote in message news:6a2eca8d2d648@uwe... Hi Biff, Abbreviated, error trapped version much appreciated. Brilliant! Counts: =COUNTIF($D$17:$D$76,$A$17:$A$76) Sum1: =SUMIF($A$17:$A$76,$A$17:$A$76,$B$17:$B$76) Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17: $A$76) Labels: =$A$17:$A$76 Then: =SMALL(IF(Counts,IF(Sum1<Sum2,Labels)),COLUMNS( $A:A)) With an error trap: =IF(ISERROR(SMALL(IF(Counts,IF(Sum1<Sum2,Labels )),COLUMNS($A:A))),"",SMALL(IF (Counts,IF(Sum1<Sum2,Labels)),COLUMNS($A:A))) Cheers, Sam T. Valko wrote: Ooops! Made a mistake in copying/pasting: Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17: $A$76) Should be: Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76) Biff You can clean that up and shorten it considerably (to where an error trap could be added) if you use defined names: [quoted text clipped - 40 lines] Biff -- Message posted via http://www.officekb.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search /Filter vertical Numeric pattern (down single column) | Excel Worksheet Functions | |||
| Find Numeric Criterion in Column & Return the Numeric Value from Row above | Excel Worksheet Functions | |||
| Return Summed Count of Multiple Consecutive Numeric Values | Excel Worksheet Functions | |||
| Match 3 Criteria and Return Lowest Numeric Value | Excel Worksheet Functions | |||
| Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Linear Mode
