All about numbers

  • Thread starter Thread starter andrew
  • Start date Start date
A

andrew

I have the following table:

A B C D
3 1 0.5 0
2 2 0 0.5
3 2 0 0
0 1 0.5 0
1 0 0 0
2 1 0 1
1 2 0.5 0
4 1 0 1.5

The formula required is to:
1) check if any row in column C has 0 (zero) and see corresponding cell in
column D also has 0 (zero). Table above shows row 3 and 5.
2) if matches, then check if (cell A - cell B) of the same row where the
above condition is true returns a positive value.
3) if a positive value is returned, then count it. Above example will return
a value of 2 (both occurences of 0 in C3 & C5).

Can anyone help?
 
One way
Assume data as posted within A1:D8
Placed in say, F1:
=SUMPRODUCT((C1:C8=0)*(D1:D8=0)*(C1:C8<>"")*(D1:D8<>"")*(A1:A8-B1:B8>0))

The "additional" conditions: .. *(C1:C8<>"")*(D1:D8<>"")*
are precautions taken since blank cells are evaluated as zeros
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
 
I used this earlier but didn't work:

=SUMPRODUCT((C2:C9=0)*(D2:D9=0)*((A2:A9)>(B2:B9)))

When i checked, some rows in my actual data were empty. Will try again.
 
Hi Max,
It worked on my test file BUT on the actual file (which extends the rows to
800), it doesn't seem to be working.

I realised that the cells in reference has formulas within them (all four
columns per below). Eg Column A =IF(ISBLANK(Fix!A7),"",Fix!A7)

Is this a problem?
 
some rows in my actual data were empty.

Which columns can be empty? Any of them?

This assumes every column *must* have an entry *and* the values in columns
C&D will always be positive:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(A1:D8)),{1;1;1;1})=4),--(A1:A8-B1:B8>0),--(C1:C8+D1:D8=0))
 
I realised that the cells in reference has formulas within them (all four
columns per below). Eg Column A =IF(ISBLANK(Fix!A7),"",Fix!A7)
Is this a problem?
Click to expand...

So, any cell in a row may contain a formula blank? If that's the case
disregard the suggestions I've already made.

=SUMPRODUCT(--(MMULT(--(ISNUMBER(A1:D8)),{1;1;1;1})=4),--(A1:A8>B1:B8),--(C1:C8=0),--(D1:D8=0))
 
Eg Column A =IF(ISBLANK(Fix!A7),"",Fix!A7)
Is this a problem?
Click to expand...

Yes, it is. Suggest you change it all to return a zero instead of "" (which
is text), viz.:
=IF(ISBLANK(Fix!A7),0,Fix!A7)

Then you could use the simple:
=SUMPRODUCT((C1:C800=0)*(D1:D800=0)*(A1:A800-B1:B800>0))
to get the result
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
 
Ok, got it. Base on the same table, what if i want to check the following:

1) check if any row in column C has 0.5 while column D has 0. Table above
shows row 1,4 and 7.
2) if matches, then check if (cell A - cell B) +0.5 >0
3) if a positive value is returned, then count it. Above example will return
a value of 1 (row 1 returns positive while row 4 and 7 returns 0)
 
Thanks Biff. Looks good, but i'm having trouble modifying it suit the various
conditions i have in my table. (See earlier post to Max)

The pairings for column C and D include:
0.5 and 0
1 and 0
etc.

for each of the above that matches, the formula will then check if column
A-column B of the row is more than column C of the same row. E.g. using the
same table, row 1,4 and 7 has column C and D with 0.5 and 0 as the match. For
each match, the formula will then take column A-column B + 0.5 to return a
positive value. If positive value is return, it counts it, else 0.
 
Sorry, now I'm lost on what you're trying to do. This sounds like the same
thing you posted a few weeks ago. I was lost on that as well.
 
Ok, got it.

Good. Pl press the "Yes" button (like the one below) in that response, won't
you.

As for your new query, this should do it:
=SUMPRODUCT((C1:C8=0.5)*(D1:D8=0)*(A1:A8-B1:B8+0.5>0))

Above expression is simply strung up by following your logic lines
described. And if that does the job for your new query here, press the "Yes"
button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
 
No worries. Thanks to you guys, i managed to play around and found what i
wanted. I followed one of your advice to compile into 1 worksheet so that it
is able to sort and retrieve data accordingly - hence the reason i need to
modify the previous formula which was in 20 worksheets.
 
Back
Top