# Excel 2003-2010

#### guitarman

##### Member
Hi Everyone

Just a question really I have a formula created in Excel 2003. Why does it not work in Excel 2010 it is a Ranking formula
for shots taken and points gained =SUMPRODUCT((M\$4:M\$23=M4)*(L\$4:L\$23>L4)) I was wandering wether it is written
Mike

#### Somendra Misra

##### Excel Ninja
Hi Mike,

Can you explain what do you mean by "not working"? As there is no problem in the formula.

Regards,

#### guitarman

##### Member
Hi Somendra

The reason I say it is not working because nothing happens when I put results in on the other sheet it should register in the column
but I am afraid it does not it just leaves everything blank
SP Rank
0
0
0
0
0
0
So as you can see nothing registers so something is wrong somewhere
Kind Regards
Mike

#### Somendra Misra

##### Excel Ninja
Can you evaluate formula in a cell step by step, to see what is going on. Or put a sample file.

Regards,

#### guitarman

##### Member
Hi Somendra

Sorry I am late in replying but got held up in one of those boring meetings. However I have looked at the
formulas in the other columns and column Shot difference has this formula in it (=IF(D4<1,-100,T4+AB4)
and the column with the Points Has this in it (=U4+AC4-ABS(Deduction!D3). I do not understand the (ABS) if I alter it to
(AB4) the whole league table reverts to N/A ( A value is not available to the formula or function). I am really stumped
here I have not got a clue.
Regards Mike

#### Somendra Misra

##### Excel Ninja
Mike, I can understand the trauma one goes through in a meeting what I don't understand is your problem without a sample file. So kindly upload one.

Regards,

#### guitarman

##### Member
Hi Somendra
I have looked at this formula and believe it is massive. It incorporates a league table like a football one and also a form table for all the players so it would be to big to upload so I think I will forget about the form part of it and leave it at that
Kind Regards
Mike

#### Somendra Misra

##### Excel Ninja
Mike, you can create a small sample file with some data and try to explain what formula was doing in 2003 which it is not doing in 2010. May be a 50 - 100 line of data.

Regards,

#### guitarman

##### Member
Somendra
OK will do that but it will take me a bit of time I am nowhere near as good as you with Excel but i will give it a go
Many Thanks
Mike

#### guitarman

##### Member
Hi Somendra
Well I am back and I have done as you said and made a mini sort of file to give you an idea of how it works I just hope that it is satisfactory and you can make head of tail of it.
Regards Mike

#### guitarman

##### Member
Hi Somendra
Well I am back and I have done as you said and made a mini sort of file to give you an idea of how it works I just hope that it is satisfactory and you can make head or tail of it. Well it seems it will not open I don't know why it just keeps coming up Unspecified Error so i have not got a clue what to do
Regards Mike

#### guitarman

##### Member
Hi Somendra
Well I am back and I have done as you said and made a mini sort of file to give you an idea of how it works I just hope that it is satisfactory and you can make head or tail of it. Well it seems it will not open I don't know why it just keeps coming up Unspecified Error so i have not got a clue what to do
Regards Mike

#### Somendra Misra

##### Excel Ninja
Sorry, but there is no file attached.

Regards,

#### guitarman

##### Member
Somendra
Yes I know as i said in previous post it keeps coming back Unspecified error but i have tried again
Mike

#### Attachments

• 89.5 KB Views: 7

#### Deepak

##### Excel Ninja
Somendra
Yes I know as i said in previous post it keeps coming back Unspecified error but i have tried again
Mike
Doesn't seems any issue in your uploaded file. Pls take pain to explain bit more.

#### Debraj

##### Excel Ninja
Are you sure.. you have uploaded the correct file, As i am unable to find mentioned formula anywhere in the file.. =SUMPRODUCT((M\$4:M\$23=M4)*(L\$4:L\$23>L4))

and everything looks fine..

#### guitarman

##### Member
Are you sure.. you have uploaded the correct file, As i am unable to find mentioned formula anywhere in the file.. =SUMPRODUCT((M\$4:M\$23=M4)*(L\$4:L\$23>L4)) View attachment 16384 and everything looks fine..
Are you sure.. you have uploaded the correct file, As i am unable to find mentioned formula anywhere in the file.. =SUMPRODUCT((M\$4:M\$23=M4)*(L\$4:L\$23>L4)) View attachment 16384 and everything looks fine..

#### guitarman

##### Member
Hi Deepak &Debraj
Thanks for your reply the problem I am having is that in the setting file the areas for information about players form i.e Pts Rank which is working correctly but the SD Rank(shot difference) F Rank (form rank) and Year Position nothing is happening in those cells and I do not know why?
Regards
Mike