Average of calculations on every row in a table?

**kend0g187** · 08-19-2020, 05:04 PM

Hello. Imagine a table with two columns of data:

4	1
8	2
60	30

I want to divide each number in Column 2 by the corresponding number in Column 1 and then take the average of those. (Answer should be 0.333.) I've tried

Please Login or Register  to view this content.

but that gives me anything from a wrong answer to just the last row to a #VALUE! error. Any ideas? Thanks!
excelerror.png

**TMS** · 08-19-2020, 05:19 PM

Try:

Formula:

Please Login or Register  to view this content.

**kend0g187** · 08-19-2020, 06:10 PM

Thanks! I'm still getting an error though. It seems to be due to the blank rows in the table. Trying to fix it, but nothing working...
Attachment 691789

**TMS** · 08-19-2020, 06:15 PM

Follow the instructions in the yellow banner to attach a file.

**TMS** · 08-19-2020, 06:17 PM

What blank rows? Why have you got blank rows in a Structured Table? What sort of error?

**kend0g187** · 08-19-2020, 06:55 PM

Yes, my table may have blank rows so I want the formula to handle them properly. It's a #DIV/0! error.

**protonLeah** · 08-19-2020, 07:01 PM

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts braces {} around the formula (though do not attempt to manually insert these yourself).

**kend0g187** · 08-19-2020, 07:06 PM

Yes, I managed to get it working with an array formula:

Please Login or Register  to view this content.

I don't really understand what's going on or why the behavior was so strange on my previous attempts. I think I need to brush up on my Excel knowledge.

**wk9128** · 08-19-2020, 08:23 PM

Array formular

{=AVERAGE(IF((F2:F12<>0)*(D2:D12<>0),F2:F12/D2:D12))}

**Bo_Ry** · 08-19-2020, 09:14 PM

Please try array formula

=AVERAGE(IF(Table1[Entrants],Table1[Result]/Table1[Entrants]))

**kend0g187** · 08-19-2020, 09:57 PM

Yes that worked and it's cleaner than the SUM one. Thanks all!

Average of calculations on every row in a table?

LinkBack

Thread Tools

Rate This Thread

Display

Average of calculations on every row in a table?

Re: Average of calculations on every row in a table?

Re: Average of calculations on every row in a table?

Re: Average of calculations on every row in a table?

Re: Average of calculations on every row in a table?

Re: Average of calculations on every row in a table?

Re: Average of calculations on every row in a table?

Re: Average of calculations on every row in a table?

Re: Average of calculations on every row in a table?

Re: Average of calculations on every row in a table?

Re: Average of calculations on every row in a table?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] average some 0's into calculations while ignoring others.

[SOLVED] Need help in average calculations

Problem with AVERAGE calculations and #DIV/0! display.

mean average of 3 calculations

[SOLVED] Average of Multiple Calculations

[SOLVED] Average %age Calculations

[SOLVED] Monthly average calculations

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions