• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Corrections to my formulas in RED

Thank you for the compliments, you will find threads longer then this one on this forums. I tried to understand the problem to my level best but sometimes things are not that straight. :)

Ok now i have understood that there are 9 columns between Column C to Column K and these columns Correspond to the 9 columns of P to X? I hope this is correct. Further that you want to go column wise. i.e we need to ignore the no. of rounds here. just do every calculation column wise. since there are 9 columns between c to k, the produced result will be placed in the corresponding 9 columns between p to x? Hopefully this should also be correct. If yes then what you want is..

Check if C4=1, then C3*C4
Check if C9=1, then C9*C8
Check if C14=1, then C14*C13

...and so on till the end of column c.

if this description is correct then [considering only first two round, because it will let me examine lesser no. of rows while debugging formula] the formula that needed to be placed in cell P20 is:

=SUM(IF(IF($B$2:$B$10="GIR",C$2:C$10)=1,C$2:C$10)*($B$1:$B$9="PUTTS")*(C$1:C$9<>"")*1)

please see the attached file, and let me remind you again that you have explained things but not provided me with the manually calculated correct answer of the question!! :)
 

Attachments

  • 2015 Stats a(Mini) 30.01.2015 (1).xlsx
    113.5 KB · Views: 1
Hi Faseeh,

