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
=IF(K24<3,0,M24/K24)
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
yes
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
ok
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
yes
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.
