Previous Set Amount of Cells With Data as Range

**ARayburn** · 05-13-2013, 10:21 AM

I have a spreadsheet in which there are 52 columns, one for each week of the year. As the year goes on the columns fill with data. At the end of the 52 columns there are several columns of functions. One of which I need to only use the previous 6 columns with data for that row as a range. Is there a function that will use the previous 6 cells with data as a range within other functions?

**Mazzaropi** · 05-13-2013, 12:11 PM

ARayburn, Good Morning.

What you need is to determine which range will be used as your working range. I suppose it.
Take a look at the example attached.

Please, tell if it worked for you.

Have a nice day.

Belo Horizonte, MG - Brazil

**ARayburn** · 05-13-2013, 12:47 PM

Exactly! You are on the right track, now for example if I was an average() of those 6 cells with data, how would I make that function, or put it into any range for a function? Thank for all your help.

**Mazzaropi** · 05-13-2013, 01:27 PM

ARayburn, now that you have the formula to find the desired range, you need to transform it an a valid address to Excel.

Use the INDIRECT function to do this.

Take a look at a new example attached.

I hope that it helps.

Have a nice day.

Belo Horizonte, MG - Brazil

**ARayburn** · 05-13-2013, 02:51 PM

You are great! Thank you, it is working for simple functions however this is what I am trying to do. I need to calculate a rate of sale excluding any major deviations. This is where it gets a little complicated. I basically:
1. Calculated the STDEV for the 6 cells.
2. Calculated the AVERAGE of those 6 cells.
3. Did a SUMIF function stating to add those 6 cells that fall within the lower limit of the standard deviation and the upper limite. I.E. a data set has a STDEV of 75 and an AVERAGE of 900. I am only calculating cells with numbers 825<x<975.
4. After adding those numbers I do a COUNTIF function dividing #3 by the number of cells that fall within the same category.

Here is what I have:
=(SUMIFS(AR3:AW3,AR3:AW3,"<"&(AVERAGE(AR3:AW3)+STDEV(AR3:AW3)),AR3:AW3,">"&(AVERAGE(AR3:AW3)-STDEV(AR3:AW3)))/COUNTIFS(AR3:AW3,"<"&AVERAGE(AR3:AW3)+STDEV(AR3:AW3),AR3:AW3,">"&AVERAGE(AR3:AW3)-STDEV(AR3:AW3)))

I know it is pretty complicated but this would be great if I could figure it out. I basically need to replace the red ranges with the formulas you have come up with, however when I do so it won't work.

**Mazzaropi** · 05-13-2013, 10:04 PM

ARayburn, Good evening.

As I told you before, you need just to use the INDIRECT function to make things work.

As the formula isn´t short, I suggest you to use a column as an auxiliar.
Suppose you use the BH column as this role.

Example:
You are using the formula at line 3

Then at BH3 you put:
=ADDRESS(ROW(),(MATCH(999999999,$A$3:$AZ$3)-5),2,1)&":"ADDRESS(ROW(),(MATCH(999999999,$A$3:$AZ$3)),2,1)

IF the last column filled is AW then BH3 will show: AR$3:AW$3
To make this range to transform in an Address use the INDIRECT function.

INDIRECT(BH3)

"...Here is what I have:
=(SUMIFS(AR3:AW3,AR3:AW3,"<"&(AVERAGE(AR3:AW3)+STDEV(AR3:AW3)),AR3:AW3,">"&(AVERAGE(AR3:AW3)-STDEV(AR3:AW3)))/COUNTIFS(AR3:AW3,"<"&AVERAGE(AR3:AW3)+STDEV(AR3:AW3),AR3:AW3,">"&AVERAGE(AR3:AW3)-STDEV(AR3:AW3)))..."

As you need to use your BIG formula with the same range inside it, do:
Substitute the range AR3:AW3 in your formula by INDIRECT(BH3)

Put at BA3:
=(SUMIFS(INDIRECT(BH3),INDIRECT(BH3),"<"&(AVERAGE(INDIRECT(BH3))+STDEV(INDIRECT(BH3))),INDIRECT(BH3),">"&(AVERAGE(INDIRECT(BH3))-STDEV(INDIRECT(BH3))))/COUNTIFS(INDIRECT(BH3),"<"&AVERAGE(INDIRECT(BH3))+STDEV(INDIRECT(BH3)),INDIRECT(BH3),">"&AVERAGE(INDIRECT(BH3))-STDEV(INDIRECT(BH3))))

It´s really a very big formula.

Please try to do this.

Tell me if it worked for you now.

Best wishes from Brazil.

**ARayburn** · 05-16-2013, 02:18 PM

Thank you, I made a spreadsheet just as it shows on my own just deleted all of the other unnecessary data. I entered the formulas you said however I am getting an error in the auxiliary cell. Could you please advise.

Prev6.xlsx

**Mazzaropi** · 05-17-2013, 05:33 PM

ARayburn, Good afternoon.

Well there are two questions envolved.

1°) I apologize. It was a typo

Before:
Then at BH3 you put:
=ADDRESS(ROW(),(MATCH(999999999,$A$3:$AZ$3)-5),2,1)&":"ADDRESS(ROW(),(MATCH(999999999,$A$3:$AZ$3)),2,1)

Now:
Then at BH3 you put:
=ADDRESS(ROW(),(MATCH(999999999,$A$3:$AZ$3)-5),2,1)&":"&ADDRESS(ROW(),(MATCH(999999999,$A$3:$AZ$3)),2,1)

2°) You changed the layout.

As you can see, everything at Excel is RELATIVE unless it be pointed as ABSOLUTE.

At my first example I started data at column A and I finished at column AZ (52 columns at all).
The formula considered these positions.

=ADDRESS(ROW(),(MATCH(999999999,$A$3:$AZ$3)-5),2,1)&":"&ADDRESS(ROW(),(MATCH(999999999,$A$3:$AZ$3)),2,1)

At your example, perhaps your real situation, yours data starts at column E and they finish at column BE (53 columns)

When adapting the formula you must consider this offset.
As you moved your data foward to 4 cells you must consider this and ADD 4 to the final range and MINUS 4 from the initial range.

The formula adapted to your range must looks like this one:
=ADDRESS(ROW(),(MATCH(999999999,$E$3:$BE$3)-1),2,1)&":"&ADDRESS(ROW(),(MATCH(999999999,$E$3:$BE$3)+4),2,1)

Unfortunately as I have an older Excel version(2003)than yours, I can´t alter your workbook and give it you back.
But as you can see the mistakes were pointed.

Tell us if it´s what you desire.

I hope it helps.

Have a nice day.

Best wishes from Brazil!

Please, forgive my mistakes in English.