Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MVP HELP NEEDED !
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MVP HELP NEEDED !
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. "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 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MVP HELP NEEDED !
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MVP HELP NEEDED !
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MVP HELP NEEDED !
Thanks, Jerry !
Cheers -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- On Feb 6, 10:52 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: 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)-RO*W(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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MVP HELP NEEDED !
Hello,
A non-volatile solution: =(P2="NC")*AVERAGE(INDEX(A2:F2,8-COUNTIF(A2:F2,"0")):F2) Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sports Comp Ladder tabulating help needed | Excel Worksheet Functions | |||
Calculate needed gallons of water, based on weight of person | Excel Worksheet Functions | |||
A rather difficult statistical search formula needed (Part 2) | Excel Worksheet Functions | |||
Macro help needed | Excel Discussion (Misc queries) | |||
if statement" desperate insight is needed!!!!!! IF,Count, DCOUNT AHHHGGGGGGG! | Excel Worksheet Functions |