Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.