totals and average

[UndwaterExcelWeaver]

In the table below I am trying to extract data based on the date and supplier. My destination sheet has a date that must match the date in column B in order for the data to be brought in. (date is Cell D1 in the destination sheet) I need to bring in the total lbs for each date in column J, and the average results for fat, protein and solids for each date. The kicker is that those results are dependent on the supplier. If the supplier is IMP, then the average results should come from columns P:R. If the supplier is not IMP, the results should come from columns M:O. I'm not sure if I need to use lookup, vlookup, or just a sumif/averageif set of formulas to make this happen. Currently I do not have more than one set of results per day, but this is set to change next week, so I need to be able to calculate and extract totals and averages, opposed to just the first set of results found for the inquired date. Please let me know what details I may be missing for you.

UF retentate received 2024.xlsx
	B	C							J			M	N	O	P	Q	R
1	SKIM UF RETENTATE CHEMISTRY ANALYSIS
2															From Composite Bottle
3	Load information for traceability								Supplier results						Nampa Lab FT
4	Receiving date	Supplier							Weight			Solids	Fat	True Protein (TP)	Solids	Fat	True Protein (TP)
5									Lb.			%	%	%	%	%	%
6	1/27/2024	IMP							49,160			16.12	0.39	14.28	17.32	0.37	14.29
7	1/28/2024	CDI							48,380			18.41	0.19	14.66	18.55	0.35	14.79
8	1/29/2024	IMP							48,180			15.05	0.36	13.39	16.30	0.35	13.36
9	1/30/2024	IMP							47,960			15.80	0.37	14.09	17.03	0.36	14.07
10	1/31/2024	IMP							48,660			15.73	0.42	13.50	17.00	0.34	13.75
11	2/1/2024	CDI							47,740			17.94	0.20	14.12	18.01	0.34	14.15
12	2/2/2024	IMP							47,360			16.17	0.07	13.66	17.28	0.34	14.02
13	2/3/2024	IMP							48,520			16.44	0.12	13.93	17.39	0.35	14.11
14	2/4/2024	IMP							49,240			16.48	0.12	14.12	17.55	0.36	14.37
15	2/5/2024	IMP							49,100			16.07	0.07	13.62	17.34	0.35	14.07
16	2/6/2024	IMP							48,460			16.34	0.06	13.80	17.14	0.27	13.95
17	2/10/2024	IMP							50,100			16.37	9.00	13.87	17.44	0.35	14.18
18	2/11/2024	IMP							48,140			16.36	0.07	14.29	17.42	0.37	14.51
19	2/12/2024	IMP							48,540			15.90	0.10	13.88
20
21
22
23
24
25
MAIN

 

[RodneyW]

Not sure how helpful this is but my first impression tells me an =AVEIFS might be the way to go. That way, in your destination tab you can have each date in column B as you're outlining. Column C would have the supplier, IMP or CDI. Maybe you would have two rows for each date, one for IMP and one for CDI.

This link may be helpful as a starting point:

AVERAGEIFS function - Microsoft Support

This article describes the formula syntax and usage of the AVERAGEIFS function, which returns the average (arithmetic mean) of all cells that meet multiple criteria.

support.microsoft.com

 

[UndwaterExcelWeaver]

Not sure how helpful this is but my first impression tells me an =AVEIFS might be the way to go. That way, in your destination tab you can have each date in column B as you're outlining. Column C would have the supplier, IMP or CDI. Maybe you would have two rows for each date, one for IMP and one for CDI.

This link may be helpful as a starting point:

AVERAGEIFS function - Microsoft Support

This article describes the formula syntax and usage of the AVERAGEIFS function, which returns the average (arithmetic mean) of all cells that meet multiple criteria.

support.microsoft.com

This is the formula I've tried for extracting the fat for one day. I tried to combine the two options into an IF statement to yield one average if the other doesn't match all criteria. It is returning a #spill error. I tried adding the specific cell ranges instead of the entire column, but the spill error persists. I'm not sure what I'm doing wrong, but probably a lot. I only have one cell to pull the result to in the destination sheet so multiple rows is not an option.

