Another Excel Question

[Jason Betts]

Hi, I want to know if it is possible to tell a formula to ignore cells with values of 0 or an error. 

 

For example, I want to average all cells in a column but I want to ignore cells with 0, or further down the spreadsheet I want to ignore any cells with a "divide by 0" error since those cells rely on earlier cells which are currently 0. I want my formula ="$"&ROUND(AVERAGE(M2:M201),2) to ignore invalid cells in the M2:M201 list. 

 

 

Thanks, Jason. 

[ARikozuM]

Try setting a condition to those cells.

[ARikozuM]

Figure it out yet?

[Jason Betts]

1 hour ago, ARikozuM said:

Figure it out yet?

No, I don't understand what you mean by a condition. 

[ARikozuM]

1 minute ago, Jason Betts said:

No, I don't understand what you mean by a condition. 

I'm due in surgery in a bit, but if you go to "Conditional Formatting" and select (I think) the 2nd option it'll let you select errors and zeroes.

[Jason Betts]

Just now, ARikozuM said:

I'm due in surgery in a bit, but if you go to "Conditional Formatting" and select (I think) the 2nd option it'll let you select errors and zeroes.

Why are you on here? You should be relaxing while you can before a surgery! Good luck with your surgery, and thanks for the help. I'll try that out.

[ARikozuM]

Just now, Jason Betts said:

Why are you on here? You should be relaxing while you can before a surgery! Good luck with your surgery, and thanks for the help. I'll try that out.

I'll be supervising and assisting a new surgeon in his first non-school hysterectomy. Relaxing is for extroverts!

[Jason Betts]

Just now, ARikozuM said:

I'll be supervising and assisting a new surgeon in his first non-school hysterectomy. Relaxing is for extroverts!

I see. Good point. 

 

Using the tool you told me about I can write in " " to make a 0 cell appear empty until the math inside gets a value higher than 0, but I'm not sure if that will affect an average formula. I would think the formula would skip any cells that don't contain a value?

[Jason Betts]

2 minutes ago, ARikozuM said:

I'll be supervising and assisting a new surgeon in his first non-school hysterectomy. Relaxing is for extroverts!

Just Googled " hysterectomy ". Sounds serous. 

[ARikozuM]

Just now, Jason Betts said:

I see. Good point. 

 

Using the tool you told me about I can write in " " to make a 0 cell appear empty until the math inside gets a value higher than 0, but I'm not sure if that will affect an average formula. I would think the formula would skip any cells that don't contain a value?

You can also set them for errors.

[ARikozuM]

Just now, Jason Betts said:

Just Googled " hysterectomy ". Sounds serous. 

It's a normal spay procedure.

[Jason Betts]

Just now, ARikozuM said:

It's a normal spay procedure.

On a human?

[ARikozuM]

Just now, Jason Betts said:

On a human?

On dogs, cats, etc. In this case it's a dog. I like to train my surgeons to be able to spay and neuter only through A&P knowledge since the schools teach you to look into the body.

[ARikozuM]

Let me know how it goes. I'm due in 1 minute.