Array formulas

Amy Beryl

Board Regular
Joined
May 5, 2011
Messages
54
Hi all -

I have just discovered array formulas which appear to answer an averaging issue I have. One question though can I add to an array average formula an initial test to check if all the fields are zero.

For example, the array formula I wrote that works is:

{=average(if(h24:j24=0,h24:j24))}

I would like to preceed this average formula with some kind of test like:

=if(h24:j24=0,0 then the arravy average formula.

I have tried various formula constructions but none of them work and I don't want to go to macros.

Any help would be appreciated.

Amy
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Amy Beryl

Board Regular
Joined
May 5, 2011
Messages
54
The array average formula works if there is at least one number contained in the range, but it returns a #DIV! if all the cells are zeros. I want to test for this condition before the array activates.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top