indexing beyond what i know

[dmheller]

I am looking to do a sum of a bunch of data. I will do my best to lay out the file and what I am looking for.
I have a sheet (line 1) with every day of the year in column B
Also on that sheet, I have a 1 or a zero in column K
On a different sheet (test), I have random dates (dates something took place)

Date

CO (lb/hr)

NOX (Shutdown only) (lb/hr)

PMfilt (lb/hr)

PM10 (lb/hr)

PM2.5 (lb/hr)

SO2 (lb/hr)

VOC (lb/hr)

Acrylonitrile (lb/hr)

HCN (lb/hr)

NH3 (lb/hr)

<colgroup><col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;"> <col width="64" style="width: 48pt;" span="9"> <tbody> </tbody>

4/1/2018	0.6		1.1	3.7	3.7	0.42	0.4		0.99	0.68
5/23/2018	0.6		0.35	3.7	3.7	0.42	0.6	0.0018	0.61	0.66
7/18/2018	0.6		0.4	2.7	2.7	0.42	0.6	0.0038	0.61	0.66

<colgroup><col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;"> <col width="64" style="width: 48pt;" span="9"> <tbody> </tbody>

No for the index beyond me. On a different sheet, where the equation will go, I am looking for an index that says if column K in sheet (line 1) =1 find test before that date and multiply by the factor * .58 + another factor that is not going to change * 0.42 and do that for the entire year and sum them up. there are about 16 times K will = 1 Any help would be great.
thanks

 

[duggie33]

Hi dmheller,

I am pretty sure this can be done with formulas but I do not think I understand your data. I do not see any values =1 in your data. Could you provide an example (all made-up numbers if you like) which includes everything you describe and a result for the example that can be tested?

Doug

 

[dmheller]

Doug,
I think it can be too. I left that sheet out with the line info. It might be easy if I send you the file and write up a long one of what I am looking for. Long story short, I inherited a spreadsheet that was to much human interaction so I am fixing it. I have done tons to it this is the last step I think. Let me know if that works and if it does, I can send it Monday when I get back to the file. If not, I will try to explain more here.

Hi dmheller,

I am pretty sure this can be done with formulas but I do not think I understand your data. I do not see any values =1 in your data. Could you provide an example (all made-up numbers if you like) which includes everything you describe and a result for the example that can be tested?

Doug

 

[duggie33]

I am unsure if sending me a file would break the rules of the forum. Please check on that before we decide to go that route. If it would be a violation, maybe some made-up data including a date column, a few columns of numbers, and the column of 0/1 (false/true). I think 5-10 rows would be enough to test formulas. There could be two or three 1's in the last column. If we have something to test, I would need more detail on what values to sum and multiply by the factors.

An example for providing the data might be:
ColA, ColB, ColC, ColD, ColE
2/28/2019, 20, 30, 40, 0
3/1/2019, 22, 32, 42, 1
3/7/2019, 33, 43, 53, 0
etc.

 

[dmheller]

here is my attempt to put it in here the best I can. You can see below that on Jan 30, col K has a value of 1.

column b	Col c	Col k
1/1/19 12:00 AM	1.357214	0
1/2/19 12:00 AM	1.375169	0
1/3/19 12:00 AM	1.507671	0
1/4/19 12:00 AM	1.610174	0
1/5/19 12:00 AM	1.52188	0
1/6/19 12:00 AM	1.584772	0
1/7/19 12:00 AM	1.514785	0
1/8/19 12:00 AM	0.978128	0
1/9/19 12:00 AM	0.007764	0
1/10/19 12:00 AM	-0.00146	0
1/11/19 12:00 AM	0.007004	0
1/12/19 12:00 AM	0.645313	0
1/13/19 12:00 AM	1.568746	0
1/14/19 12:00 AM	1.568138	0
1/15/19 12:00 AM	1.480979	0
1/16/19 12:00 AM	1.354777	0
1/17/19 12:00 AM	1.374714	0
1/18/19 12:00 AM	0.553958	0
1/19/19 12:00 AM	0.54649	0
1/20/19 12:00 AM	0.933055	0
1/21/19 12:00 AM	0.736771	0
1/22/19 12:00 AM	0.840233	0
1/23/19 12:00 AM	1.864769	0
1/24/19 12:00 AM	2.680357	0
1/25/19 12:00 AM	2.936516	0
1/26/19 12:00 AM	3.025225	0
1/27/19 12:00 AM	2.63325	0
1/28/19 12:00 AM	2.631113	0
1/29/19 12:00 AM	2.657347	0
1/30/19 12:00 AM	2.140504	1
1/31/19 12:00 AM	0.340401	0

<colgroup><col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

On a different sheet, I have this

Date	CO (lb/hr)	NOX (Shutdown only) (lb/hr)	PMfilt (lb/hr)	PM10 (lb/hr)	PM2.5 (lb/hr)	SO2 (lb/hr)	VOC (lb/hr)	Acrylonitrile (lb/hr)	HCN (lb/hr)	NH3 (lb/hr)
10/12/2017	0.59		1.22	2.13	2.1	0.06	0.44		0.59	0.86
11/14/2017	0.59		1.22	2.13	2.1	0.06	0.44		0.59	0.86
02/13/2019	0.59		1.22	2.13	2.1	0.06	0.1		0.59	0.28

<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="64" style="width: 48pt;" span="10"> <tbody> </tbody>

so with those 2 bits of info, I need an equation that looks for a 1 in column K then finds the corresponding date then uses the correct emission factor so in this case, the one from 11/14/17 would be the last one. then it will match with the same pollutant on the sheet that is adding it up at.

 

[duggie33]

Can this be simplified for me? I do not know what the "emission factor" or the "same pollutant" are on your tables. Make it for a dummy like me....ColA, ColB, 10, 20, when the value in ColK=1 return the date in ColB. After than I’m kind of lost, I would use that date (1/30/2019) to arrive at 11/14/2017 and then do something else like sum values and multiply by a factor.


To find the first date (with ColK=1) from top to bottom you could use an OFFSET or INDEX something like this: =OFFSET($B$1,MATCH(1,$K$1:$K10000,0)-1,0,1,1)


To get to 11/14/2017 you might use another MATCH function to return the row number it is found (closest date less than or equal to k=1 date).