Stuck on AVERAGEIFS Problem

Hi everyone. I didn't find an answer to this question elsewhere, so I thought I'd ask and see if anyone can help me. I am trying to create a formula using AVERAGEIFS that is pulling criteria from three different columns and then giving me an average score from a fourth column if all of the other three columns meet the criteria. I can't get this to work and perhaps you can't do this or perhaps AVERAGEIF formula isn't suited to this. To give you a better feel for what I'm doing since I am not allowed to share the real data, I’ll make up a simplified version here with random imaginary criteria. I want to get the average score from column A, but only when it meets a specific combination of criteria from columns B, C, and D at the same time. So for example, I want to know what the average score is if Column B registers "Hard", Column C registers "Sunny", and Column D registers "Work"

Screen Shot 2021-02-02 at 9.26.53 AM.png

Here is how I'm trying to build my formula, but it only results in #VALUE!
=AVERAGEIFS($A$2:$A$7,$B$2:$B$57”Hard",$C$2:$C$7,”Sunny",$D$2:$D$7,”Work")

I realize I’m pulling from blank rows below, but I do this because I don’t want to have to change my formula each time I put new data in. When I do put new data in I insert it in row 2 wight below the column labels so that my new data is at the top. I’m pretty sure this is what’s causing my #VALUE error. When I change this to only calculate rows 2-5, I instead get a #DIV/0 error and I think that’s because of the missing data that I don’t have in columns B-D. Is there a way to not count these empty cells? Please help me and tell me what I'm doing wrong or if this just won't work for what I want. I looked online at some different examples and put this together based on what I was seeing. However, either I'm missing something simple or am way off! Thanks for your help.


Here is what’s going on in my real spreadsheet to give you a better picture. So, in all four columns, I have it set to pull from rows 1-1000, even though I am only using 300 rows right now and my data starts at row 4. I did this because I am constantly inserting new rows at the top of my data in row 4 (row 3 are the headers and rows 1 and 2 are the titles) and that is pushing the old data down. I did this so I don't have to constantly edit my formula to take in all of the new rows inserted (when I insert it pushes my start row down 1) and the older data at the bottom that otherwise would go beyond my data range when pushed down. However, these extra rows without data in them are creating the VALUE error. When I change just Column A's range to 4-300, I still get the VALUE error. When I change all 4 columns range from 4-300, the VALUE error changes to the DIV/0 error. I'm pretty sure the DIV error is because in columns B, C, and D I have some cells that are blank, especially in the older data because I added these columns later on and I don't have the data to fill in these cells and can't go back and get it, so I leave them blank. I tried switching these cells to #N/A, but then that threw off other formulas in my sheet that didn't like that, so I took it back out. I wish I could share the sheet with you, but because of the data. I have three questions:

1. Is there a way when I insert new rows at the top to not have my formulas in cells below this have their rows in the formula shift down? I find this in a lot of my formulas when I insert rows above them and they are calculating from the rows below the new row. I want the formula to stay exactly the same even though the cell with the formula has shifted down and the rows it's pulling from have been shifted down

2. Is there a way to calculate AVERAGEIFS and have blank rows in the formula so I don't constantly have to change my formula every time I inset a new row?

3. Is there a way for AVERAGEIFS to ignore blank cells altogether in the ranges I'm calculating or pulling data from?

4. Here's an additional question for you: I have all of my data-columns A-D on the left side of my sheet. After Column D, in cell E4, I used the freeze and splitting panes so that I can split the left side of my sheet from a right side in columns E-K. In column E-K is where all of my calculating of my data is going on to show me various averages, minimums, etc. Is there a way to split my sheet so that when I scroll on the left or right the other side stays where it is? Meaning they scroll independently? I know you can do this with rows, but I haven't found a way to do it with columns. My goal is to have the data on one side easily visible and the calculation on the other side easily visible, but separate.

Thank you in advance for your help. I'm trying to figure this out and am stuck and it seems my options are to forget this problematic calculation and go without this calculation in my sheet or look for a different way to approach this and not use the AVERAGEIFS formula. I don't know what the alternative would be.

Your Scores are aligned to the left, which implies that they are text values rather than real numbers, and if you try to do any arithmetic on them you will get the #VALUE error. Just change them to proper numbers and see how you get on.

Hope this helps.

Pete

Originally Posted by Pete_UK

Your Scores are aligned to the left, which implies that they are text values rather than real numbers, and if you try to do any arithmetic on them you will get the #VALUE error. Just change them to proper numbers and see how you get on.

Hope this helps.

Pete

This was just a screen shot of a generic table I typed up real quick without the actual formatting in my sheet. I didn't know how to put it in a table here. The values in Column A are calculated numbers from a formula and the values in B, C, D are text entries. I wonder if that's having an impact?

Sorry about that. I'm new around here and trying to figure things out!