Excel Average Function

[bucky6225]

I have rows of data I'm trying to average, but am having trouble. I think there could be two problems.

1. Each cell is a vlookup function getting the data from elsewhere.
2. Some of the cells have no datapoint and instead display "#N/A".

I have data points in K38:N38, but O38 and P38 both show "#N/A". If I want to average K38:P38 how do I get a number back?

Thanks

 

[MrKowz]

you have to add in an if statement for the vlookup like

=IF(iserror(*vlookup function*),"",*vlookup function*)

That way, if it cannot find a value, instead of showing N/A for the vlookup, it will return a blank

 

[DonkeyOte]

correcting the vlookup is certainly a good option -- alternative would be the below (as an array)

=SUM(IF(ISNUMBER(K28:P28),K28:P28))/COUNT(K28:P28)

(commit to array using SHIFT+CTRL+ENTER -- formula will become encase in {...})

 

[bucky6225]

Hey Guys,

Thanks for the replies, but I made a mistake in describing the problem. It's actually not a vlookup, but a rank function that I am using. Don't know if this makes a difference.

In cell K38 I have the following formula: =RANK(K2,K$2:K$32, 1)
In cells L38, =RANK(L2,L$2:L$32, 1)
and so on.

In cells N38 and O38 I have the same formulas but they are returning "#N/A" because the cells in columns they are trying to rank are blank. What formula should I use so there won't be a "#N/A" but a blank?

Thanks again.

 

[MrKowz]

use the same function i listed above, but instead put in the rank function where I have the *vlookup function* part.

The function basically says:

If the result of your function is an error, then it will return a blank spot, otherwise it will return the result of your function

 

[bucky6225]

worked great. Thanks!