If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Array Function - cell to remain blank
I am a novice on Excel, however, I don't get easy projects.
I have a spreadsheet where D615 have values. In cell D17 is the Total, which is an average of what exists in D615. However, some of these cells have zero value, so this was taken into consideration when building the formula for D17. This is the formula: {=AVERAGE(IF(D6150,D615,""))} Right now, there are no values in D615 (nothing to input yet), but in cell D17 is the message: #DIV/0!. I've tried to see if I can fix the above array formula, but I'm making it worse. How do I get D17 (Total) to be empty when there is nothing (yet) in D615? Any suggestions will be helpful. Thank you. |
#2
|
|||
|
|||
Array Function - cell to remain blank
You're getting a division by zero error because AVERAGE() calculates
SUM(Xn)/n and n is zero when there are no elements. You could wrap your formula in an IF() like so: =IF(ISERROR(AVERAGE(IF(D6150,D615,"")),"",AV ERAGE(IF(D6150,D615, ""))) Remember to array-enter it again. /i. "mare" wrote in message ... I am a novice on Excel, however, I don't get easy projects. I have a spreadsheet where D615 have values. In cell D17 is the Total, which is an average of what exists in D615. However, some of these cells have zero value, so this was taken into consideration when building the formula for D17. This is the formula: {=AVERAGE(IF(D6150,D615,""))} Right now, there are no values in D615 (nothing to input yet), but in cell D17 is the message: #DIV/0!. I've tried to see if I can fix the above array formula, but I'm making it worse. How do I get D17 (Total) to be empty when there is nothing (yet) in D615? Any suggestions will be helpful. Thank you. |
Thread Tools | |
Display Modes | |
|
|