Remove first cell in a range

[m5edward]

Hi All,

I work for a retailer and would like compute the average weekly sales for each one of our stores.

That said, I want to eliminate the first week of sales from the average because it is always much lower and affects the overall average.

What is the best way to accomplish this?

Below is an example of what I want to do, but in an automated fashion (we have 100s of stores). Column K is just the average of column B to J, while column L is the average without the first week of sales (desired result).

Thanks,

Mark

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=AVERAGEIF(B2:J2,">"&MIN(B2:J2))

 

[m5edward]

Hi Fluff,

Thank you very much for your suggestion.

It appears to be looking for a lowest sales number in the range, and removing that number from the average.

This produces the desired result for most stores, but not all.

Do you have any other suggestions?

Thanks,

Mark

 

[Fluff]

In that case please post some sample data where it is not working.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also you may have missed this

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[m5edward]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF
1	Store Number	Store Banner	Store Name	Store Ownership Type	Store Centre Type	Store Placement Within Centre	Store Size	2021-01-09	2021-01-16	2021-01-23	2021-01-30	2021-02-06	2021-02-13	2021-02-20	2021-02-27	2021-03-06	2021-03-13	2021-03-20	2021-03-27	2021-04-03	2021-04-10	2021-04-17	2021-04-24	2021-05-01	2021-05-08	2021-05-15	2021-05-22	2021-05-29	2021-06-05	2021-06-12	2021-06-19	2021-06-26	2021-07-03	2021-07-10	2021-07-17	2021-07-24	2021-07-31	2021-08-07	2021-08-14	2021-08-21	2021-08-28	2021-09-04	2021-09-11	2021-09-18	2021-09-25	2021-10-02	2021-10-09	2021-10-16	2021-10-23	2021-10-30	2021-11-06	2021-11-13	2021-11-20	2021-11-27	2021-12-04	Total 2021	2021 Avg Weekly Sales	Desired Result
2	B1	Banner 1	Calgary	Corporate	Strip Centre	TBD	1200				$ 10,759	$ 20,343	$ 24,536	$ 27,782	$ 30,775	$ 30,327	$ 31,561	$ 32,980	$ 32,182	$ 37,365	$ 36,595	$ 36,461	$ 37,823	$ 36,809	$ 39,068	$ 35,960	$ 37,341	$ 40,404	$ 38,405	$ 33,064	$ 39,171	$ 36,018	$ 38,226	$ 33,715	$ 36,694	$ 41,253	$ 39,882	$ 34,400	$ 43,955	$ 39,302	$ 37,653	$ 37,200	$ 43,773	$ 39,516	$ 39,491	$ 39,623	$ 38,437	$ 35,542	$ 36,843	$ 33,374	$ 36,792	$ 37,856	$ 34,538	$ 35,030	$ 36,631	$ 1,595,455	$ 35,455	$ 36,016
3	B2	Banner 1	Edmonton	Franchise	Strip Centre	Inline	400				$ 4,015	$ 8,303	$ 10,458	$ 12,742	$ 12,683	$ 15,465	$ 21,048	$ 25,422	$ 28,786	$ 33,786	$ 26,577	$ 29,109	$ 29,071	$ 30,012	$ 34,747	$ 33,440	$ 34,469	$ 34,985	$ 38,259	$ 41,544	$ 42,856	$ 45,702	$ 44,551	$ 45,749	$ 42,868	$ 46,824	$ 47,046	$ 43,913	$ 49,288	$ 47,667	$ 50,905	$ 49,255	$ 48,725	$ 49,803	$ 51,718	$ 51,786	$ 49,892	$ 49,342	$ 47,992	$ 53,846	$ 46,730	$ 47,949	$ 47,456	$ 46,118	$ 42,828	$ 1,695,731	$ 37,683	$ 38,448
4	B3	Banner 1	Toronto	Franchise	Streetfront	Endcap	1450	$ 19,245	$ 1,000	$ 25,755	$ 30,355	$ 34,349	$ 34,878	$ 40,949	$ 43,898	$ 48,308	$ 58,201	$ 62,125	$ 60,437	$ 59,697	$ 58,930	$ 54,774	$ 58,276	$ 58,407	$ 53,283	$ 56,767	$ 57,954	$ 60,848	$ 59,543	$ 62,098	$ 67,025	$ 72,264	$ 72,868	$ 72,986	$ 68,670	$ 74,401	$ 76,394	$ 73,695	$ 76,682	$ 73,894	$ 73,579	$ 76,710	$ 76,652	$ 81,210	$ 78,863	$ 79,118	$ 79,189	$ 79,172	$ 78,484	$ 77,736	$ 76,791	$ 75,673	$ 78,121	$ 73,577	$ 71,767	$ 2,985,597	$ 62,200	$ 63,114
Sheet1

Cell Formulas
Range	Formula
BD2:BD4	BD2	=SUM(H2:BC2)
BE2:BE4	BE2	=AVERAGE(H2:BC2)
BF2:BF3	BF2	=AVERAGE(L2:BC2)
BF4	BF4	=AVERAGE(I4:BC4)

 

[m5edward]

Hi Fluff,

Thanks for introducing me to this tool.

The first two rows of data are simple. The average starts at 2021-02-06.

The third row is where it gets complicated. The 'averageif' formula you provided me will remove 2021-01-16 from the average when it should be 2021-01-09 that gets removed.

Is there a way to tell Excel "I want the first non-blank cell in the range to be removed from the average"?

Thanks,

Mark

 

[Fluff]

If have still not said what version of Excel you are using.

 

[m5edward]

If have still not said what version of Excel you are using.

Sorry about that. I am using Microsoft 365.

 

[Fluff]

I am using Microsoft 365.

Thanks for that, please don't forget to update you account details to show that.

how about

=AVERAGE(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,H2:BC2)&"</m></k>","//m[position()>1]"))