Nested Array Formula to Determine Average cost Per Mile in 100 mileincrements
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 |
Nested Array Formula to Determine Average cost Per Mile in 100mile increments
Try this array* formula:
=AVERAGE(IF((A$9:A$44100)*(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$44E1)*(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 On Oct 15, 5:52*pm, Mark Gaipo wrote: 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 |
All times are GMT +1. The time now is 03:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com