Max, BIG THANKS!! Wouldn't have done it without a UDF if you hadn't pointed
the way to the OFFSET() use. I just didn't think of trying that and had
given up barring help from someone. Definitely giving you the "Answered the
Question" award on this one.

The following formula is what I came up with based on the reality, having
conned Jane into sending me an actual sheet out of the real-world workbook.
=IF(N9=0,"",IF(ISERROR(IF(P9="","",IF(P9="NC",$N9/SUM(OFFSET(M9,ROW(M$8)-ROW(M9),,,-(COUNTIF(B9:M9,"0")-1))),N9/$N$8))),"",IF(P9="","",IF(P9="NC",$N9/SUM(OFFSET(M9,ROW(M$8)-ROW(M9),,,-(COUNTIF(B9:M9,"0")-1))),N9/$N$8))))

First IF takes care of rows that have an NC entry (or other) but the sum
total of sales is zero, the second IF takes care of potential #DIV/0 errors
and the rest does the work.

"Max" wrote:

I just hazarded a venture in the OP's repeat posting <g, where I assumed the
store codes were in col I ..

-----
Assuming store sales in cols A to F (Jan to Jun), store codes (ie: NC, Comp,
etc) indicated in col I, data from row2 down, then perhaps this ..

Put in say, J2:
=IF(I2="","",IF(I2="NC",AVERAGE(OFFSET(F2,,,,-(COUNTIF(A2:F2,"0")-1))),"Non
NC Store"))
Copy J2 down. For stores indicated as "NC" in col I, col J returns the
required averages according to your rules, otherwise the indication: "Non NC
Store" would be returned. Nothing: "" would be returned for blank cells in
col I
---

Correcting the above for the store codes indication in col P instead as you
mentioned, then perhaps this in say, Q2, copied down:
=IF(P2="","",IF(P2="NC",AVERAGE(OFFSET(F2,,,,-(COUNTIF(A2:F2,"0")-1))),"Non
NC Store"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JLatham" wrote:
For those looking to assist - found out that "COMP" and "NC" are out in
column P, entries in column P can be "COMP", "NC" or empty.

Now, if someone has a quick formula to determine where (which column) the
first non-blank entry is in a row, beginning with column B (A can be used for
something else, apparently), that would be a help, otherwise I'll probably
write a UDF to deal with that part of it.