Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

use this formula if this box is equal or less than zero if not use this formula
Solved by G. Q. in 49 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 08/08/2018 - 04:36
Hi…Welcome to Got It Pro
Excelchat Expert 08/08/2018 - 04:36
Could you please share the formula to be used?
User 08/08/2018 - 04:37
so long story short I am trying to figure out the last bit of a forumla so I dont have to copy and paste formulas.
User 08/08/2018 - 04:38
I am trying to basically have a scenario where if a box is at zero or blank it calls up one formula but if it has a number it uses a different formula
User 08/08/2018 - 04:38
these are the two formulas I am using
User 08/08/2018 - 04:38
=IF(K11<9,'Weekly points'!BL29,M11/K11)
User 08/08/2018 - 04:39
User 08/08/2018 - 04:39
this is for calculating an average
User 08/08/2018 - 04:39
if the person had a predetermined average from last year, that person must shoot at least 9 games for the new average to take place.
Excelchat Expert 08/08/2018 - 04:40
Both formulas are either checking K11 or K24
User 08/08/2018 - 04:40
if they dont have an average then they would establish the day of
Excelchat Expert 08/08/2018 - 04:41
Is it possible to share the file?
User 08/08/2018 - 04:41
these were copied from two seperate people's averages. this is why the K value is different. One person had an average from last year the other did not
User 08/08/2018 - 04:41
yes, I was not sure if you would be confused by me sharing all the mess
Excelchat Expert 08/08/2018 - 04:41
:) Let's try
User 08/08/2018 - 04:41
[Uploaded an Excel file]
Excelchat Expert 08/08/2018 - 04:42
Which sheet should I be looking at?
User 08/08/2018 - 04:42
weekly points
User 08/08/2018 - 04:43
determine the average on the far right...
Excelchat Expert 08/08/2018 - 04:43
Column BL?
User 08/08/2018 - 04:43
on the averages page I have all the formulas built in to add and divde out the averages
User 08/08/2018 - 04:43
Excelchat Expert 08/08/2018 - 04:44
So what should ideally happen in BL? I mean the logic
User 08/08/2018 - 04:44
sorry that was a mixed communication... On the average page is all formulas I have built in to read numbers I put in on the weekly points page
User 08/08/2018 - 04:45
BL is the last years average the person shot
User 08/08/2018 - 04:45
This data is manually input from last years final numbers
Excelchat Expert 08/08/2018 - 04:46
Yes, I see the formulas in Average tab.
Excelchat Expert 08/08/2018 - 04:47
So what exactly is your requirement?
Excelchat Expert 08/08/2018 - 04:47
As per you problem statement, you will like to alter formula based on a cell value.
User 08/08/2018 - 04:48
sorry for mix communication. its alot going on... I was basically trying to see if there was a way to combine both formulas as i mentioned on before to be used at the same time so if the person has no average form last year or is set to zero then it reads the one formula. if they do have a value then it uses the other formula.
Excelchat Expert 08/08/2018 - 04:50
ok...let me build something around it and may be we can work from there
User 08/08/2018 - 04:51
Not sure if something like IF= BL163<1 uses formula =IF(K58<9,'Weekly points'!BL163,M58/K58) IF=BL163>1 Use formula =IF(K58<3,0,M58/K58)
Excelchat Expert 08/08/2018 - 04:52
the problem is your K58 is referencing Average sheet and not Weekly points.
User 08/08/2018 - 04:53
Do you understand what I am trying to accomplish?
Excelchat Expert 08/08/2018 - 04:53
Yes, kind of
User 08/08/2018 - 04:54
this is all i could come up with and basically if the person did not shoot last year I used the one formula and if they did I used the other and copied and pasted them to every persons average.
User 08/08/2018 - 04:54
then I adjusted numbers if they got messed up while coping and pasting
Excelchat Expert 08/08/2018 - 04:55
You are checking the Total games for the corresponding player and then depending on whether its less than 9 or 3 you would like to use a different formula.
User 08/08/2018 - 04:55
if that works that is fine, this was just to make anyone who shot last year use the last years average for the first 9 games. after this it would recalculate their average
Excelchat Expert 08/08/2018 - 04:56
Now the question is where do you need the formula. Is it Weekly points sheet or Average sheet?
User 08/08/2018 - 04:56
does not matter to me, I just did whatever I got to work out in the end
Excelchat Expert 08/08/2018 - 04:58
Is it possible to arrange all the players in one column in Average tab?
User 08/08/2018 - 04:59
Preferably not just to make everything easier to print off.
Excelchat Expert 08/08/2018 - 04:59
Excelchat Expert 08/08/2018 - 05:03
Please be with me, I am working on the formula.
User 08/08/2018 - 05:03
No worries, I have spent hours getting as far as I am now. If you figure this out in a few minutes your awesome
Excelchat Expert 08/08/2018 - 05:11
Could you please place this formula in cell BM2 in Weekly points sheet?
Excelchat Expert 08/08/2018 - 05:11
=IF(BL2>0,IF(IFERROR(INDEX(Average!$A:$F,MATCH('Weekly points'!A2,Average!$A:$A,0),4),IFERROR(INDEX(Average!$H:$M,MATCH('Weekly points'!A2,Average!$H:$H,0),4),""))<3,0,IFERROR(INDEX(Average!$A:$F,MATCH('Weekly points'!A2,Average!$A:$A,0),6),IFERROR(INDEX(Average!$H:$M,MATCH('Weekly points'!A2,Average!$H:$H,0),6),""))/IFERROR(INDEX(Average!$A:$F,MATCH('Weekly points'!A2,Average!$A:$A,0),4),IFERROR(INDEX(Average!$H:$M,MATCH('Weekly points'!A2,Average!$H:$H,0),4),""))),IF(IFERROR(INDEX(Average!$A:$F,MATCH('Weekly points'!A2,Average!$A:$A,0),4),IFERROR(INDEX(Average!$H:$M,MATCH('Weekly points'!A2,Average!$H:$H,0),4),""))<9,BL2,IFERROR(INDEX(Average!$A:$F,MATCH('Weekly points'!A2,Average!$A:$A,0),6),IFERROR(INDEX(Average!$H:$M,MATCH('Weekly points'!A2,Average!$H:$H,0),6),""))/IFERROR(INDEX(Average!$A:$F,MATCH('Weekly points'!A2,Average!$A:$A,0),4),IFERROR(INDEX(Average!$H:$M,MATCH('Weekly points'!A2,Average!$H:$H,0),4),""))))
User 08/08/2018 - 05:13
I got an error saying one or more circular references
Excelchat Expert 08/08/2018 - 05:13
Let me send you the file.
User 08/08/2018 - 05:14
In my thoughts this should be the last place this formula should get placed cause this is where I put in the shooters last years average.
Excelchat Expert 08/08/2018 - 05:14
[Uploaded an Excel file]
Excelchat Expert 08/08/2018 - 05:14
Column BM is where you have the formula.
Excelchat Expert 08/08/2018 - 05:15
Formula is actually very long due to the fact that we have the Average listed in 2 tables.
User 08/08/2018 - 05:15
I see it sorry. My screen was acting weird.... I think you did it
User 08/08/2018 - 05:16
I will have to run a few tests but it looks great. You are AWESOME
Excelchat Expert 08/08/2018 - 05:16
Rest assured, its fetching the correct calculation.
Excelchat Expert 08/08/2018 - 05:16
Do you want me explain a little on the logic I used?
User 08/08/2018 - 05:16
yes please
Excelchat Expert 08/08/2018 - 05:17
Its first checking if BL is zero or blank, if so if looks for Total games for that player and if that value is less than 3, results in 0, if more then it does Total points/Total Games for that player.
Excelchat Expert 08/08/2018 - 05:18
In case BL value is >0, then it checks if Total games is <9, if so it puts the BL value, else does Total points/Total games.
Excelchat Expert 08/08/2018 - 05:19
Just adapting both your formulas.
Excelchat Expert 08/08/2018 - 05:19
Does that sound right?
User 08/08/2018 - 05:19
Excelchat Expert 08/08/2018 - 05:19
I have used Index Match lookup function to match up the Total games and Total points for the players.
Excelchat Expert 08/08/2018 - 05:20
Is there anything else I can help you with this query?
User 08/08/2018 - 05:20
hmmm. thats great.
Excelchat Expert 08/08/2018 - 05:20
Please do give your valuable feedback of our service at the exit of this session.
User 08/08/2018 - 05:21
the only other item I could ask for is that on the standings page. I do a manual custom sort after I put in items, is there a way to have excel automatically sort
User 08/08/2018 - 05:21
I sort B3 to AN16 and do a custom sort
Excelchat Expert 08/08/2018 - 05:22
If you set up custom sort, next time, you just have to go to Custom and hit OK.
User 08/08/2018 - 05:23
Sounds good
Excelchat Expert 08/08/2018 - 05:24
It can't be more automatic than this unless you get vba into it and just does this work by click of a button
Excelchat Expert 08/08/2018 - 05:24
But VBA is currently out of scope from our services.
User 08/08/2018 - 05:24
sounds good. Thanks again for all your help
Excelchat Expert 08/08/2018 - 05:24
Thanks for your time. Please do come back for any new question. You can now end this session. Have a great day ahead!

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc