Question description:
This user has given permission to use the problem statement for this
blog.
im creating a fantasy hockey model and trying to figure out how to rank if someone has played more than a certain number of games, and if they're certain position how to rank them accordingly
Solved by X. D. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
14/07/2018 - 08:21
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern.
Excelchat Expert
14/07/2018 - 08:22
I understand you need help in RANK formula with certain conditions.
Excelchat Expert
14/07/2018 - 08:22
Do you have a sample data so I can help you create the formula?
User
14/07/2018 - 08:22
thats one issue yes
User
14/07/2018 - 08:22
i have a sheet
Excelchat Expert
14/07/2018 - 08:22
Got it. I can help you with that. Before we proceed, just a friendly reminder that our policy is 1 question per session so for this session we'll be resolving this particular question.
User
14/07/2018 - 08:22
okay cool thanks
Excelchat Expert
14/07/2018 - 08:22
Ok, kindly share the data you have so we can start building the formula.
User
14/07/2018 - 08:23
how do i do that?
Excelchat Expert
14/07/2018 - 08:23
Click on share icon on the bottom of this chart (paper clip icon)
Excelchat Expert
14/07/2018 - 08:23
you can also paste the data in sheet1 in document preview
Excelchat Expert
14/07/2018 - 08:27
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon.
User
14/07/2018 - 08:27
i pout the data in
User
14/07/2018 - 08:27
sorry was slow
Excelchat Expert
14/07/2018 - 08:27
I see, no worries
User
14/07/2018 - 08:28
so for the players with pos G they don't play 82 games but the players who are other might
User
14/07/2018 - 08:28
so how i can make the expected column multiple by 82 but only for players not position g who other should be multiplied by games played
User
14/07/2018 - 08:28
fan its average by 82
User
14/07/2018 - 08:29
fantasy points average by 82 but only for people who don't play g or is it possible?
Excelchat Expert
14/07/2018 - 08:30
Ok can you specify the columns you were referring to? It it column E (GP)?
User
14/07/2018 - 08:31
column E is GP, column C is position, column r is fantasy points average
Excelchat Expert
14/07/2018 - 08:32
Can you clarify what output you want to get in column U expected? what criteria you want to consider, please specify the column name or exact header name
User
14/07/2018 - 08:34
i want column R* 82 but if column C has position g, i want to multiply by column E instead
Excelchat Expert
14/07/2018 - 08:34
Alright, please give a moment to solve your problem. Thank you!
User
14/07/2018 - 08:34
thanks
User
14/07/2018 - 08:35
also sorry if column e has less than 20 gp in column e to exclude if possible but i can figure that out later i guess
Excelchat Expert
14/07/2018 - 08:37
Ok so if column E (GP) has less than 20 gp, and column C (position) is G, then leave it as blank?
User
14/07/2018 - 08:37
sure
Excelchat Expert
14/07/2018 - 08:37
Ok got it
User
14/07/2018 - 08:37
thanks
User
14/07/2018 - 08:40
is that the formula?
Excelchat Expert
14/07/2018 - 08:40
Still fixing it
Excelchat Expert
14/07/2018 - 08:42
Here's the formula you need:
Excelchat Expert
14/07/2018 - 08:42
=if(C2<>"G",R2*82,if(and(C2="G",E2>20),E2*82,""))
Excelchat Expert
14/07/2018 - 08:44
Sorry this should be the updated formula
Excelchat Expert
14/07/2018 - 08:45
=if(C986<>"G",R986*82,if(and(C986="G",E986>=20),E986*82,""))
User
14/07/2018 - 08:46
the goalie total are to high? are they being multiplied by 82?
Excelchat Expert
14/07/2018 - 08:46
It reads: If Position is not equal to G, then FANPTS/GM x82, if Position is G and greater than or equal to 20, then GP (Column E) X82, else leave blank
Excelchat Expert
14/07/2018 - 08:46
Do you have any questions/clarifications regarding the solution provided?
User
14/07/2018 - 08:47
oh okay can i not multiple by e by 82 but R by column E?
User
14/07/2018 - 08:47
not by 82
User
14/07/2018 - 08:47
for if g in column c
Excelchat Expert
14/07/2018 - 08:47
ok
Excelchat Expert
14/07/2018 - 08:48
That would be R*E correct?
User
14/07/2018 - 08:48
ya instead of e by 82 do r by E
User
14/07/2018 - 08:49
for g in column c
Excelchat Expert
14/07/2018 - 08:49
=if(C2<>"G",R2*82,if(and(C2="G",E2>=20),R2*E2,""))
User
14/07/2018 - 08:49
yeah that looks better
Excelchat Expert
14/07/2018 - 08:49
Is there anything else that I can assist you with regarding this issue?
User
14/07/2018 - 08:51
is there anyway to column u and rank it based on column C?
User
14/07/2018 - 08:51
so like by position ?
User
14/07/2018 - 08:52
we could do that in separate colum
Excelchat Expert
14/07/2018 - 08:53
You mean rank column U based on position?
Excelchat Expert
14/07/2018 - 08:53
We already incorporated column U on the formula, so I think we can rank just column U
User
14/07/2018 - 08:55
what if you take column u and rank based on everyone who plays same position in column c
Excelchat Expert
14/07/2018 - 08:56
Yes we can do that, just rank everyone who plays C?
Excelchat Expert
14/07/2018 - 08:56
Hmm, wait
User
14/07/2018 - 08:56
rank by everyone who has same position in column c
User
14/07/2018 - 08:57
so like if the best g among g and the best RW of RW's
User
14/07/2018 - 08:57
or would that take operate columns
User
14/07/2018 - 08:57
separate*
Excelchat Expert
14/07/2018 - 08:58
I'll check if we can do that since RANK is an array formula
User
14/07/2018 - 08:59
maybe like rank for out of G in one column and if do not rank and same for other postions
User
14/07/2018 - 08:59
and then just readjust for other columns,
User
14/07/2018 - 08:59
and other positions
Excelchat Expert
14/07/2018 - 09:00
Yes we can do that but let's try if excel would allow it, since RANK has to be in a continuous array
User
14/07/2018 - 09:01
okay
User
14/07/2018 - 09:05
uhm is there way so they don't need to be specifically c/rw but if they qualify for C or RW for example they just need to go into those columns
Excelchat Expert
14/07/2018 - 09:06
but you have data with combined like LW/C?
User
14/07/2018 - 09:06
so that won't work because its already combined
User
14/07/2018 - 09:06
makes sense
Excelchat Expert
14/07/2018 - 09:07
Yeah coz we are ranking based on values in column C
Excelchat Expert
14/07/2018 - 09:10
Here's the formula: =--if($C3=V$2,rank($U3,$U:$U))
Excelchat Expert
14/07/2018 - 09:10
You can download the sheet by going to the link above, clicking FILE > DOWNLOAD AS > Microsoft Excel (.xlsx)
Excelchat Expert
14/07/2018 - 09:10
Are you satisfied with the solution provided?
User
14/07/2018 - 09:13
yes
Excelchat Expert
14/07/2018 - 09:13
Thanks for using Got It Pro-Excel. Please give your kind feedback for our service. Have a good day! :)
Excelchat Expert
14/07/2018 - 09:13
If you need additional help, please don't hesitate to contact us again.
Excelchat Expert
14/07/2018 - 09:13
Just a reminder, we are unable to end this session on our end until you do so please do not close this window without ending the session properly (Red X or End Session). Feel free to end this session as soon as you are ready for us to continue helping other customers. Thank you.
User
14/07/2018 - 09:15
its not downloading
Excelchat Expert
14/07/2018 - 09:15
Wait i'll send you the file instead
Excelchat Expert
14/07/2018 - 09:20
[Uploaded an Excel file]
Excelchat Expert
14/07/2018 - 09:20
Here's the file you can download
User
14/07/2018 - 09:20
thanks
Excelchat Expert
14/07/2018 - 09:20
You're welcome
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
