# Problem using nested IFs

#### andrewb90

##### Well-known Member
Hello everybody,

I seem to have once again attempted to write a formula that was beyond my current skill level, and I need some assistance.
In the Cells B9,B11,B13,B15,and B17 I have this formula(well the cell refs are different):
Code:
``=IF(B22>0,B22,"-")``
In B19 I am trying to get an average of the five cells, but only if they have data in them. i was figuring some sort of nested if would work, but I haven't had any luck.
The only useful part about the data is that each cell would fill up in order (as they represent five consecutive weeks) meaning that B9 could have number and none of the others would but that wouldn't be the case for B13 (if B13 had data then 9 & 11 would have data at least)

I hope I am making enough sense for somebody to help me.

Regards,

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi Andrew,

Is this what you're after?

=IF(COUNT(B9:B17)=5,AVERAGE(B9:B17),0)

Robert

Something like this might give you an idea

Excel 2003
B
9-
10
1125
12
1315
14
1525
16
1717
18
19-
Sheet1
Cell Formulas
RangeFormula
B19=IF(COUNT(B9,B11,B13,B15,B17)<5,"-",AVERAGE(B9,B11,B13,B15,B17))

Hi Michael,

This is a short version of what I tried based on your idea, but I think I am missing something here:

Code:
``=IF(COUNT(B22,B23,B24,B25,B26)=1,"=B22",IF(COUNT(B22,B23,B24,B25,B26)=2,"=AVERAGE(B22,B23)"))``

If I have either 1 or 2 numbers listed it displays the formula =B22 instead of actually calculating it. Is there a proper way I should be writing that part?

Hello everybody,

I seem to have once again attempted to write a formula that was beyond my current skill level, and I need some assistance.
In the Cells B9,B11,B13,B15,and B17 I have this formula(well the cell refs are different):
Code:
``=IF(B22>0,B22,"-")``
In B19 I am trying to get an average of the five cells, but only if they have data in them. i was figuring some sort of nested if would work, but I haven't had any luck.
The only useful part about the data is that each cell would fill up in order (as they represent five consecutive weeks) meaning that B9 could have number and none of the others would but that wouldn't be the case for B13 (if B13 had data then 9 & 11 would have data at least)

I hope I am making enough sense for somebody to help me.

Regards,

Something like:

=SUM(B9,B11,B13,B15,B17)/INDEX(FREQUENCY((B9,B11,B13,B15,B17),0),2)

Replies
27
Views
690
Replies
1
Views
198
Replies
4
Views
175
Replies
8
Views
228
Replies
5
Views
333

1,196,442
Messages
6,015,293
Members
441,886
Latest member
fbell

### 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.

### Which adblocker are you using?

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

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