Nested Array Formula to Determine Average cost Per Mile in 100 mileincrements

[Mark Gaipo]

Greetings. I've spent a great amount of time this morning working on a
formula for the following.

I have a column (A) of 45 values which are distances between point "a" and
point "b".

I have the next column (B) as an Accounting Value which equals the cost to
move a cargo that distance listed in Column A.

Column C is the cost per mile - Column B divided by Column A

I now have to break down the average cost per mile in 100 mile increments.

I started with =Averageif(A9:A44,IF(A9:A44,>100,IF(A9:A44,<201,,),C9:C44)

I've tried using the following operators found on various discussions boards
and books - AND - OR

I've tried =Average(IF(

I've tried just about anything I could find on boards, books etc and now I
just need to break away for a while to avoid frustration but I am hoping
some one out there could offer a little assistance.

I am pretty certain AND & OR only work with absolute logical values.

Mark

 

[Pete_UK]

Try this array* formula:

=AVERAGE(IF((A$9:A$44>100)*(A$9:A$44<201),C$9:C$44))

If you want this to apply for a range of increments, then it will be
better to put those increments in a column somewhere and then you can
amend this formula to allow it to be copied down. Suppose you have
these values in column E starting with E1:

0 (or blank)
100
200
300
400
and so on,

then you can put this array* formula in F2:

=AVERAGE(IF((A$9:A$44>E1)*(A$9:A$44<=E2),C$9:C$44))

and copy it down as required. The 100, 200 etc represent the upper
range for the value in column F, i.e. 0-100, 101-200 etc.

* An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual <enter>. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
edit the formula then you will need to use CSE again.

Of course, this formula is taking the average of the costs per mile in
column C for each increment (as indicated in your attempts at the
formula), but this is not necessarily the same as the total cost for
the increment divided by the total of the mileage for that increment.

Hope this helps.

Pete