I have a worksheet that has 4 columns. The information is populated based on
months with the last column for the quarter information.

for example:

Col K Col L Col M Col N

46% 0% 0% Average of the three columns K-M

My problem is since columns L and M have no data to calculate the
percentage, I need a formula that will calculate the Average. However, if
Col K has a value and L and M have 0% then return value in col K. However,
If K and L had values average K&L and should all three columns have values
average all three in column N.

So, At this point the Average should be 46%. If the the first column is
46% and column M is 43% then Colunm N should be 45%, and so on. At this
point the average is being calculated as if there are values in all three
columns. The formular creates an average of 15%.

I'm unsure how to nest the if statement, if that's the proper way to get to
what I want.

Any help would be great!

Thanks!

Try this

=IF(SUM(K1:M1)=0,0,SUM(K1:M1)/COUNTIF(K1:M1,"<0"))

Hope this helps,

Hutch

"LWilson" wrote:

I have a worksheet that has 4 columns. The information is populated based on
months with the last column for the quarter information.

for example:

Col K Col L Col M Col N

46% 0% 0% Average of the three columns K-M

My problem is since columns L and M have no data to calculate the
percentage, I need a formula that will calculate the Average. However, if
Col K has a value and L and M have 0% then return value in col K. However,
If K and L had values average K&L and should all three columns have values
average all three in column N.

So, At this point the Average should be 46%. If the the first column is
46% and column M is 43% then Colunm N should be 45%, and so on. At this
point the average is being calculated as if there are values in all three
columns. The formular creates an average of 15%.

I'm unsure how to nest the if statement, if that's the proper way to get to
what I want.

Any help would be great!

Thanks!

Perfect!! Thank you! Works like a charm!!


"Tom Hutchins" wrote:

Try this

=IF(SUM(K1:M1)=0,0,SUM(K1:M1)/COUNTIF(K1:M1,"<0"))

Hope this helps,

Hutch

"LWilson" wrote:

I have a worksheet that has 4 columns. The information is populated based on
months with the last column for the quarter information.

for example:

Col K Col L Col M Col N

46% 0% 0% Average of the three columns K-M

My problem is since columns L and M have no data to calculate the
percentage, I need a formula that will calculate the Average. However, if
Col K has a value and L and M have 0% then return value in col K. However,
If K and L had values average K&L and should all three columns have values
average all three in column N.

So, At this point the Average should be 46%. If the the first column is
46% and column M is 43% then Colunm N should be 45%, and so on. At this
point the average is being calculated as if there are values in all three
columns. The formular creates an average of 15%.

I'm unsure how to nest the if statement, if that's the proper way to get to
what I want.

Any help would be great!

Thanks!