Complex Formula; Formula or VBA?

[easybpw]

Hello,

Thanks in advance for any help.

I'm trying to average out the top 3 expense lines on my spreadsheet that together fall under a specific dollar amount. So for example in column A I have the expense types (electric, water, sewer, etc, which all have the same indicator "E" in column B), in column C I have their total expense ($2,500, $500, etc.) and in column D I have their budget amount, which could vary so I'd need to be able to change the budget and then show the top 3 based on that budget. There are many expenses and I need to find 3 expenses that together have the highest average. I'm okay of the top 3 are somehow conditionally formatted to a color. But it's not practical to go thru all the expenses to find the average of the top 3. Basically its this:


electric E $2,500 $15,000
water E $500 $15,000
garbage E $300 $1,000

and so on...I need to be able to say these are the top 3 averaged expenses that fall under the $15,000 budget and these are the top 3 expenses that fall under the $1,000 budget and so on. Unfortunately there are many expense lines with various budgeted amounts. I am not so much worried about the budgeted amount as I am trying to figure out how to show the top 3. Again, happy to add columns. Prefer to do this with formulas vs. VBA but will do what works best.


Hope that was clear.

Thanks!

Bill

 

[FDibbins]

...But it's not practical to go thru all the expenses to find the average of the top 3....

maybe it is lol?

You can probably do this with AVERAGIFS and LARGE
=averageifs($C$2:$C$1000,$D$2:$D$1000,15000,$C$2:$C$1000,">="&large(if($D$2:$D$1000=15000,$C$2:$C$1000),3))
entered using CTRL SHIFT ENTER, not just ENTER

 

[Fazza]

If I understand correctly, can you make a pivot table and show the TOP 3 expenses by budget.
Be sure to refresh the pivot table when budget values change.