Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ignoring text and errors with Sumproduct
I have a ranking formula for numerical values in Column J based on Text
categories in Column A: =SUMPRODUCT(--(A$13:A$2651=A13),--(J13<J$13:J$2651))+1 It doens't work because some of the cells in Column J include error or text values. How can I make this formula work so that it ignores these error/text values? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ignoring text and errors with Sumproduct
SteveC wrote...
I have a ranking formula for numerical values in Column J based on Text categories in Column A: =SUMPRODUCT(--(A$13:A$2651=A13),--(J13<J$13:J$2651))+1 It doens't work because some of the cells in Column J include error or text values. How can I make this formula work so that it ignores these error/text values? The best way would be to eliminate the error values in col J. Filtering out text is simple: =SUMPRODUCT(--(A$13:A$2651=A13),--ISNUMBER(J$13:J$2651),--(J13<J$13:J$2651))+1 Filtering out error values can't be done without using the IF function, and IF requires that formulas calling it be entered as array formulas when IF should return array results. If you can't eliminate the error values in col J, you'll have to use the array formula =SUM((A$13:A$2651=A13)*IF(ISNUMBER(J$13:J$2651),J1 3<J$13:J$2651))+1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ignoring text and errors with Sumproduct
Thanks Harlan. After playing around wtih it some more, I realized there was
an #N/A error in Cell A2651, which was bringing #N/A errors for all the cells where the formula below was pasted. Once I fixed CellA2651, I had no problems. I'm going to keep your formulas as a backup in case I get further errors... thanks a lot for your time. -SteveC "Harlan Grove" wrote: SteveC wrote... I have a ranking formula for numerical values in Column J based on Text categories in Column A: =SUMPRODUCT(--(A$13:A$2651=A13),--(J13<J$13:J$2651))+1 It doens't work because some of the cells in Column J include error or text values. How can I make this formula work so that it ignores these error/text values? The best way would be to eliminate the error values in col J. Filtering out text is simple: =SUMPRODUCT(--(A$13:A$2651=A13),--ISNUMBER(J$13:J$2651),--(J13<J$13:J$2651))+1 Filtering out error values can't be done without using the IF function, and IF requires that formulas calling it be entered as array formulas when IF should return array results. If you can't eliminate the error values in col J, you'll have to use the array formula =SUM((A$13:A$2651=A13)*IF(ISNUMBER(J$13:J$2651),J1 3<J$13:J$2651))+1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ignoring text and errors with Sumproduct
Hey, this is what I'm doing now:
=IF(ISNUMBER(J13),(SUMPRODUCT(--($A$13:$A$2650=$A13),--(J13<J$13:J$2650))+1),1000000) If the cell contains an error or text, I assign it a high number (1000000) so it is ranked at the very bottom of the screen. do you like this version? I'm using it in conjunction with this to assign % rank, where the formula above is in column AM. =IF(AM13/COUNTIF($A$13:$A$2500,A13)1,1,AM13/COUNTIF($A$13:$A$2500,A13)) See a follow up question in a separate post if you are interested -- trying to figure out how to easily use autofilter to show only the top 10%, 20% etc... thanks again for your comments/help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average of times where text may be in range | Excel Worksheet Functions | |||
Error setting shape text property | Charts and Charting in Excel | |||
Combining Text and Date for VLOOKUP | Excel Worksheet Functions | |||
need a refresher: text boxes on charts that use relational formulas? | Charts and Charting in Excel | |||
linking text cells | Excel Worksheet Functions |