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!
Bookmarks