• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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
differently in 2010 Thank you in advance for your assistance.
Mike
 

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
 

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
 

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))

02-03-2015 02-55-26.jpg

and everything looks fine..
 

guitarman

Member

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
 
Top