 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2007Simple formula help please...
This should be simple...but it's beyond my knowledge. I have column A (20 Drivers) Column B (position in race 1-20) I want column C to show points won based on the cell contents in adjacent cells in column B. Where first place (cell entry=1) gives 10 points, second (2) gives 8, third gives 6 down to 1pt for eigth place. 9th and below should give zero points. What formula should I be using in column C? Thanks for any help. Try: =MAX((B1<4)*(12-B1*2)+(B1>4)*(10-B1),0)  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > This should be simple...but it's beyond my knowledge. > [quoted text clipped - 9 lines] > > Thanks for any help. Sandy, your formula was not return correct result for 4th place to me. The folowing one gives right points: =IF(RANK(B1;$B$1:$B$20;1)>=9;0;CHOOSE(RANK(B1;$B$1:$B$20;1); 10;8;6;5;4;3;2;1)) Is this for F1 standings Sysyphus? ;-) Enter it in C1 and copy to C20 cell. Depending on your local settings maybe you will need to change all coma-points to comas. -- Regards, Mladen http://excelancije.bloger.hr > Try: > =MAX((B1<4)*(12-B1*2)+(B1>4)*(10-B1),0) [quoted text clipped - 24 lines] > > - Show quoted text - Yes, that's because I tested one formula and posted another, (don't ask me how I did it!) What I should have posted was: =MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0) Which returns the same results as your when I substitute , for ; SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > Sandy, your formula was not return correct result for 4th place to me. > The folowing one gives right points: [quoted text clipped - 40 lines] >> >> - Show quoted text - > Yes, that's because I tested one formula and posted another, (don't ask me > how I did it!) [quoted text clipped - 4 lines] > > Which returns the same results as your when I substitute , for ; <OP> Thanks all... Sandy, this works beautifully. Thank you. Unfortunately, I forgot to mention(!) I also a few drivers with 'position 0' (user entry) for DNF's (Did not finish race) in column B. This formula gives all zero entries 12 points - Can I append/insert an =IF function into your equation? ie. IF(B1=0, 0) or something? Yes certainly, Your solution will work very well: =IF(B1=0,0,MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0)) Or if you want to use DNF in place to the zero then: =IF(B1="DNF",0,MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0)) Or if you want to use either: =IF(OR(B1="DNF",B1=0),0,MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0)) Or even if you want to enter various different text messages: =IF(ISTEXT(B1),0,MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0)) SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk >> Yes, that's because I tested one formula and posted another, (don't ask >> me how I did it!) [quoted text clipped - 15 lines] > =IF function into your equation? > ie. IF(B1=0, 0) or something? > Sandy, your formula was not return correct result for 4th place to me. > The folowing one gives right points: [quoted text clipped - 3 lines] > > Is this for F1 standings Sysyphus? ;-) Is it *that* transparent? I suppose I should have just said so.... :+) I think Sandy meant something like this =MAX((B1>0)*(B1<3)*(12-B1*2)+(B1>=3)*(9-B1),0) and copy it down to C20. keizi > This should be simple...but it's beyond my knowledge. > [quoted text clipped - 9 lines] > > Thanks for any help. Not quite but your does work. What I meant was: =MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0) SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk >I think Sandy meant something like this > [quoted text clipped - 17 lines] >> >> Thanks for any help. > Not quite but your does work. What I meant was: > > =MAX((B1<4)*(12-B1*2)+(B1>=4)*(9-B1),0) Many thanks to all for the help. I'd never have figured this complex (to me) formuala. Cheers. You're welcome, sorry I messit it up to start with. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk >> Not quite but your does work. What I meant was: >> [quoted text clipped - 4 lines] > > Cheers. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.