MVP HELP NEEDED !

[Guest]

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


182.83 804.25 328.56 347.99 321.95 328.45 3548.61 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

 

[Guest]

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.

 

[Guest]

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"))

 

[Guest]

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]

Thanks, Jerry !
Cheers

 

[Bernd]

Hello,

A non-volatile solution:
=(P2="NC")*AVERAGE(INDEX(A2:F2,8-COUNTIF(A2:F2,">0")):F2)

Regards,
Bernd