Question Locked

If Statements

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am working on a spreadsheet in Excel. I would like to see if I can get this to do the following, if k23:k27>0 then I need these values to be avareaged without including the 0 value.

Also, I need some help with the following, similar to the previious one: If k14>=167.5, k14<=228.5 then I would need the value to be averaged. The data in this sequence I'm looking at needs to be between 167.5 - 228.5 and can include these values.

Thank you for your time and any information you can give. I don't even know if this can be done.

Thank you again.

Kim Anderson
Laboratory Supervisor

3 replies

by bizzo

Are any of the cells going to be negative numbers? If so do you want those adding also?

If you only want to add and average the positive non-negative numbers, then this will work:

=SUMIF(A1:A20,">0")/COUNTIF(A1:A20,">0")

Adds all the numbers over 0, then divide by the total number of entries over 0. Assuming that's the average you require.

I need a longer think about part 2.

by bizzo

=(SUMIF(A1:A20,">=167.5")-SUMIF(A1:A20,">228.5"))/(COUNTIF(A1:A20,">=167.5")-COUNTIF(A1:A20,">228.5"))

Put your own range in there, I was just testing.

This discussion is closed to new replies.