YES!! Everything up until the formula for P20 is correct. Before I look at your attachment I have a simple question. You have b2:b10 and c2:c10 etc., and b1:b9 and c1:c9. If I changed those to something like this...B:B and C:C and B:B and C:C, wouldn't that encompass ALL of Column C? That's what I need. The way you broke it down is kind of how it might look JUST for April. (If your #'s were more like...c1:c40 or so) I'm not sure why you chose the numbers you did. I hope it was just an example.

I thought I described how to verify in earlier posts, but if not here is an example.

Every time a 1 is the result in the GIR cell in Column C, multiply C4*C3. That result should be added in cell N20. C4=1 C3=2...The result added to N20 should be 2. Hope that makes sense.

Thanks

BL84
 
Hi BL84,

@Nayarn Sir is right, you can go through the thread and it is very detailed one. infect is is always advised to search for a similar thread before posting a problem .

Regarding Selection of column, if you not select B1:B9 it will eventually give you zero in the result due to multiplication of zero in one array with non zero in the later one. Try it you will learn something new.

Please see attached sheet if it is giving correct result, i have added entire columns form C [and when you drag formula to right, it will eventually select/consider column D,E to K], although i have only put data up til Round 10 in Column C. The result that shows in Cell P20 is 30. If you can check and correct me. Thanks.

Regarding
 

Attachments

  • 2015 Stats a(Mini) 31.01.2015.xlsx
    113.8 KB · Views: 3
Hi BL84,

@Nayarn Sir is right, you can go through the thread and it is very detailed one. infect is is always advised to search for a similar thread before posting a problem .

Regarding Selection of column, if you not select B1:B9 it will eventually give you zero in the result due to multiplication of zero in one array with non zero in the later one. Try it you will learn something new.

Please see attached sheet if it is giving correct result, i have added entire columns form C [and when you drag formula to right, it will eventually select/consider column D,E to K], although i have only put data up til Round 10 in Column C. The result that shows in Cell P20 is 30. If you can check and correct me. Thanks.

Regarding

Hello Faseeh,

All seems to be correct for GIR Putts in cells thru round 10. I will re-phrase my question from earlier. Where you have b2:b801, c2:c801, c2:c801, b1:b800, c1:c800. Those ranges encompass what would be my ENTIRE year of golf stats for Column C. If I played 30 rounds of golf in April am I correct in changing those to b2:b150, c2:c150, c2:c150, b1:b150, c1:c150? Just to clear things for me, why did you use b2:801 in one case and b1:800 in the other? If they all said B/C1:B/C800 would that change something in the range down to cell 800? I'm just wondering why one range is 1-800 and the other is 2-801. I THINK all is well with your forumula. (Depending on your answer to my above Q) If all is well, I just need you to "extend" my formula for U/D-M in the same way. I also changed the formula in C4 so the 1 would not appear if cells C2 & C3 were empty. That was irritating to me.

Thanks

BL84
 

Attachments

  • Faseeh (Stats).xlsx
    146.9 KB · Views: 0
Hi ,

Just out of interest , you can check the following thread :

http://chandoo.org/forum/threads/ho...e-custom-results-in-a-golf-match-format.8894/

The OP , has one or two other threads on similar topics.

Narayan

I will scan it. At first glance it is WAY to high a level for anything I'll ever try to do. My Q's aren't really golf Q's, they are excel Q's. I understand all things "golf", but not how to write formulas for those things. When I have time, I will look thru and see if there is anything simple enough for me to apply sometime in the future. All good advice.

Thanks

BL84
 
Hi BL84,

@Nayarn Sir is right, you can go through the thread and it is very detailed one. infect is is always advised to search for a similar thread before posting a problem .

Regarding Selection of column, if you not select B1:B9 it will eventually give you zero in the result due to multiplication of zero in one array with non zero in the later one. Try it you will learn something new.

Please see attached sheet if it is giving correct result, i have added entire columns form C [and when you drag formula to right, it will eventually select/consider column D,E to K], although i have only put data up til Round 10 in Column C. The result that shows in Cell P20 is 30. If you can check and correct me. Thanks.

Regarding


Just regarding the b1:b9 statement, you can see on my formula in N8 etc., there aren't any numbers just B:B and C:C and it works perfectly. It only looks for data where there is "Score". Does that formula work because it is different in some fundamental way from your formula? Interesting for me to know.
 
Hi BL84,

Nice to see you back, i think our day timings are at-least 12 hrs different bcz my clock is showing 1:00 am, so this might be the last post of the day. Taking your comments one by one...

All seems to be correct for GIR Putts in cells thru round 10.
Great!!

I will re-phrase my question from earlier. Where you have b2:b801, c2:c801, c2:c801, b1:b800, c1:c800. Those ranges encompass what would be my ENTIRE year of golf stats for Column C.

I told you that i used smaller range say one that included only two rounds because it was easier for me to debug! Off course you can use entire columns but to me, that will slow down the calculations and make your formulas lethargic. So i will not recommend you to use something like C:C and B:B instead examine your requirement and use as per it.

If I played 30 rounds of golf in April am I correct in changing those to b2:b150, c2:c150, c2:c150, b1:b150, c1:c150?

Yes you can do that!!
 
Hi BL84,

Nice to see you back, i think our day timings are at-least 12 hrs different bcz my clock is showing 1:00 am, so this might be the last post of the day. Taking your comments one by one...


Great!!



I told you that i used smaller range say one that included only two rounds because it was easier for me to debug! Off course you can use entire columns but to me, that will slow down the calculations and make your formulas lethargic. So i will not recommend you to use something like C:C and B:B instead examine your requirement and use as per it.



Yes you can do that!!

The only reason I asked about the B:B, C:C was so I could feel comfortable writing a formula and then filling in the exact ranges later. Sounds good! Hopefully, see you tomorrow sometime. It is 12:20 PM here.

Good night,

BL84
 
I'm just wondering why one range is 1-800 and the other is 2-801

You can't understand that unless you examine what would happen if i use 1-801 for both. Just for your understanding. Lets consider a smaller range and the formula that i give you in start.

SUM(IF(IF($B$2:$B$10="GIR",C$2:C$10)=1,C$2:C$10)*($B$1:$B$9="PUTTS")*(C$1:C$9))

Now change the setting as you asked every thing 2:10 it will be

SUM(IF(IF($B$2:$B$10="GIR",C$2:C$10)=1,C$2:C$10)*($B$2:$B$10="PUTTS")*(C$2:C$10))

Now executing it

Untitled.png

Now you can see F*F,F*T,1*F,F*F,F*F,F*F,F*F,1*F,F*F hence an array of F,F,F,F,F,F,F,F,F OR {0,0,0,0,0,0,0,0,0} so what ever you multiply with this array will be zero.

Now second case. if i offset the second array as 1:9 it would be like this:

F*F,1*T,F*F,F*F,F*F,F*F,1*T,F*F,F*F hence an array of F,T,F,F,F,F,T,F,F OR {0,1,0,0,0,0,0,1,0}*{4,3,1,0,0,4,2,1,0} OR {0,3,0,0,0,0,2,0,0} OR 5 will be answer.


Now extend this logic to 2-801 and 1-800.

Good Night
 
You can't understand that unless you examine what would happen if i use 1-801 for both. Just for your understanding. Lets consider a smaller range and the formula that i give you in start.

SUM(IF(IF($B$2:$B$10="GIR",C$2:C$10)=1,C$2:C$10)*($B$1:$B$9="PUTTS")*(C$1:C$9))

Now change the setting as you asked every thing 2:10 it will be

SUM(IF(IF($B$2:$B$10="GIR",C$2:C$10)=1,C$2:C$10)*($B$2:$B$10="PUTTS")*(C$2:C$10))

Now executing it

View attachment 15348

Now you can see F*F,F*T,1*F,F*F,F*F,F*F,F*F,1*F,F*F hence an array of F,F,F,F,F,F,F,F,F OR {0,0,0,0,0,0,0,0,0} so what ever you multiply with this array will be zero.

Now second case. if i offset the second array as 1:9 it would be like this:

F*F,1*T,F*F,F*F,F*F,F*F,1*T,F*F,F*F hence an array of F,T,F,F,F,F,T,F,F OR {0,1,0,0,0,0,0,1,0}*{4,3,1,0,0,4,2,1,0} OR {0,3,0,0,0,0,2,0,0} OR 5 will be answer.


Now extend this logic to 2-801 and 1-800.

Good Night


I'm hoping you just made a simple mistake. If not, I'm going to be really lost. On my spreadsheet, the last cells for data needed in the formulas we are talking about are the 799 group. I can see if you wrote the formula to end at cell 799, but you have it ending at cell 800 and cell 801. 801 is out of the data cells. PLEASE tell me the end of the range could be 800 or 799 and there would be NO problems. I'm not even going to ask again why you started with B1 & C1. I'm hoping your sheet just looks different than mine. Uh oh. I just looked at the sheet you sent me. It is aligned just like mine. I'm confused by the beginnings and endings of your formulas. B1 & C1 are not even in the data range cells for GIR Putts. Go ahead, tell me I'm nuts. I definitely see how B1 & C1 & D1 etc. will show in the formula for U/D-M, because "Par" is a basic part of that one. This formula works, but the listed numbers are still affecting me.

See ya when you are up and about, (hopefully)

BL84
 

Attachments

  • Faseeh (Stats).xlsx
    145.9 KB · Views: 3
Here is a formula I was given for U/D-M. It did not work. Anyone see the mistake(s)?

Thanks

BL84

=sumproduct(--MOD(ROW($c4:$c800)+1,5)=0),--($c$4:$c$800<1),--($c$2:$c$798<=$c$1)

The "excel formula guy" took out the , after =0) and after <1)

