Excel 2007 : Formula need few tweaks to handle blanks properly.

[spiros63]

Hi guys,

What we need is someone better than us in Excel to modify, adjust or do something else to a formula
that fails to handle blanks properly.

In the attached file you will find a small but clear example.

Any help would be greatly appreciated !

[arlu1201]

Try changing the formula in L4 to

Please Login or Register  to view this content.

and in M4 to

Please Login or Register  to view this content.

[spiros63]

Hmmm...

As you can see in the re-uploaded file (comparing to expected results) still something is missing

[Marcol]

Is the series in Column K (Tot) correct, or are "CORRECT EXPECTED RESULTS" indeed correct?

Seems to me, if you change the formula in L4 to

Please Login or Register  to view this content.

and in M4 to

Please Login or Register  to view this content.

Then make the series in K4 down 1,2,3,4, ..... You get the result you are expecting.

If you leave the series as 1,1,1,2,3,4,... you get a different, but logical result.

P.S.
Dont wrap single cells in SUM(), it makes your formulae hard to read.
e.g.
IF(SUM(M$3)=0,"",SUM(M$3)) is the same as IF(M$3=0,"",M$3), unless M3 is text in which case it will always return 0 (zero)

[martindwilson]

in l4 =COUNTIF($J$4:J4,">=0") and in m4 =COUNTIF($J$4:J4,"<0")

or not to fill in when blank
=IF(J4="","",COUNTIF($J$4:J4,">=0"))
=IF(J4="","",COUNTIF($J$4:J4,"<0"))

[spiros63]

Hey Marcol,

Data in columns K, N, and O are correct.
Basically they are an ascending numbering : K column of I column's (counts contracts), N column of J column's (counts positives) and O column of J column's (counts negatives).

As you you'll see in the re-uploded file we need something more

p.s. It was nice to see you again

[martindwilson]

you do not need the ifs use the countif as i showed you see what i posted its done
as for the other column k one ,in k4 put 1 ,in k5 put
=IF(COUNTIF($I$4:I5,I5)>1,K4,K4+1) drag down

[spiros63]

@Martin

Speechless !
So easy, so simple...
Looking for my nose I went 1000 miles away...

Thanks a lot Martin !!!

(for bringing me back to earth)

[Marcol]

Well spotted Martin!
I was letting the tail wag the dog assuming that Column "Tot" was a control column.

@ spiros, further to post #4

This is possibly an example of when you might use SUM() with a single cell.
Instead of putting 1 in K4 as Martin suggests put this

Please Login or Register  to view this content.

Drag/Fill Down
All is well

Now put some text in K3, say a header, the formula will fail

Change the formula to

Please Login or Register  to view this content.

Drag/Fill Down
All is well again!
You could also use

Please Login or Register  to view this content.

SUM() treats text as 0 (zero)

[spiros63]

Well spotted, by you this time Marcol !

Indeed the previous formula would have fail if in K3 I'd put a header.

There are no words guys to thank you enough.

1.jpg

Check the attachment !!