Nested Array Formula to Find Average Cost Per Mile Between 2Distances

[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

 

[katie_c]

You may want to try and array formula for each band of mileage:
{=sum((A9:A44>0)*(A9:A44<100)*(A9:A44))/sum((A9:A44>0)*(A9:A44<100)*(B9:B44))}

This essentially says, look in column A for the specified range, then sum column A (Mileage) for the matching records and divide by sum of column B (Cost) for the matching records.

-Katie

 

[John McGhie]

Nice one, Katie

You may want to try and array formula for each band of mileage:
{=sum((A9:A44>0)*(A9:A44<100)*(A9:A44))/sum((A9:A44>0)*(A9:A44<100)*(B9:B44))}

This essentially says, look in column A for the specified range, then sum
column A (Mileage) for the matching records and divide by sum of column B
(Cost) for the matching records.

-Katie

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

--

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]