Value error with average formula - Because of blank cells?

[nobodyukno]

Ok here's the situation:

I am averaging 4 cells out of a possible 6. 2 cells are constant. The other 2 cells are chosen from a possible 4 cells depending on a search function in my formula.


Still, all in all, 4 cells are being averaged. Yet, when one of these 4 cells is blank, I get a value error. There is an error formula in this blank cell that returns blank based on the formula.

I want the formula to average depending on the number of values are present. So if there are 3 values present, then it divides by 3, two values present, divides by two, and so on...


=AVERAGE(O11,IF(ISERROR(SEARCH("throws R",T11)),BC11*4.2,BO11*4.2),IF(ISERROR(SEARCH("at",T11)),CM11*4.2,CA11*4.2),(AF11*4.2))

[nobodyukno]

Anyone have any idea?

[igoodable]

could you place all 6 cells in one column and use an averageifs formula? For example: AVERAGEIFS(C:C,C:C,"<>"&"") will average all items in column C that are non blanks.

[Tony Valko]

If you're getting a #VALUE! error that means one or more of these cells contains text (or a formula blank):

BC11
BO11
CM11
CA11
AF11

You could try putting them inside the N(...) function:

N(BC11)*4.2

[nobodyukno]

could you place all 6 cells in one column and use an averageifs formula? For example: AVERAGEIFS(C:C,C:C,"<>"&"") will average all items in column C that are non blanks.

Not preferably, I have hundreds of rows of data so I would need to create hundreds of columns

[nobodyukno]

If you're getting a #VALUE! error that means one or more of these cells contains text (or a formula blank):

BC11
BO11
CM11
CA11
AF11

You could try putting them inside the N(...) function:

N(BC11)*4.2

Yes, these cells have a formula in them even if blank

Tried the N function. It works, but it just continuously divides by 4. It does not divide by the amount of numbers that are present

[igoodable]

you'll need an iserror condition for each of the calculated cell. IE: IF(ISERROR(BC11*4.2),"",BC11*4.2)

[Tony Valko]

Try it like this...

=AVERAGE(O11,IFERROR(IF(ISERROR(SEARCH("throws R",T11)),BC11*4.2,BO11*4.2),{""}),IFERROR(IF(ISERROR(SEARCH("at",T11)),CM11*4.2,CA11*4.2),{""}),IFERROR(AF11*4.2,{""}))

[nobodyukno]

Try it like this...

=AVERAGE(O11,IFERROR(IF(ISERROR(SEARCH("throws R",T11)),BC11*4.2,BO11*4.2),{""}),IFERROR(IF(ISERROR(SEARCH("at",T11)),CM11*4.2,CA11*4.2),{""}),IFERROR(AF11*4.2,{""}))

Thats giving me a value error

[igoodable]

=AVERAGE(O11,IF(ISERROR(SEARCH("throws R",T11)),if(iserror(BC11*4.2),"",BC11*4.2),if(iserror(BO11*4.2),"",BO11*4.2)),IF(ISERROR(SEARCH("at",T11)),if(iserror(CM11*4.2),"",CM11*4.2),if(iserror(CA11*4.2),"",CA11*4.2)),(AF11*4.2))

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[nobodyukno]

There's too many formulas to try to recreate so I'm uploading the workbook.

So column CV is where the formula will go. What the formula should do is the following starting with cell CV5:

1.) Take the value from O5
2.) Take the value from AF5
3.) Depending on the contents in T5, the value from either cell BC5 or BO5 will be taken
4.) Depending on the contents in T5, the value from either cell CA5 or CM5 will be taken
* The search function is listed in the first post
5.) Average these four values
* As you can see from the workbook, the blank cells have formulas in them


The results should be the following:
CV5 - 1.48095
CV12 - 1.75016 (This cell should be divided by 3 as cells AF5 is blank)

Book1.xlsx

[nobodyukno]

Anyone take a stab at it?

[igoodable]

Yes, these cells have a formula in them even if blank

Tried the N function. It works, but it just continuously divides by 4. It does not divide by the amount of numbers that are present

The reason it's divided by 4 because the n function yield value of 0 and therefore included in the average calculation. If you replace the n function with an iserror formula and replace the error with a blank value, average will ignore these values. What error did you get from my formula?

[nobodyukno]

