• 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.

how do you average every other column?

Harry0

Harry0

Member
Hi
In my last post I asked it was a bit more complicated so I am asking for a simpler solution without shifting the column. The average calculations were off when manually using every other column, which I do not understand in the formula so any more accurate way?

I have the sum of every other column but not an average.

here is the example form the previous post with a download file.
http://forum.chandoo.org/threads/an...-while-shifting-cells-every-new-column.33903/

Sorry since this is another question despite similar so its another post.
Thanks
 
Harry0
if it would do this way...
You would see 'averages' in B&C-columns.
 

Attachments

  • shifting average every other column.xls
    30 KB · Views: 3
Both are good info but isnt their a way to be able to keep the structure without adding new rows.
 
@John Jairo V ... Yes ... so I am asking for a simpler solution without shifting the column...
=IFERROR(AVERAGEIFS($E4:$AA4,$E$3:$AA$3,B$3),"") for avoid some errors

@Harry0 Where You do not want to add new rows? below?
1) Many times it would be better have some 'free places' around data,
for further possibilities.
2) If You someday need other years,
then maybe those 'months would change to dates (output mmm/yy ?)
3) If You are interesting to get ex last 4 months average
then You could use something like this with Cell[D3].
 

Attachments

  • shifting average every other column.xls
    30.5 KB · Views: 9
The reason I ask is that others will use it and i know they will mess it up if I do it the other way.
 
... hmm? ...
If someone can only write to Cell[A1] letter A.
It would be okay for few years!
If someday should need to learn to write to Cell[B2] letter B ...
It could not be mission impossible or is it?
If You're worry about mess ... use protection
as well as in many many other cases too!
 
Sorry to say its a bit more complicated than that. The program is already bloated enough which needs a powerful computer to run despite taking 1 minute around to calculate formulas.

I was originally looking for a to find a "average" formula to something similar which uses the sum formula =SUMPRODUCT(A1:H1,MOD(COLUMN(A1:H1),2))

The last post was kind of better but the formula was not accurate when calculating average. .
http://forum.chandoo.org/threads/an...-while-shifting-cells-every-new-column.33903/
 
Your case is (was)
how do you average every other column?
and
You were worry about:
The reason I ask is that others will use it and i know they will mess it up if I do it the other way.
Next You write that:
The program is already bloated enough which needs a powerful computer to run despite taking 1 minute around to calculate formulas.
I can only ask:Are Your formulas Okay?
and what would be connection with original case?
 

Attachments

  • ShiftingCountAverageEveryOtherColumn.xls
    29.5 KB · Views: 3
Thanks anyway.
The calculations are still different when using the manual average formula. I Guess its best to use the other formula
odds =SUMPRODUCT(A1:H1,MOD(COLUMN(A1:H1),2))
evens =SUMPRODUCT(A1:H1,MOD(COLUMN(A1:H1)+1,2))
and the other and then divide the amount it counts which saves processing power and more accurate in results sorry to say.

f it was me using it then I would do what you guys say. But I am not the one using it and trying to figure out the mess for people that do not know how to use excel or want to mess with it. Its like I cant please everyone LOL

I would need to copy the formula to 4000 places which has already over 10 formulas in it, despite the program I use calculates over 1-10 million cells every time I edit something. Their was no other way to do it since I asked many in this forum and even potentially paid program professionals were scratching their heads and gave up.

This site erased my post tip how updating MS excel is a bad idea which can slow the program down. And I have seen it crash the program when updating it since it took over 5 minutes to calculate. Has to downgrade to get it working faster. You would think they would appreciate a tip on how to speed the clunker.

Thanks.
Post closed.
 
You must log in or register to reply here.
Back
Top