Styjun

Embedded C - Most elegant way to insert a delay

Why is this photograph shot with Delta 400 and developed with D76 1+1 so grainy

What Marvel character has this 'W' symbol?

How do I say "this is why…"?

Would it take any sort of amendment to make DC a state?

What force enables us to walk? Friction or normal reaction?

Rampant sharing of authorship among colleagues in the name of "collaboration". Is not taking part in it a death knell for a future in academia?

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

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

How do I make my photos have more impact?

Move arrows along a contour

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

Exploiting the delay when a festival ticket is scanned

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

Can a US President, after impeachment and removal, be re-elected or re-appointed?

Why does the Rust compiler not optimize code assuming that two mutable references cannot alias?

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

Coworker mumbles to herself when working, how to ask her to stop?

Find all the numbers in one file that are not in another file in python

May a hotel provide accommodation for fewer people than booked?

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

Does Ubuntu reduce battery life?

Correct word for a little toy that always stands up?

Why tantalum for the Hayabusa bullets?

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