The result of this formula was 1. Everytime. Didn't matter what was in the data cells.
 
UP/Downs-Made

In case someone can ever use a formula like this that works, here it is.

=countifs($c$4:$c$799,"<1",$b$4:$b$799,"GIR",$c$2:$c$797,"<="&$c$1)

Using the order of my "Titles" and using your own number range etc.

Thanks All,

BL84
 
HI BL84,

Can you plz try this formula for UDM in cell P15 with CSE. If the result (i.e. 2 it is showing is correct) then replace range like i have done before. This is just for two rounds.

=SUM((($B2:$B10="GIR")*(C$2:C$10<=1))*(($B1:$B9="PUTTS")*(C$1:C$9<=C$1)))

Please consider my local timing when you posted the last post my clock 2 pm so cant do much about timing.

I'm hoping you just made a simple mistake. If not, I'm going to be really lost. On my spreadsheet, the last cells for data needed in the formulas we are talking about are the 799 group. I can see if you wrote the formula to end at cell 799, but you have it ending at cell 800 and cell 801. 801 is out of the data cells. PLEASE tell me the end of the range could be 800 or 799 and there would be NO problems. I'm not even going to ask again why you started with B1 & C1. I'm hoping your sheet just looks different than mine. Uh oh. I just looked at the sheet you sent me. It is aligned just like mine. I'm confused by the beginnings and endings of your formulas. B1 & C1 are not even in the data range cells for GIR Putts. Go ahead, tell me I'm nuts. I definitely see how B1 & C1 & D1 etc. will show in the formula for U/D-M, because "Par" is a basic part of that one. This formula works, but the listed numbers are still affecting me.

