ASAP - need help with formula!

[Jane]

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

 

[Don Guillett]

Why is your need more important than any other's. ASAP sounds like we are
your employees.

 

[Dave F]

Ha.

 

[Epinn]

A good link for all posters.

How To Ask Questions The Smart Way

I am learning netiquette everyday. Don is right although I have a feeling that you might have posted before and didn't get an answer.

Trying to help you get what you want.

Epinn

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

 

[Jane]

I am sorry... I in no way wanted it to sound like that at all!

I have a project due tomorrow and needed help as soon as possible. I posted
the question on Friday an no one asnwered so I hoped to get help as soon as I
could.

This group has helped me so many times an woul never want to offend them.

Whenever I post, I have always tried to be specific but was not sure where
this question fell.

 

[Max]

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

 

[Dave F]

Just remember it always pays to think how other people will interpret your
written words. Since we can't see you and can't hear you speak all we see
are words.

Does Max's formula help?

Dave

 

[Epinn]

Dave,

I looked at the subject/heading of Jane's original post and I was not surprised why she didn't get as much support as she might have had otherwise. I totally agree that choice of words is important and I do my best. If I fail, I hope others can forgive me. With the collaboration of Max and Mr Latham (under the original thread), I am not surprised if Jane is happy now.

I was trying to help and I hope Jane didn't take it the wrong way.

On a different subject......

Previously we were wondering what the OP wanted regarding tariff codes (1234.56.7890). He/she responded. I wonder if you saw the post. Something else evolved and I would appreciate your insight if you don't mind.

Epinn

Just remember it always pays to think how other people will interpret your
written words. Since we can't see you and can't hear you speak all we see
are words.

Does Max's formula help?

Dave