Need Help with Average formula

[thatgreen]

I need some help getting a formula to calculate the average of 4 non contiguous cells that have a number and text (ie cell P10= "86.79% of green apples").
I need to average 4 cells P10,P19,P28,P37.
P10= 86.79% of green apples
P19= 88.73% of green apples
P28= 0% of green apples
P37= 0% of green apples

Currently I have this:

Please Login or Register  to view this content.

and P46= 43.88% of green apples

So it's calculating the average but I need it to not include P28 and P37 since they are zero and need P46 to say 87.76% of green apples.

However P10,P19,P28,P37 all are also updated based on other inputs so if the sheet gets updated and P28 becomes 85.77%, I would then need P46 to updated to 87.10% since it would've taken the average of 3 numbers instead of just the 2.

I think my formula should be an AVERAGEIF function, but don't know how to make it work with multiple cells like this.

Thanks for taking a look, and let me know what other information is needed, if any.

[FlameRetired]

Hello that green. Welcome to the forum.

Try array entering this in P46.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

It returns 87.76% of green apples at my end.

[FlameRetired]

This is a little shorter. Still array entered.

Please Login or Register  to view this content.

[thatgreen]

Thank you for both of these solutions, however neither worked for me. Is one of them supposed to work better than the other? I'm still fairly new to excel and not very familiar with the formulas you provided.
Also I should note that P1:P45 also have data in them that could display 0%, however none of the other cells (aside from the original 4) contain the &"% of green apples", some contain "% of (x,y,Z)"

I just want to make sure that the formula will only be including the cells P10,P19,P28,P37 as those 4 particular cells contain the "% of green apples"

Thanks again!

[thatgreen]

Okay I'm attaching a link to a photo of my spreadsheet to hopefully help understand better. Forget apples. Basically I am trying to get an Average in P46 that is the Average of P10,P19,P28,P37. However if any of P10,P19,P28,P37 are 0.00% (like currently P28,P37 are) it only averages the cells that have values >0. Currently I need P46 to =87.76% not 43.88%.

excel.png

[FlameRetired]

Please attach the Excel workbook. No one wants to retype data you already have.


To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[thatgreen]

See that's my problem, desensitizing the data is over 50 sheets for me. This is 1 column that calculates from all over the workbook. Any other options?

[FlameRetired]

We don't need to see all 50 sheets. Just the rows P4:P47 you show in the screen shot should suffice with instructions relevant to the sample. Dummy up the "blacked out" data.

[FlameRetired]

... Also I should note that P1:P45 also have data in them that could display 0%, however none of the other cells (aside from the original 4) contain the &"% of green apples", some contain "% of (x,y,Z)"

I just want to make sure that the formula will only be including the cells P10,P19,P28,P37 as those 4 particular cells contain the "% of green apples"

Yes. My first formula does that.

Please see the attached with slight modification to 1st formula.

Please Login or Register  to view this content.

[thatgreen]

I am attaching what you are asking for, but I don't know if it will really help. Everything is just a REF because its all pulling from elsewhere.

[FlameRetired]

Try copying what you have and pasting values only back into the range. The formulas will no longer be there. Only the values remain.

[thatgreen]

Okay try this, the left 2 columns are just values, the right 2 columns are the formulas I'm using to get those values.

[FlameRetired]

Array enter in A48 (Ctrl + Shift + Enter)

Please Login or Register  to view this content.

[thatgreen]

Okay well I got the following message display, and the cell on my worksheet says 0.00... Thoughts?
Attachment 629707

[FlameRetired]

Does it do that in my attached?

[thatgreen]

With the new one, if I enter as array it says "Array formulas are not valid in merged cells" Is this because I format P:Q together? If I don't enter as array is just returns 0.00.

Edit: Even if I un-merge P:Q and just enter this in P it still returns 0.00.

Also bear in mind I did change all of A's in the functions to P's for my sake.

[FlameRetired]

With the new one, if I enter as array it says "Array formulas are not valid in merged cells" Is this because I format P:Q together? If I don't enter as array is just returns 0.00.

Edit: Even if I un-merge P:Q and just enter this in P it still returns 0.00.

Also bear in mind I did change all of A's in the functions to P's for my sake.

• Do you see that the formula is working in my upload?
• Once you un-merge the cells the formula must be array entered.
• Did you reference the new source range in the formula?
• Is the formatting of the data you are using consistent with your previous upload?

[thatgreen]

• Do you see that the formula is working in my upload?
  - Yes, I do, which is why I don't understand why it's not working in mine. Like I stated I don't really understand exactly what your function is doing it, otherwise maybe I just need 1 additional tweak?
• Once you un-merge the cells the formula must be array entered.
  - Correct, it was, and it is currently displaying 0.00
• Did you reference the new source range in the formula?
  - Well I changed all the A1:A47 to P1:P47 (where my data is) so, I think so...?
• Is the formatting of the data you are using consistent with your previous upload?
  - Yes, but there is just a lot more data around and in the workbook altogether. Each cell of P1:P47 is basically calculating from different sheets depending on the cell. So P10,P19,P28,P37 are numbers located on different sheets within the workbook itself. So those numbers will update at some point and the % in P28,P37 will not be 0% forever. When it becomes a number I want to include it in the average in P46, but when it is 0% I do not want it included in the average of those 4 numbers.

[FlameRetired]

My formula should work with all you described --- unless there is a deviation from the string "% Avg of Twist Proj of Zone w/o G Bud" in the target cells. Is there? An extra space is all it would take.

[thatgreen]

My formula should work with all you described --- unless there is a deviation from the string "% Avg of Twist Proj of Zone w/o G Bud" in the target cells. Is there? An extra space is all it would take.

Would it not work if the P10,P19,P28,P37 are merged cells? They are currently P:Q merged...

Other than that, everything is typed correctly... but it's just coming up 0.00

this is the formula in P10 for example:

Please Login or Register  to view this content.

I have the sheet name in O3,O12,O21,O30 and its pulling a percent that is calculated on each of those sheets from cell O3 on each sheet.

[FlameRetired]

The formula you are currently trying --- can you copy/paste the whole thing in the next post?

[thatgreen]

This is what I have in P46 using Ctrl+Shift+Enter

Please Login or Register  to view this content.

[thatgreen]

Okay so I changed the formula to actually only go to P45 and now the cell says #VALUE! and when I check the function error this message appears... It says it! Just not in the cell??
Attachment 629715

NEVERMIND. Forgot to hit array again... WORKING!

[FlameRetired]

There's the problem. You are entering the formula in the range you are referencing. P46 is in the range P1:P47. Try array entering it in P48.

I need to turn in. It's past my bed time. I'll look at this again tomorrow if you haven't already solved it by then.

[thatgreen]

There's the problem. You are entering the formula in the range you are referencing. P46 is in the range P1:P47. Try array entering it in P48.

I need to turn in. It's past my bed time. I'll look at this again tomorrow if you haven't already solved it by then.

It's working now. I myself and heading to bed. THANK YOU!!!

[FlameRetired]

You are welcome. Glad you found solution.