Problem using IF and AND functions correctly / effectively in a formula

[Bermudez]

Hello Again....

I am attempting to define class position as seen in Column P.

Where there is a "DNF" the competitor did not finish the race and consequently does not have a class position. However he does get one point for starting the race as stated in column Q.

By using the IF/AND functions in a formula in column P, the formula will ignore the cell above it if it contains DFL, and will go to the one above that in order to continue to count down the positions. Note that the countdown also resets itself as the class changes as defined in Column H.

The problem for me arises when there are 2 "DNF"s next to each other (P21 & P22), then the formula no longer works. see code below...

Please Login or Register  to view this content.

I then modified it to ignore the 2 cells above if they both contain "DNF"s however it is not working....

Please Login or Register  to view this content.

I have attached the Sheet for you review, please note that the first formula resides in column P with the exception of P23 that has the second formula. note that when the formula from P23 is added to cell P18 it returns the wrong number count...

The sheet as downloaded is showing the desired result but this I due to the 2 formulas being used I need to figure out one formula the gives the same result.


Any help with this would be greatly appreciated!!

Forum Cup Race sheet.xls

[AlphaFrog]

I think a COUNTIFS function could work.

In column P, this counts the the cells above if they are in the same class and not equal to DNF or 0. Then adds one to that count. The formula goes in P6. The references to $P$5:P5 is intentional.

Please Login or Register  to view this content.

[Bermudez]

Wow... AlphaFrog that is almost working.... See attached Sheet..... There is no first place in P6 or P26 although there seem to be first places in other places that are correct.

Forum 2.xls

[AlphaFrog]

Change this bit...

Please Login or Register  to view this content.

[Bermudez]

AlphaFrog - Thank you, that worked!!!! You are a godsend!!

[AlphaFrog]

You're welcome. Thanks for the feedback.