=IF('[UF retentate received 2024.xlsx]MAIN'!$C6:$C32<>"CDI",AVERAGEIFS('[UF retentate received 2024.xlsx]MAIN'!$Q6:$Q32,'[UF retentate received 2024.xlsx]MAIN'!$B6:$B32,D1,'[UF retentate received 2024.xlsx]MAIN'!$C6:$C32,'[UF retentate received 2024.xlsx]MAIN'!$C6:$C32<>"CDI"),AVERAGEIFS('[UF retentate received 2024.xlsx]MAIN'!$N6:$N32,'[UF retentate received 2024.xlsx]MAIN'!$B6:$B32,D1,'[UF retentate received 2024.xlsx]MAIN'!$C6:$C32,"CDI"))

 

[UndwaterExcelWeaver]

This is the formula I've tried for extracting the fat for one day. I tried to combine the two options into an IF statement to yield one average if the other doesn't match all criteria. It is returning a #spill error. I tried adding the specific cell ranges instead of the entire column, but the spill error persists. I'm not sure what I'm doing wrong, but probably a lot. I only have one cell to pull the result to in the destination sheet so multiple rows is not an option.

=IF('[UF retentate received 2024.xlsx]MAIN'!$C6:$C32<>"CDI",AVERAGEIFS('[UF retentate received 2024.xlsx]MAIN'!$Q6:$Q32,'[UF retentate received 2024.xlsx]MAIN'!$B6:$B32,D1,'[UF retentate received 2024.xlsx]MAIN'!$C6:$C32,'[UF retentate received 2024.xlsx]MAIN'!$C6:$C32<>"CDI"),AVERAGEIFS('[UF retentate received 2024.xlsx]MAIN'!$N6:$N32,'[UF retentate received 2024.xlsx]MAIN'!$B6:$B32,D1,'[UF retentate received 2024.xlsx]MAIN'!$C6:$C32,"CDI"))

Trying this and it returns a #div0 error

=OR(AVERAGEIFS('[UF retentate received 2024.xlsx]MAIN'!$Q:$Q,'[UF retentate received 2024.xlsx]MAIN'!$B:$B,D1,'[UF retentate received 2024.xlsx]MAIN'!$C:$C,'[UF retentate received 2024.xlsx]MAIN'!$C:$C<>"CDI"),AVERAGEIFS('[UF retentate received 2024.xlsx]MAIN'!$N:$N,'[UF retentate received 2024.xlsx]MAIN'!$B:$B,D1,'[UF retentate received 2024.xlsx]MAIN'!$C:$C,"CDI"))

 

[RodneyW]

Since you have only one cell to pull the result in, someone smarter than me may be able to help with an "IF" function. In simple terms, it would look to see if column C equals IMP, if it does, then do "this". If it doesn't equal IMP, then do "a different calculation". Eager to see the final solution. I'm pushing my abilities with my suggestions and don't know if they're on track or not.

 

[RodneyW]

I've made some progress. I've created a sheet and named one tab "Source" and another "Destination".

1. On the "Source" tab, starting with row 20, I have added a few more lines so we could have some data to average across the same date. I've added three rows for 1/27/2024, and one each for 1/28/2024 - 2/1/2024. Enter whatever data you want, just so you have something to work with.

2. In the "Destination" tab, in cell D3, I have this code =Source!C6 which pulls in the data from the date column so as to assure matching.
3. In the "Destination" tab in cell E3, I have this code =AVERAGEIFS(Source!$J$6:$J$27,Source!$C$6:$C$27,Destination!$D3)

This does indeed pull an average from ALL cells in column J where the dates match.

This gets us part way there. What is now needed is for the E3 of the Destination tab, to ALSO look in column D of the Source tab and only do the average if D = CDI. I think you will need another column in your Destination tab to do the same thing but average the number if D=IMP.

Make sense? Again, I'm stretching my ability here, just trying to steer in a direction that may help.

 

[RodneyW]

UPDATE -- In the Destination tab in cell C3 I have entered CDI. In C4 I have entered IMP. This formula

=AVERAGEIFS(Source!$J$6:$J$27,Source!$C$6:$C$27,Destination!$D3,Source!$D$6:$D$27,Destination!$C$3)

Pulls up CDI information but it's not doing an average, it's only showing the last entry I made using fictious data. It's the correct information reflecting the last CDI entry for 1/27/2024 but again, it's not the average.

Hope this helps. I need get back to other projects.