Hi All
I have a sum function which is returning Zero for cells with no data only a formula. This is making it hard for me to rank my entries, as the zero automatically sets that person as best ranked.
Is there a way of removing the zero from the Sum Function? or getting my Rank to ignore entries listed with a zero? If(iserror) an If(Isblank) do not help, as the formula get make the cell appear containing data
Any help would be great please
Column AA is the Sum and Column AC is the rank
Last edited by 6StringJazzer; 09-27-2018 at 11:05 AM. Reason: Moderator clarified title
Your image doesn't tell us anything about your problem, or let us see your formulas. An image attachment has very little value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that.
The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
I have also updated your title, which should summarize your question.
Well, to not include zeros jou could use just SUMIF(range,"<>0") but you probably not want to sum zeros (the sum x+0 is still x :-)) but somehow treat them special.
As you mentioned "if(iserror)" (and seeing excel 2003 in your profile - is that true?)
you can try:
Formula:
Please Login or Register to view this content.
Best Regards,
Kaper
russellhiedi,
If you enter this formula in cell AC2 and then copy it down, it should do the trick. It will adjust the ranking for you based on how many zeros are in column AA. If you want to limit the range to not include the entire column, it would be ideal. Since I don't know how many rows are in your data, I included the whole column in the formula.
=IF(AA2=0,"",RANK(AA2,$AA:$AA,1)-COUNTIF($AA:$AA,0))
Thanks!
That worked perfectly - thank you.
Would you mind explaining how that formula works for my head. I think I get the IF = 0 shows blank. But why a countif on the end?
See if this helps with an explanation...
Ranking Data
HTH
Regards, Jeff
The IF simply handles the output in cases where the sum is 0. However, the RANK function will still count the zeros. The COUNTIF function is there so that the number of zeros in the column are subtracted from the RANK.Originally Posted by russellhiedi
As an example, let's say you have 10 zeros in your data. Then the zeros will all have a rank of 1 and the other sums will be ranked starting at 11 and going on. The COUNTIF($AA:$AA,0) counts the number of zeros that are in column AA... in this case 10. Since the ranking would start at 11, subtracting 10 from that will get you the rank of 1, 12 - 10 = 2, etc. That's how it works.
Hi Brant, your a legend.
That makes absolute perfect sense and you have a great way of explaining things.
Cheers for you help buddy ����
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks