Problem using nested IFs

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,077
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi Andrew,

Is this what you're after?

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

Robert
 
Upvote 0
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))
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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
Back
Top