Conditional Formatting?

usc1000

New Member
Joined
Mar 23, 2009
Messages
42
scores
Player1
4
Player2
6
Player3
3
Player4
4
Number of player's score needed
2
lowest total score
7

<tbody>
</tbody>
Can any help me find the correct formula? The lowest score will depend on the number of players scores needed. In this case we needed two player's scores, so the lowest total score is 7, but if we needed 3 scores our total will be 11. Thanks, any help will be appreciate!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,213
Player1 4 3
Player2 6 4
Player3 3 4
Player4 4 6

Number of player's score needed 3
lowest total score 11

E1 = =SMALL($B$1:$B$4,ROW()) and drag down

=> this give the result 3, 4 , 4, 6 in E1 : E4

B6 = manualy added the number of players (in this case 3)

B7 = =SUM(INDIRECT("E1"&":"&"E"&1+$B$6-1))


Please reply
 

usc1000

New Member
Joined
Mar 23, 2009
Messages
42
Player1 4 3
Player2 6 4
Player3 3 4
Player4 4 6

Number of player's score needed 3
lowest total score 11

E1 = =SMALL($B$1:$B$4,ROW()) and drag down

=> this give the result 3, 4 , 4, 6 in E1 : E4

B6 = manualy added the number of players (in this case 3)

B7 = =SUM(INDIRECT("E1"&":"&"E"&1+$B$6-1))


Please reply

Thank very much! This work great. I will post another question later. :cool:
 

usc1000

New Member
Joined
Mar 23, 2009
Messages
42
Oeldere, thank you for helping me out, but every time I try to put the formula anywhere below the fourth row the formula does not work. Thus this means that every fomula must above fourth column? Thanks, usc1000
 

usc1000

New Member
Joined
Mar 23, 2009
Messages
42

ADVERTISEMENT

I meant row, not columns
 

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,213
e1=SMALL($B$1:$B$4,ROW())

the part row() in cel e1 give as result (row1) = 1.

e2 will give the result 2

if you want to put the formula in cel e10 and you want the result 1, then you have to amend the formula.

e10=> row()-9 =1
e11=> row()-9=2

Hope I explained well enough, why the formula is NOT working if you put it somewhere else in the file.
 

usc1000

New Member
Joined
Mar 23, 2009
Messages
42

ADVERTISEMENT

e1=SMALL($B$1:$B$4,ROW())

the part row() in cel e1 give as result (row1) = 1.

e2 will give the result 2

if you want to put the formula in cel e10 and you want the result 1, then you have to amend the formula.

e10=> row()-9 =1
e11=> row()-9=2

Hope I explained well enough, why the formula is NOT working if you put it somewhere else in the file.

I am currently at work, but I will give it a try once I get home or over the weekend. I am not very good at decipher formulas as you have explain it, but anytime I added the formula "small($B$1:$B$4,ROW()) and placed it on e1 and drag down it work well, even if I put on f1 and drag id down it would work okay, but as soon as I would drag the formula from the cell and place it anywhere from e5 or any other column " "5 and down it would not work. Hopefully this new process works. Thanks! P.S. I do appreciate all your help and specially the process of the explanation, this makes me understand how the formula works. Once again, thanks.
 

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,213
Code:
 place it anywhere from e5 or any other column " "5

Code:
B7 = =SUM(INDIRECT("[COLOR="#FF0000"]E1[/COLOR]"&":"&"[COLOR="#FF0000"]E[/COLOR]"&1+$B$6-1))

If you put your formula in another column you need to amend the column in the red text above.
 

usc1000

New Member
Joined
Mar 23, 2009
Messages
42
Code:
 place it anywhere from e5 or any other column " "5

Code:
B7 = =SUM(INDIRECT("[COLOR=#ff0000]E1[/COLOR]"&":"&"[COLOR=#ff0000]E[/COLOR]"&1+$B$6-1))

If you put your formula in another column you need to amend the column in the red text above.

This processe"e10=> row()-9 =1
e11=> row()-9=2
did the trick. Thank you very much. I do appreciate it. :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,875
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top