Styjun

Why is it "on the inside" and not "in the inside"?

Exploiting the delay when a festival ticket is scanned

Boots or trail runners with reference to blisters?

My employer is refusing to give me the pay that was advertised after an internal job move

What is the source of this clause, often used to mark the completion of something?

Can I attune a Circlet of Human Perfection to my animated skeletons to allow them to blend in and speak?

Is there a word to describe someone who is, or the state of being, content with hanging around others without interacting with them?

Should I intervene when a colleague in a different department makes students run laps as part of their grade?

Is it bad style if the personal first person narrator of a story dies during said story?

Why put copper in between battery contacts and clamps?

How can flights operated by the same company have such different prices when marketed by another?

Did Vladimir Lenin have a cat?

"Valet parking " or "parking valet"

How to innovate in OR

Was Donald Trump at ground zero helping out on 9-11?

What were the first chips with hardware support for SPI?

Should students have access to past exams or an exam bank?

What language is Raven using for her attack in the new 52?

Why would anyone ever invest in a cash-only etf?

Correct word for a little toy that always stands up?

Embedded C - Most elegant way to insert a delay

How can Paypal know my card is being used in another account?

Solve equation using Mathematica

Why is softmax function used to calculate probabilities although we can divide each value by the sum of the vector?

How to use a rank formula that ignores errors

How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?How to create strings containing double quotes in Excel formulas?Return empty cell from formula in ExcelShortcut to Apply a Formula to an Entire Column in ExcelIs it possible to force Excel recognize UTF-8 CSV files automatically?Recalculate unique rank with autofilter?AVERAGEIF(range, criteria) formula that ignores errors in the dataAVERAGEIFS: Weighted rankingsLoop or Array to a lot shifts from rankings?Can SUMPRODUCT be used in an array formula?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I am trying to build a rank formula that ignores errors.

I've tried:

=Rank(BN4,(if(iserror(BQ4:BQ31),"",BQ4:BQ31)))

I've also Tried:

 =IF(ISERROR($BQ$4:$BQ$31),"",1+SUMPRODUCT($BP$4:$BP$31=BP4,$BQ$4:$BQ$31>BQ4))

Is There something wrong with the formula? Is there a better way? See image below, the rank formula appears in Col BN. Thanks!

]1

excel excel-formula

share|improve this question

edited Mar 26 at 21:25

cybernetic.nomad

3,89722 gold badges1313 silver badges2626 bronze badges

asked Mar 26 at 21:17

austinmbaustinmb

3266 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I found this link from google-ing. excelforum.com/excel-formulas-and-functions/…
  
  – Mistella
  Mar 26 at 21:43
  

  
  
  
  

add a comment | 

0

I am trying to build a rank formula that ignores errors.

I've tried:

=Rank(BN4,(if(iserror(BQ4:BQ31),"",BQ4:BQ31)))

I've also Tried:

 =IF(ISERROR($BQ$4:$BQ$31),"",1+SUMPRODUCT($BP$4:$BP$31=BP4,$BQ$4:$BQ$31>BQ4))

Is There something wrong with the formula? Is there a better way? See image below, the rank formula appears in Col BN. Thanks!

]1

excel excel-formula

share|improve this question

edited Mar 26 at 21:25

cybernetic.nomad

3,89722 gold badges1313 silver badges2626 bronze badges

asked Mar 26 at 21:17

austinmbaustinmb

3266 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I found this link from google-ing. excelforum.com/excel-formulas-and-functions/…
  
  – Mistella
  Mar 26 at 21:43
  

  
  
  
  

add a comment | 

I am trying to build a rank formula that ignores errors.

I've tried:

=Rank(BN4,(if(iserror(BQ4:BQ31),"",BQ4:BQ31)))

I've also Tried:

 =IF(ISERROR($BQ$4:$BQ$31),"",1+SUMPRODUCT($BP$4:$BP$31=BP4,$BQ$4:$BQ$31>BQ4))

Is There something wrong with the formula? Is there a better way? See image below, the rank formula appears in Col BN. Thanks!

]1

excel excel-formula

share|improve this question

edited Mar 26 at 21:25

