EXCEL gurus??

[golfinnut]

Ok THP ... where are my EXCEL gurus?

I have a question about a few formulas

 

[outlawx]

What are you trying to do?

 

[golfinnut]

What if I wanted to sum up the even cell numbers in column C? For instance, I want to add C4 + C6 etc = with the result in C20 .... but I want it to be automatic when I put in a cell value in all of the even numbered cells? Make sense? Once I enter another value into cell C20 & C21 it adds all of them up automatically?

 

[wubears71]

put this formula in c20. SUMPRODUCT(c3:c19,MOD(ROW(c3:c19)+1,2))

 

[xThor]

@RealPretendPsychic

 

[outlawx]

put this formula in c20. SUMPRODUCT(c3:c19,MOD(ROW(c3:c19)+1,2))

That should work. If not try this:
For odds
=sum(c3:c19*(MOD(ROW(c3:c19),2)=1))

for even
=sum(c3:c19*(MOD(ROW(c3:c19),2)=2))

 

[outlawx]

Also if you want you can put that formula at the top of the row or where ever and expand the range to c3:c500 and it will auto sum as you add more data.

 

[Iceman!]

Ok we have an Excel thread - Yippee.
I actually started using Standard Deviation for the first time as I've gotten Foresight Reports sent to me from PGATSS.
I take out the bad shots are rework the Average and Std Dev.
I was so excited to make that work.

 

[outlawx]

Ok we have an Excel thread - Yippee.
I actually started using Standard Deviation for the first time as I've gotten Foresight Reports sent to me from PGATSS.
I take out the bad shots are rework the Average and Std Dev.
I was so excited to make that work.

Want to make it next level? We could treat the data as if it was coming from a process. Once you have 30 data points we can setup a control chart to mathematically point out the outliers. We can also use this to see if you actually fixed something in your swing, ie shifted the process mean.

 

[golfinnut]

Thanks for the replies guys! I'll try them

 

[golfinnut]

Now what if I wanted to keep a running average of the even/odd cells as I enter numbers down the column? Sum it as I enter numbers then take the average of all of those?

 

[outlawx]

Edit to be clear replace the sum with average

=avgerage(what ever formula worked)

 

[Iceman!]

Want to make it next level? We could treat the data as if it was coming from a process. Once you have 30 data points we can setup a control chart to mathematically point out the outliers. We can also use this to see if you actually fixed something in your swing, ie shifted the process mean.

So is a screen shot of what I get from PGATSS on my swings of off their practice bays.
Here I'm testing two shafts.
I took out the "Flyers" to get it down to 5 shots and reconfigured the Averages and Std. Dev.



And I get this:




Now if you show me how to get AVG and Std. Dev. on columns "H and I" with the "L" in there, that would be great.

 

[outlawx]

=average(h48:h52)
=stdev.s(h48:h52)

then replace h with I and L

for your second shaft it’s h56:h60

 

[outlawx]

=average(h48:h52)
=stdev.s(h48:h52)

then replace h with I and L

for your second shaft it’s h56:h60

You can use other stdev functions but your data set is small and this will be the best approximation.

 

[golfinnut]

Edit to be clear replace the sum with average

=avgerage(what ever formula worked)

But how do I get it to keep adding the columns as I add more data??

=AVERAGE(C3:C23,MOD(ROW(C3:C23)+1,2))

For instance, I add C20 to C24 ... I want it to continuously add as I type new data, so I don't have to manually go back & change the range

 

[outlawx]

Also if you want you can put that formula at the top of the row or where ever and expand the range to c3:c500 and it will auto sum as you add more data.

But how do I get it to keep adding the columns as I add more data??

=AVERAGE(C3:C23,MOD(ROW(C3:C23)+1,2))

For instance, I add C20 to C24 ... I want it to continuously add as I type new data, so I don't have to manually go back & change the range

View attachment 9167776

=AVERAGE(C3:C500,MOD(ROW(C3:C500)+1,2))

bolded so you can see the changes. Basically just pick and overtly large range and as you input data it will add. If you exceed the 500 push it to 1000 or however much you eventually add.

 

[golfinnut]

=AVERAGE(C3:C500,MOD(ROW(C3:C500)+1,2))

bolded so you can see the changes. Basically just pick and overtly large range and as you input data it will add. If you exceed the 500 push it to 1000 or however much you eventually add.

Ok I did that ... what did I do wrong? This is what the sum & avg should be

 

[outlawx]

Can you show the function you inserted?

 

[outlawx]

Oppsies forgot that mod would mess with the average you need to modify the formula.

=average(IF(MOD(ROW(c3:c500),2)=0,c3:c500,””))

 

[golfinnut]

Oppsies forgot that mod would mess with the average you need to modify the formula.

=average(IF(MOD(ROW(c3:c500),2)=0,c3:c500,””))

Now I get "Invalid Name Error"

 

[golfinnut]

I entered this:

=AVERAGE(IF(MOD(ROW(C3:C500),2)=0,C3:C500,""))

& got 4.13655

 

[outlawx]

Note these formulas should only work with the even cells for the odd cells you'll need this:

=SUM(IF(MOD(ROW(C3:C500),2)=1,IF(MOD(C3:C500,2)=1,C3:C500,""),""))

=AVERAGE(IF(MOD(ROW(C3:C500),2)=1,IF(MOD(C3:C500,2)=1,C3:C500,""),""))


Also once you have pasted those in the cells you'll need to hit Ctrl + Shift + Enter at the same time instead of a normal enter

 

[outlawx]

I entered this:

=AVERAGE(IF(MOD(ROW(C3:C500),2)=0,C3:C500,""))

& got 4.13655

Okay you cant preselect the cells for average then. It will count them so your sum/count(to 500) will lower the average. It wont affect the sum but it will mess with average. So whatever your last c cell you enter into that will be the final one in the C3:CLast number you enter.

 

[golfinnut]

Note these formulas should only work with the even cells for the odd cells you'll need this:

=SUM(IF(MOD(ROW(C3:C500),2)=1,IF(MOD(C3:C500,2)=1,C3:C500,""),""))

=AVERAGE(IF(MOD(ROW(C3:C500),2)=1,IF(MOD(C3:C500,2)=1,C3:C500,""),""))


Also once you have pasted those in the cells you'll need to hit Ctrl + Shift + Enter at the same time instead of a normal enter

Does the "=1" or "=2" represent whether it's the ODD or EVEN cell that we are summarizing or averaging?

Which formula for the EVEN?