See ya when you are up and about, (hopefully)

Don't worry about that row offset stuff you will learn it by time. :)
 
Last edited:
Here is a formula I was given for U/D-M. It did not work. Anyone see the mistake(s)?
Thanks
BL84
=sumproduct(--MOD(ROW($c4:$c800)+1,5)=0),--($c$4:$c$800<1),--($c$2:$c$798<=$c$1)
The "excel formula guy" took out the , after =0) and after <1)
The result of this formula was 1. Everytime. Didn't matter what was in the data cells.

You are simply not Pressing "Ctrl + Shift + Enter" you need to understand what is an array formula and how does it works!!! (The same answer as my formula is showing i.e. 2)
 
Last edited:
You are simply not Pressing "Ctrl + Shift + Enter" you need to understand what is an array formula and how does it works!!! (The same answer as my formula is showing i.e. 2)

Actually, I left out a ( right before MOD. That made it work. My eyes were getting bleary as I copied the formula.
 
Faseeh,

Don't worry about getting back to me immediately. I know we are in different time zones. I keep checking back when I have time. I appreciate any help you give me. I'll input your formula in a moment and see if it works. I have another question about getting a "sum" cell to appear blank unless data is in the related cells. On my sheet, Column L is where the total of C:K is. I was writing a formula to show the average of all cells in Column L in the Score row. All the the rows that have zero data show a zero for a total. Dang if that doesn't affect my average. Is there a simple way to have the totals for those scoring rows to show nothing unless there is data in the related cells? I used an IF(and("" formula the other day for different cells, but I don't know what makes zeroes "not count". It's not a big deal, so if you don't want to waste time, don't worry about it. Okay, off to check your formula.

Thanks

BL84
 
As you can see, I input two rounds of scoring. Your formula is in purple. The answer should be 5. (F3,I3,D8,J8,K8) I noticed you moved the $ signs on the C$2:C$10 and the others C's as well. I suppose that was for some special reason. Hmmmmm. I'm now off to bed. It must be around 2:30 PM there right now. Enjoy your day.

Thanks

BL84
 

Attachments

  • 2015 Stats (Colors).xlsx
    363.2 KB · Views: 3
Can you explain why you have not included in the list [(F3,I3,D8,J8,K8)] these cells?

C8, GIR = 0 [1st Criteria of GIR<= 0 is ok) & P(1)<=Scoree(<5) 2nd Criteria Ok
D3, GIR = 0 [1st Criteria of GIR<= 0 is ok) & P(1)<=Scoree(<3) 2nd Criteria Ok
G8, GIR = 0 [1st Criteria of GIR<= 0 is ok) & P(1)<=Scoree(<4) 2nd Criteria Ok
I8, GIR = 0 [1st Criteria of GIR<= 0 is ok) & P(2)<=Scoree(<5) 2nd Criteria Ok
H3, GIR = 0 [1st Criteria of GIR<= 0 is ok) & P(2)<=Scoree(<5) 2nd Criteria Ok

??

Thanks,

PS First solve this udm and gir issue then will see remaining stuff.
 
Can you explain why you have not included in the list [(F3,I3,D8,J8,K8)] these cells?

C8, GIR = 0 [1st Criteria of GIR<= 0 is ok) & P(1)<=Scoree(<5) 2nd Criteria Ok
D3, GIR = 0 [1st Criteria of GIR<= 0 is ok) & P(1)<=Scoree(<3) 2nd Criteria Ok
G8, GIR = 0 [1st Criteria of GIR<= 0 is ok) & P(1)<=Scoree(<4) 2nd Criteria Ok
I8, GIR = 0 [1st Criteria of GIR<= 0 is ok) & P(2)<=Scoree(<5) 2nd Criteria Ok
H3, GIR = 0 [1st Criteria of GIR<= 0 is ok) & P(2)<=Scoree(<5) 2nd Criteria Ok

??

Thanks,

PS First solve this udm and gir issue then will see remaining stuff.


Hey Faseeh,

If you are asking why C8, D3 etc. were not categoriezed as U/D-M, here is my answer.

C8 = The "score" was NOT <= to C1. (Does not qualify for U/D-M)

D3 = The "score" was NOT <= to D1. (Does not qualify for U/D-M)

G8 = The "score" was NOT <= to G1. (Does not qualify for U/D-M)

I8 = The "score" was NOT <= to I1. (Does not qualify for U/D-M)

H3 = The "score" was NOT <= to H1. (Does not qualify for U/D-M)

Remember, there are 3 criteria that MUST be met if it is a U/D-M.
1) There CANNOT be a 1 in the GIR cell.
2) The Putts cell must have a 1 or 0.
3) The Score CANNOT be > than "C1". (Par)

