NEED! Formula to calculate a conditional average of cells in multiple rows

[PhotoFranco]

Help needed! I'm trying to create a calculator of sorts for work that is aiming to compare and old vs new commission system. It consists of a table with two calendar months side by side, if that makes sense. The purpose is to track daily output. This new commission system brings about the potential to bonus on a daily basis, and I want to make a formula that would accurately represent your current daily bonus as you fill it in day-by-day. My best stab so far has been trying to use some sort of =AVERAGEIF function, making the condition that the cell has to be ">0" (WHICH IS IMPORTANT, because the tool is meant to be filled in daily, and grow daily....this data is supposed to represent a projection based off of the culmination of all previous work) so its not accounting for days people haven't worked yet, or days where a bonus was not reached. My problem is in defining the range. As you can see the "Pot. Bonus" cells I'm trying to reference are structured within a calendar month format and cannot be easily defined as a range. I have tried creating a dynamic range (CTRL+ selecting each cell, and naming it in the cell name bar) and using that as my range in the formula....the formula recognizes the dynamic range, but it still pumps out a #VALUE! error.

I'm sure there's an Excel guru with way more knowledge than me out there somewhere who can help me!

Example1.PNG

Exampe2.PNG

[sandy666]

Pictures are useless
attach example desensitized excel file



In attached Excel file try not to use:

• merged cells
• password protection
• unnecessary formatting like: colours, borders, aligning another than default, etc...
• unnecessary zooming/grouping/freezeing

You have words to logically describe the problem

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

[PhotoFranco]

Sorry, this is my first time posting on this forum...wasn't sure how those images were going to come out to be honest haha. Thank you for the instructions on uploading the sheet; I hope this is more help!

[kvsrinivasamurthy]

Pl show how you arrive results manually.

[JohnTopley]

For your various totals use

in T36 (as example)

=SUMIFS($R$4:$AD$30,$Q$4:$AC$30,$S36)

in Y36

=SUMIFS($R$4:$AD$30,$Q$4:$AC$30,"Pot. Bonus")


What is your AVERAGEIFS formula ???