cybernetic.nomad

3,89722 gold badges1313 silver badges2626 bronze badges

asked Mar 26 at 21:17

austinmbaustinmb

3266 bronze badges

=Rank(BN4,(if(iserror(BQ4:BQ31),"",BQ4:BQ31)))

 =IF(ISERROR($BQ$4:$BQ$31),"",1+SUMPRODUCT($BP$4:$BP$31=BP4,$BQ$4:$BQ$31>BQ4))

share|improve this question

edited Mar 26 at 21:25

cybernetic.nomad

3,89722 gold badges1313 silver badges2626 bronze badges

asked Mar 26 at 21:17

austinmbaustinmb

3266 bronze badges

share|improve this question

edited Mar 26 at 21:25

cybernetic.nomad

3,89722 gold badges1313 silver badges2626 bronze badges

asked Mar 26 at 21:17

austinmbaustinmb

3266 bronze badges

edited Mar 26 at 21:25

cybernetic.nomad

3,89722 gold badges1313 silver badges2626 bronze badges

edited Mar 26 at 21:25

cybernetic.nomad

3,89722 gold badges1313 silver badges2626 bronze badges

asked Mar 26 at 21:17

austinmbaustinmb

3266 bronze badges

asked Mar 26 at 21:17

austinmbaustinmb

3266 bronze badges

2 Answers
2

active

oldest

votes

1

Given the spreadsheet below:

You can use the following formula on C2 and drag it:

=IF(ISERR(A2),"",COUNTIF($A$2:$A$7,">"&A2)+1)

share|improve this answer

answered Mar 26 at 21:59

LucasLucas

40788 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think that's what I'm doing? Just w/ sumproduct
  
  – austinmb
  Mar 26 at 23:21
  

  
  
  
  

add a comment | 

0

I think this is what's going on: in your second formula, your Sumproduct is still calculated with the errors. Although the first part your formula tells Excel to return a Blank when calculating the rank of an ERROR, the errors are stil inputted into second part where you actually calculate the rank, and thus Excel will spit out an Error even if it's calculating the rank of a regular number

The formula provided by Lucas above is probably the simplest way. If you must use sumproduct, you should include something in your sumproduct that tells excel to use a blank value if it comes across an error when calculating the some product.

For example, if you're trying to do the sumproduct of A1:A4 and B1:4 but the both ranges have some errors, then you'd use:

 =SUMPRODUCT(IF(ISNA(A1:A4),0,A1:A4),IF(ISNA(B1:B4),0,B1:B4))

share|improve this answer

answered Mar 27 at 3:39

Eric JohnsonEric Johnson

2844 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Usually, when mentioning another's answer in a different one, I add a link (from "share" under their answer). This is because a verbal mentioning can be insufficient if multiple other answers get added; especially since the order of the answers can shift depending on votes.
  
  – Mistella
  Mar 27 at 13:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for the tip, makes sense.
  
  – Eric Johnson
  Mar 27 at 13:59
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55366328%2fhow-to-use-a-rank-formula-that-ignores-errors%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Given the spreadsheet below:

You can use the following formula on C2 and drag it:

=IF(ISERR(A2),"",COUNTIF($A$2:$A$7,">"&A2)+1)

share|improve this answer

answered Mar 26 at 21:59

LucasLucas

40788 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think that's what I'm doing? Just w/ sumproduct
  
  – austinmb
  Mar 26 at 23:21
  

  
  
  
  

add a comment | 

1

Given the spreadsheet below:

You can use the following formula on C2 and drag it:

=IF(ISERR(A2),"",COUNTIF($A$2:$A$7,">"&A2)+1)

share|improve this answer

answered Mar 26 at 21:59

LucasLucas

40788 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think that's what I'm doing? Just w/ sumproduct
  
  – austinmb
  Mar 26 at 23:21
  

  
  
  
  

add a comment | 

Given the spreadsheet below:

You can use the following formula on C2 and drag it:

=IF(ISERR(A2),"",COUNTIF($A$2:$A$7,">"&A2)+1)

share|improve this answer

answered Mar 26 at 21:59

LucasLucas

