Hello,

I'm currently trying to work out how I can make a formula without using an IF in an IF in an IF in an IF etc.....

I am trying to work out the average sales data on a product by totalling the last 6 months /6.

Eg 1:
Jan Feb Mar Apr May Jun
500 423 586 951 142 214 Ttl/6 = 469.34 (Average monthly sales)

However, it falls down if the item is new and has only been selling for the last 6 months.

Eg 2:
Jan Feb Mar Apr May Jun
000 000 000 452 426 512 Ttl/6 = 231.67 (Average monthly sales)

In example 2 - I need the formula to automatically exclude the 0's and divide by how many values >0 are present. So far, I ahve this:

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

....but I can only use this in a range... the problem is, my cells are seperated and not in a range - so I would need the equivalent of:

=(SUMIF(A1,A5,A9,A11, ">0")) / (COUNTIF(A1,A5,A9,A11, ">0"))

....any ideas how can I do this?

Thanks!