Did you forget to expand the formula for the cells D:K? I don't think you would do that, but just asking. A couple of those would be U/D-M if you used C1 for ALL.

Hope this helps.

BL84
 
=averageifs($L2:$L10,"score",">=29",$B2:$B10) Don't do the formula for me, but give me some clues as to what is wrong. The "formula builder" says not enough info has been added. It marks the b2:b10 in blue. Am I close to the right solution? Averageif didn't seem to have enough slots to input criteria. Column B is the place where titles are. Column L is where the actual scores are sitting. I switched the B's and the L's and moved the >=29 in front of the B's thinking I was doing okay. In the builder, the average range is blue, criteria range1 & criteria are gray and criteria range2 is green. Do those colors mean anything? My goal here is to have an average of all "scores" in Column L that are 29 or higher. Small range of L2:L10 is for example.

Thanks

BL84
 
=averageifs($L2:$L10,"score",">=29",$B2:$B10) Don't do the formula for me, but give me some clues as to what is wrong. The "formula builder" says not enough info has been added. It marks the b2:b10 in blue. Am I close to the right solution? Averageif didn't seem to have enough slots to input criteria. Column B is the place where titles are. Column L is where the actual scores are sitting. I switched the B's and the L's and moved the >=29 in front of the B's thinking I was doing okay. In the builder, the average range is blue, criteria range1 & criteria are gray and criteria range2 is green. Do those colors mean anything? My goal here is to have an average of all "scores" in Column L that are 29 or higher. Small range of L2:L10 is for example.

Thanks

BL84


Excel example of student scores helped me for the first formula and received very nice help in "reorganizing" my other formula to proper form.

=averageifs(L2:L10,L2:L10,">29")

=averageifs(L2:L10,L2:L10,">=29",b2:b10,"score")

Getting them in the proper order seems to be the most difficult for me.

Gaining ground, though.
 
Why do you want to include D:K don't you want to do calculation column wise? Column C to K corresponds to column Col N to V is't that right? You are confusing issues why you moved to avg formula now?
 
Remember, there are 3 criteria that MUST be met if it is a U/D-M.
1) There CANNOT be a 1 in the GIR cell.
2) The Putts cell must have a 1 or 0.
3) The Score CANNOT be > than "C1". (Par)

Your last sheet countains this formula that you siad works for you, where is the criteria for putt<=1 in this formula??

=COUNTIFS($C$4:$C$799,"<1",$B$4:$B$799,"GIR",$C$2:$C$797,"<="&$C$1)

The section in red counts GIR with < 1 cell, Section in Blue counts for Score<Par where is that part that accounts for Putt<=1??
 
=countifs(C4,"<1",C2,"<="&C1)

Putts @ 1 or 0 was never part of the formula. Just one of the 3 ways to check and see if U/D-M was possible. All 3 must still be met. I never tried to put it in a formula because that would be one more thing I'd have trouble with.

Here is the other formula that was lent to me for U/D-M.

=sumproduct(--(MOD(ROW($C$4:$C$799)+1,5)=0),--($C$4:$C$799<1),--($C$2:$C$797<=$C$1))

That one had so much stuff in it I didn't understand. That would take me a year or so to figure out "how" all that worked together. Kind of looked like one of your Einstein formulas. Too intense for me to use. You've taught me quite a bit in this last week. I do certainly appreciate all you've done. Hope this clears issues with the putting being in the formula.

Thanks

BL84
 
Back
Top