40788 bronze badges

=IF(ISERR(A2),"",COUNTIF($A$2:$A$7,">"&A2)+1)

share|improve this answer

answered Mar 26 at 21:59

LucasLucas

40788 bronze badges

answered Mar 26 at 21:59

LucasLucas

40788 bronze badges

answered Mar 26 at 21:59

LucasLucas

40788 bronze badges

0

I think this is what's going on: in your second formula, your Sumproduct is still calculated with the errors. Although the first part your formula tells Excel to return a Blank when calculating the rank of an ERROR, the errors are stil inputted into second part where you actually calculate the rank, and thus Excel will spit out an Error even if it's calculating the rank of a regular number

The formula provided by Lucas above is probably the simplest way. If you must use sumproduct, you should include something in your sumproduct that tells excel to use a blank value if it comes across an error when calculating the some product.

For example, if you're trying to do the sumproduct of A1:A4 and B1:4 but the both ranges have some errors, then you'd use:

 =SUMPRODUCT(IF(ISNA(A1:A4),0,A1:A4),IF(ISNA(B1:B4),0,B1:B4))

share|improve this answer

answered Mar 27 at 3:39

Eric JohnsonEric Johnson

2844 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Usually, when mentioning another's answer in a different one, I add a link (from "share" under their answer). This is because a verbal mentioning can be insufficient if multiple other answers get added; especially since the order of the answers can shift depending on votes.
  
  – Mistella
  Mar 27 at 13:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for the tip, makes sense.
  
  – Eric Johnson
  Mar 27 at 13:59
  

  
  
  
  

add a comment | 

0

I think this is what's going on: in your second formula, your Sumproduct is still calculated with the errors. Although the first part your formula tells Excel to return a Blank when calculating the rank of an ERROR, the errors are stil inputted into second part where you actually calculate the rank, and thus Excel will spit out an Error even if it's calculating the rank of a regular number

The formula provided by Lucas above is probably the simplest way. If you must use sumproduct, you should include something in your sumproduct that tells excel to use a blank value if it comes across an error when calculating the some product.

For example, if you're trying to do the sumproduct of A1:A4 and B1:4 but the both ranges have some errors, then you'd use:

 =SUMPRODUCT(IF(ISNA(A1:A4),0,A1:A4),IF(ISNA(B1:B4),0,B1:B4))

share|improve this answer

answered Mar 27 at 3:39

Eric JohnsonEric Johnson

2844 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Usually, when mentioning another's answer in a different one, I add a link (from "share" under their answer). This is because a verbal mentioning can be insufficient if multiple other answers get added; especially since the order of the answers can shift depending on votes.
  
  – Mistella
  Mar 27 at 13:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for the tip, makes sense.
  
  – Eric Johnson
  Mar 27 at 13:59
  

  
  
  
  

add a comment | 

I think this is what's going on: in your second formula, your Sumproduct is still calculated with the errors. Although the first part your formula tells Excel to return a Blank when calculating the rank of an ERROR, the errors are stil inputted into second part where you actually calculate the rank, and thus Excel will spit out an Error even if it's calculating the rank of a regular number

The formula provided by Lucas above is probably the simplest way. If you must use sumproduct, you should include something in your sumproduct that tells excel to use a blank value if it comes across an error when calculating the some product.

For example, if you're trying to do the sumproduct of A1:A4 and B1:4 but the both ranges have some errors, then you'd use:

 =SUMPRODUCT(IF(ISNA(A1:A4),0,A1:A4),IF(ISNA(B1:B4),0,B1:B4))

share|improve this answer

answered Mar 27 at 3:39

Eric JohnsonEric Johnson

2844 bronze badges

 =SUMPRODUCT(IF(ISNA(A1:A4),0,A1:A4),IF(ISNA(B1:B4),0,B1:B4))

share|improve this answer

answered Mar 27 at 3:39

Eric JohnsonEric Johnson

2844 bronze badges

answered Mar 27 at 3:39

Eric JohnsonEric Johnson

2844 bronze badges

answered Mar 27 at 3:39

Eric JohnsonEric Johnson

2844 bronze badges