Hazarding a venture here ..

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jane" wrote:
Data:
A B C D E F G H
Season
Ratio
Jan. Feb. Mar. Apr. May Jun avg. to Ssn avg.
182.83 804.25 423.68 263.66 388.32 319.83 2944.66 x
210.09 427.43 380.02 1941.79 x
0 0 518.18 411.43 415.57 251.04 3343.16 x

Facts:
- Columns A - F = store sales
- Column G row 1 = COMPANY average of store's averages below
- Columns G row 2-4 = STORE averages of months in columns A - F
- Each store is coded "NC" and they are among other stores that are coded
"COMP" and not shown here.
- Column H needs to = ratio of STORE'S sales average to COMPANY average -
with a catch


I need a formula that says
if the store is coded NC, calculate the ratio to COMPANY average - teh
COMPANY average must be an average that 1) sums the Company averages for
months in which the STORE's monthly data is 0 and 2) starts at the 2nd month
in which there is STORE data.

ie
For store 1, the Company average would include Feb - Jun
Fro store 2, the Company average would include May - Jun
For store 3, the Company average would include Apr - Jun

thank you in advance! Jane