The reason it's divided by 4 because excel counts the n function yield value of 0 and included in the average. Therefore if you replace the n function with an iserror formula and replace the error with a blank value, average will ignore these values. What error did you get from my formula?

I got value errors for the cells that did not pull 4 cells of data. Again, the blank cells have formulas in them so I don't know if that is what's causing the value error. I attached the workbook in my last post

[igoodable]

does o11 and af11 always have a value? If not, you'll need to put an iserror formula on it too.

[nobodyukno]

does o11 and af11 always have a value? If not, you'll need to put an iserror formula on it too.

The only one that always has a value is O11

[igoodable]

=AVERAGE(O11,IF(ISERROR(SEARCH("throws R",T11)),IF(ISERROR(BC11*4.2),"",BC11*4.2),IF(ISERROR(BO11*4.2),"",BO11*4.2)),IF(ISERROR(SEARCH("at",T11)),IF(ISERROR(CM11*4.2),"",CM11*4.2),IF(ISERROR(CA11*4.2),"",CA11*4.2)),IF(ISERROR(AF11*4.2),"",AF11*4.2))

what about this? I couldn't open your workbook btw... all the values becomes error even if I click do not update.

[nobodyukno]

=AVERAGE(O11,IF(ISERROR(SEARCH("throws R",T11)),IF(ISERROR(BC11*4.2),"",BC11*4.2),IF(ISERROR(BO11*4.2),"",BO11*4.2)),IF(ISERROR(SEARCH("at",T11)),IF(ISERROR(CM11*4.2),"",CM11*4.2),IF(ISERROR(CA11*4.2),"",CA11*4.2)),IF(ISERROR(AF11*4.2),"",AF11*4.2))

what about this? I couldn't open your workbook btw... all the values becomes error even if I click do not update.

Same thing where it computes, but it returns a value error for any cells that don't have all 4 cells with data.

Hmm I guess that's because pretty much all those cells have references to other worksheets that are not present in that workbook?


EDIT:

I had some luck I thought with this formula, but now it returns blank any cells that do not pull from 4 cells of data. Don't know what I would've changed to affect it.

=IFERROR(AVERAGE(O11,IF(ISERROR(SEARCH("throws R",T11)),BC11*4.2,BO11*4.2),IF(ISERROR(SEARCH("at",T11)),CM11*4.2,CA11*4.2),(AF11*4.2)),"")

[igoodable]

yes - it is because of the links. Could you start a new book and produce a sample of the errors only? For example: include only relevant fields in the calculations and the items that would cause the error.

[nobodyukno]

Ok this should explain what is needed. Let me know if you have any questions:

Book11.xlsx

I'll explain the search function a little as well:

1.) Searches for the word "throws R" in column A - If error (doesn't find the search) it takes the value from column D, if it finds the words "throws R" it takes the value from column E
2.) Searches for the word "at" in column A - If error (doesn't find the search) it takes the value from column G, if it finds the word "at" it takes the value from column F

**ALSO**

This workbook isn't completely accurate to the real thing since columns C:G all have formulas with index/match functions in them. So if blank, these cells still have index/match formulas in them, and are not just completely blank.

[nobodyukno]

Let me know if you guys have any questions regarding the workbook

[martindwilson]

try each bit of the if on its own i had trouble un picking it
what are the distinct things you are averaging
average(b2,what1,what2,what3,what4)

[igoodable]

I recommend you add four consecutive columns at the end of your spread sheet. Build if conditions for each of those columns to re-pull the values you need. THEN use averageif formula like I suggested initially. You'll use more real estate, but it'll solve your problem for sure.

[Tony Valko]

I don't know if the logic of your formula is correct or not but...

The AVERAGE function will only ignore text values when they're elements of an array (or range of cells).

For example:

=AVERAGE(O11,"","",AF11*4.2)

Will return the #VALUE! error since the text values (the formula blanks) are not elements of an array (or a range of cells).

In my earlier suggestion you may have noticed that I enclosed the formula blanks in squiggly brackets. This forces them to be evaluated as arrays (even though the array is just a single element) allowing the AVERAGE function to ignore them:

=AVERAGE(O11,{""},{""},AF11*4.2)

On a side note...

I'm totally confused as to what the OP is wanting to do at this point!

I looked at the most recent sample file (post #21) but still can't figure out what cells need to be calculated and under what conditions.