Comparing two cells to provide lowest number.... but....

[BMAC86]

Hi,

I'm new to using Excel (2010) and I'm in the process of summarising a fun little spreadsheet to track Golf stats between my friends and I that I made. I've learnt many things so far to get me what I want but I'm stumped on this problem.

I have a 'Data' tab to hide everything I want to use, but not display. On that tab I have a table where I keep a numerical value of each of our scores over par (per date) as we play, so say 15, or 20, per player. Column = unique date, row = numerical value. I want to use that table to give me the percentage of beating another player, based on comparing the two scores if we played.

So far I have figured out how many times each of us has played the other by using:

Please Login or Register  to view this content.

35 being my row, and 36 being another player. I counted if my cell was >0 at the same time theirs was >0, on the same day (column).

What I need to do now is similar, but with another layer. So logically this is what I want to achieve:

If my row is >0 and their row is >0, count if my score is less than their score on the same day (column).

If I can find that then I'll have no worries getting a percentage value.

Thanks for your help!

[BadlySpelledBuoy]

Any chance you could post the workbook and show us your expected outcome?
Far easier to provide a solution when we can see the file.

BSB

[BMAC86]

Also I should add - I have no problem making a new table for these comparisons on the data sheet if I need to compare column by column and summing those values in another tab if that is easier.

[BMAC86]

Any chance you could post the workbook and show us your expected outcome?
Far easier to provide a solution when we can see the file.

BSB

Here is a sample - the values are in the 'Data' tab, the value I want should be in the "Times Beaten" cell of the 'Summary' Tab

[Refer to amended attached sample further down this thread]

[BadlySpelledBuoy]

Without you providing expected results it's difficult to provide a solution, but perhaps the below formula in H3 and copied down will help:

Please Login or Register  to view this content.

BSB

[BMAC86]

Unfortunately that doesn't seem to work, however I think you're on the right path. It seemed to work for the top two then reversed the result for the bottom two for some reason.

EDIT: Having a further play around with =COUNTIFS(3:3, "<" &4:4) and similar variants is providing me with some very weird results, and also the exact same formula seems to give different results depending on what column it's pasted into.

Anyway, I have updated and attached the sample spreadsheet with the expected results, as requested (sorry I was tired when I uploaded the first spreadsheet and forgot to include these).

[Phuocam]

Try this ...

=SUMPRODUCT(--(Data!$B$3:$AFD$3>0),--(Data!$B4:$AFD4>Data!$B$3:$AFD$3))

[BMAC86]

Try this ...

=SUMPRODUCT(--(Data!$B$3:$AFD$3>0),--(Data!$B4:$AFD4>Data!$B$3:$AFD$3))

That works perfectly thank you.

I'll be interested to learn what you have done there, it makes no sense to me now as I've never used that function before.

Thanks a lot!

[Phuocam]

You're welcome!