Cells that average differences between 2 columns

Hello, again

I have some sorting out to do, and would like a couple of cells to do the following:

The first one I'd like to sum up the average difference between values in columns B and C.
(This one's in the kit already, just mentioned for introductory purposes)

The second cell should return the greatest difference between values in columns B and C.
The differences being between values on the same level (B4-C4, B5-C5 etc)

These two I might possibly be able to churn out myself, if I horsed around with them a couple of days/weeks, so thanks in advance for saving me that time.

The next one is a bit trickier, where another couple of cells performs the same tasks as above, only this time treating the differences between the values following one another down the same column.
Instead of B4-C4, it would be B4-B5, B5-B6 and so on.
This time as well the two cells would return the average difference and greatest difference.

Finally, the challenge totally beyond my range: a "diagonal" twist of these, where the differences I want counted, maxed, averaged and so on, are between mentioned C-column-cells and the cells one row above in the column beside, in this case it would be the differences between B3-C4, B4-C5 and so on.

If it were just a few rows, I'm sure it would be just some fairly simple maths required to solve the problem, but since there are hundreds and hundreds of descending rows, I need some sort of ingenious Excel formula that doesn't take an hour to type.

Anybody know it?
BCB

Originally Posted by BCB

Hello, again

I have some sorting out to do, and would like a couple of cells to do the following:

The first one I'd like to sum up the average difference between values in columns B and C.
(This one's in the kit already, just mentioned for introductory purposes)

The second cell should return the greatest difference between values in columns B and C.
The differences being between values on the same level (B4-C4, B5-C5 etc)

These two I might possibly be able to churn out myself, if I horsed around with them a couple of days/weeks, so thanks in advance for saving me that time.

The next one is a bit trickier, where another couple of cells performs the same tasks as above, only this time treating the differences between the values following one another down the same column.
Instead of B4-C4, it would be B4-B5, B5-B6 and so on.
This time as well the two cells would return the average difference and greatest difference.

Finally, the challenge totally beyond my range: a "diagonal" twist of these, where the differences I want counted, maxed, averaged and so on, are between mentioned C-column-cells and the cells one row above in the column beside, in this case it would be the differences between B3-C4, B4-C5 and so on.

If it were just a few rows, I'm sure it would be just some fairly simple maths required to solve the problem, but since there are hundreds and hundreds of descending rows, I need some sort of ingenious Excel formula that doesn't take an hour to type.

Anybody know it?
BCB

One line fits all (with variations)

with figures in C & D you could do a diff with =C1-D1 which, when formula filled downwards will increase to c2-d2 c3-d3 etc, irrespective of the row you started on. (for ease and simplicity we always match the row)

for your Largest difference (and second, third and fourth largest later on)

=LARGE((C$1:C$10-D$1:D$10),1)
will give the largest number

=LARGE((C$1:C$10-D$1:D$10),ROW()-9)
will do the same for a formula put into column P on row 10 etc
formula=fill down for second, third etc

=LARGE((C$1:C$9-C$2:C$10),1)
will take the following row

=LARGE((C$1:C$9-D$2:D$10),1)
will take the diagonal

all are array formulae and so when #Value is the answer, modify the formula and Ctrl-Shift-Enter

hth
---

Thanks again. Mr. Hessey


I'll try them out the following days.
I'm not sure if I follow you on all the explanations, but I'll get back to that when need be.
At first glance they seem like the kind of thing I could use.
And one of the "AVERAGE"-functions will do its thing in place of the LARGE, I take it?
Due to your "one line fits all" and all?
Well, I shall soon find out.

Thanks again

bcb

Originally Posted by BCB

Thanks again.

(please don't call me 'Mr')

I'll try them out the following days.
I'm not sure if I follow you on all the explanations, but I'll get back to that when need be.
At first glance they seem like the kind of thing I could use.
And one of the "AVERAGE"-functions will do its thing in place of the LARGE, I take it?

An extract from your question is:

"The first one I'd like to sum up the average difference between values in columns B and C.
(This one's in the kit already, just mentioned for introductory purposes)"

. . so I didn't do 'Averages'

Your next lines were:

"The second cell should return the greatest difference between values in columns B and C.
The differences being between values on the same level (B4-C4, B5-C5 etc"

which should be handled by the various 'Large' examples (the various combinations required being all possible in a similar formula, hence the 'one-fits-all' comment.

Due to your "one line fits all" and all?
Well, I shall soon find out.

Thanks again

bcb

Good luck with your testing.

---

Hello, again

Seems like the thread vanished from the Forum pages. Had some trouble finding it.
I gave a bad explanation on the averages: I meant that I could cope just at this level, and get away with a formula like =(B1:B9)-(C1:C9)/9, whereas the other problems would have left me typing each individual subtraction in some long and clunky formula(e). I am no stranger to manual labour, but that would have been ridiculously ineffective in the long run.
As opposed to the nifty formulae you handed me here.
Twaked them a bit, and - phew - they finally work.
And, yes, also with the Average function.

Thanks again

bcb

Originally Posted by BCB

Hello, again

Seems like the thread vanished from the Forum pages. Had some trouble finding it.
I gave a bad explanation on the averages: I meant that I could cope just at this level, and get away with a formula like =(B1:B9)-(C1:C9)/9, whereas the other problems would have left me typing each individual subtraction in some long and clunky formula(e). I am no stranger to manual labour, but that would have been ridiculously ineffective in the long run.
As opposed to the nifty formulae you handed me here.
Twaked them a bit, and - phew - they finally work.
And, yes, also with the Average function.

Thanks again

bcb

Thanks for the response, and yes, the formulas that we give need some adjustment to your situation. Formula-wise we assume, unless told differently, that everything begins in A1 and, if required, goes to A9, A10 or A100 depending on the day.

Good to see it worked out for you.

Note, to find threads/posts for yourself or any other person, select the bullet point next to the name, and then 'Find All Posts' etc.

---

Hello again, Bryan Hessey

I'll take my chance heating up this thread again, partly to show some of the respondents in my recent thread: "How to calculate the percentage a returned average represents of cond. measured range" what these formulae try to calculate.
I take it your lack of response to that thread is due to your not having any further suggestions to those questions, but hope it's not utterly impossible to combine (several) array and SUMPRODUCT formula, like the one duane suggested.
That one I will definately try, but thought I'd ask your opinion before I start, because I suspect there'll be some horrendous tweaking involved, language hassle and all. Any advice?

bcb

Hello, again

Below 5%?
Leaves quite a bit to learn for the rest of us.

Good luck with your cricket test Match!

BCB

Ah, well..."poignant"
Got me.
I must admit, or rather, insist I was a bit hesitant about the word (alternatives like "lucid" and "illuminating" popping up), but as my immediate surroundings got a bit hectic, there weren't time for any extensive mining of synonyms. Just checked my dictionary now, and realized I was at best a bit off the mark ("keen"), or hilariously, plain wrong ("distressing to the feelings, deeply moving"). Now that's a lesson.
However, the dictionary is almost 30 years old, and I could swear (until now) that the term has meant something along the lines of "precise" or "to the point" in the usage wherein I've encountered it over the years; then again it's a questionable attitude to have misused terms excused by the ever-changing reign of convention. Anyway, there you have my intended description of your explanations.

bcb