Hello Everyone,
I would like to know if it is possible to accomplish the following:
I have built a formula view template with approximately 21 columns that I want to add together and average (sounds simple emough right?).

Here's the problem:
I am using the #COLRANK function for each of the columns I want to include in the total but I only want to include these columns if the value of the column is greater than zero.

For example:
#COLRANK(4)+#COLRANK(5)+#COLRANK(6)+#COLRANK(7)
where the values of column 4=99, column 5=102, column 6=0 and column 7=91.
What I want to do is add up only the columns that have a value greater than zero (3 columns) and divide by that number to attain my average.
My template is based on past performances so each line may or may not contain a column or columns that have a value of zero so the calculation would be different for each row.
I hope this makes sense enough for someone to tell me if this can be done and if so how to go about accomplishing this.
Sorry for the long post but I have been banging my head against a brick wall for months trying to figure this out. In the meantime I have been doing this by hand. Any help would be greatly appreciated.

Patrick

Hi,

Haven't verified this in an FV but something like this should work:

You might put these into separate columns to test and then combine them and so your aware this doesn't test for divide by Zero errors:


( #COLRANK(4) * #GREATER(#COLVAL(4), 0) +
#COLRANK(5) * #GREATER(#COLVAL(5), 0) +
#COLRANK(6) * #GREATER(#COLVAL(6), 0) +
#COLRANK(7) * #GREATER(#COLVAL(7), 0) )

/

(1 * #GREATER(#COLVAL(4), 0) +
1 * #GREATER(#COLVAL(5), 0) +
1 * #GREATER(#COLVAL(6), 0) +
1 * #GREATER(#COLVAL(7), 0))

The key is using the #GREATER function to test for values greater than Zero.

Hope that helps,
HP

Thanks HP. I will give this a shot. I appreciate your help.

Patrick

Holding Pattern,

YOU ARE DA MAN!!! Your formula works like a charm. You have solved the problem that has plagued me for a long time. THANK YOU VERY MUCH!!!

Patrick