Easier way to write this formula?

[JDL48]

This is my first time ever posting a question on a forum so I apologize if I do something wrong. Hopefully my question is fairly simple, and hopefully so is the answer.

Is there a way to clean up the formula I have in cell C15?
=AVERAGE((C$7-B8)/30,(C$7-B9)/30,(C$7-B10)/30,(C$7-B11)/30,(C$7-B12)/30,(C$7-B13)/30)

Image 1.PNG

[Jonmo1]

Perhaps
=SUMPRODUCT((C$7-B8:B13)/30)/6

[Glenn Kennedy]

Why not just use =AVERAGE(C8:C13)

[martindwilson]

one way
=AVERAGE((C$7-B8),(C$7-B9),(C$7-B10),(C$7-B11),(C$7-B12),(C$7-B13))/30

[MrShorty]

WARNING: this post contains material of an algebraic and/or mathematical nature. Mathophobes should cover their eyes/ears and sing "la la la la la" while having someone else scroll past this post.
I don't know if you are up to the algebra exercise, but a little algebra will simplify that.

definition of arithmetic mean: average=sum(xi)/n
In your case, you are computing average[(k-xi)/30]=sum[(k-xi)/30)]/n
manipulation shows --> sum[(k-xi)/30] is the same as n*k/30-1/30*sum(xi)
substitute back into average formula --> [n*k/30-1/30*sum(xi)]/n = nk/30/n-1/30*sum(xi)/n] = k/30-1/30*average(xi)
I will assume that, if you got this far, you will be able to translate that into Excel/spreadsheet syntax.

[ChemistB]

or as an array

=AVERAGE($C$7-$B$8:$B$13)/30
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[JDL48]

Thanks Jonmo1, =SUMPRODUCT((C$7-B8:B13)/30)/6 is what I was looking for, and it works perfectly. The array might work as well, but I haven't tested it yet.

Thanks everyone!!

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)