Make Formula ignore blank cells

satch

New Member
Joined
Aug 16, 2004
Messages
22
Hi all, need help regarding how to make a formula ignore blank cells. I have formulas within a worksheet that looks at the values contained within a row of cells and then compares them with the values in the next row. There are 3 formulas, the first looks to see if the values in the upper row are larger, the second looks for larger values in the lower row and the third looks for matching values. The following shows how i am employing these in my worksheet, basically i'm analysing football results and the formulas give me a breakdown of games won, lost and drawn. The trouble i'm having is that the third formula that looks for matches is comparing blank cells with blank cells and so is distorting my results. How do i get it to ignore blank cells. I've searched through the message boards but after reading through them i'm not sure whether to use Isblank or Isnumber or whatever.
Any help will be greatly appreciated.

An example of my worksheet, the data being analysed is row C5:U5 compared with C6:U6
Footy Tables Rev2a.xls
ABCDEFGHIJKLMNOPQRSTU
5ArsenalH2012222052421431223
6A0001111001211121010
7AstonVillaH0201230323010002113
8A2221120001012201002
04 - 05 SCORES


This is where the formulas are, all are fine if the data range contains data in every cell. However, when i clear the contents to input new data my formula for counting draws, cell AM5, is counting blanks. Not what i want.
Hope i've made things clear, thanks in advance.
Footy Tables Rev2a.xls
AIAJAKALAMAN
3TOTHWHLDraw
4
5191504
679%0%21%
719946
04 - 05 SCORES


What happens when there is no data, Cell AM5 shows 19 draws. Not what i want.
Footy Tables Rev2a.xls
AIAJAKALAMAN
3TOTHWHLDraw
4
5190019
60%0%100%
719946
04 - 05 SCORES
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi,

How about a non-array entered function ...

=SUMPRODUCT(--(C5:U5>C6:U6),C5:U5)

(Hi Nimrod!)
 
Upvote 0
Hi, thanks for your suggestions, however, they do not give the result i want.
I tried your solution fireftyr but when i filled the range with test data in which there is four draws (so the formula should return 4) i got the return of 38!
I also tried your solution Nimrod but the value return is 2. I checked the test data and there are 2 instances of a draw being 0 0. See below, cells D5&D6 and J5&J6. Is this the reason why the return value is 2 and not 4? Does the formula need to be revised? Is it ignoring instances where the cells are 0 0.
Footy Tables Rev2a.xls
BCDEFGHIJKLMNOPQRSTU
4
5H2012222052421431223
6A0001111001211121010
7H0201230323010002113
8A2221120001012201002
04 - 05 SCORES


And your result Nimrod
Footy Tables Rev2a.xls
AIAJAKALAMAN
3TOTHWHLDraw
4
5171502
688%0%12%
719946
847%21%32%
04 - 05 SCORES

Hope you can help
Thanks people.
 
Upvote 0
To just count, something like ...

=SUMPRODUCT(--(C5:U5>C6:U6),--(ISNUMBER(C5:U5)))

And in your above example, it's 15, not four - at least with your example.
 
Upvote 0
Still not sure what you are getting at firefytr, tried your suggestion but still not working as i want. Not sure if i have explained myself clearly as to what i'm trying to achieve and whether this is proving confusing. :(
Also, the return i am looking for is 4 and not the 15 you see reurned. If you look at the example below i have highlighted the cells of data in question. Comparing the value of cells in top row with those in the row underneath on a column by column basis. Thus, there are 19 comparrisons in all.
Footy Tables Rev2a.xls
CDEFGHIJKLMNOPQRSTU
70201230323010002113
82221120001012201002
04 - 05 SCORES


Now, the calculations are undertaken in the following area. As you can see, using the data above returns the results below. Formula in Cell AK5 has calculated 15 instances where the top row cell is greater than the lower row, thus 15 home wins in this case. There are no Home losses, (Cell AL5) and four draws(Cell AM5).
Footy Tables Rev2a.xls
AJAKALAM
3TOTHWHLDraw
4
5191504
04 - 05 SCORES


This all works fine when the data range is full but as i've said earlier the formula for calculating draws breaks down when there is no or just a few entries in, counting blank cells as a match. Applying the suggestions from yourself and Nimrod and variations thereof are not quite what i'm looking for, although Nimrods suggestion seemed to work up to a point, i.e. it recognised matches where the cell contents was 1 or above, but not where the contents was 0 & 0.
Sorry if i seem to be going on a bit or being a pest, just that i'm sure there is a simple solution to this frustrating problem. Thanks for your patience.
Cheers
 
Upvote 0
Just thought i'd add that my Htmlmaker does not seem to showing the formulas in individual cells, just seems to be stuck on one for some reason.
 
Upvote 0
Ok, so you're looking to compare the two ranges and count only the pairs that EQUAL each other, but not zero? If that is the case, try ...


=SUMPRODUCT(--(C5:U5=C6:U6),--(C5:U5<>0))
 
Upvote 0
Nearly there, looking to compare the two ranges and count only the pairs that EQUAL each other, but INCLUDE zero (nil nil or zero zero is a valid score) but NOT empty cells. At the moment the ranges are full of data, in real usage they start off blank and will have entries added week by week, so blank cells have to be accounted for.
Thanks
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top