indexing beyond what i know

D

dmheller

Board Regular
Joined
May 26, 2017
Messages
156
Office Version
  1. 365
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)
[TABLE="width: 534"]
<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>[TR]
[TD="width: 78, bgcolor: silver"]Date[/TD]
[TD="width: 57, bgcolor: silver"]CO
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]NOX (Shutdown only)
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]PMfilt
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]PM10
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]PM2.5
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]SO2
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]VOC
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]Acrylonitrile
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]HCN
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]NH3
 (lb/hr)[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 534"]
<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>[TR]
[TD="width: 78, bgcolor: transparent, align: right"]4/1/2018[/TD]
[TD="width: 57, bgcolor: transparent, align: right"]0.6[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1.1[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]3.7[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]3.7[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0.42[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.4[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.99[/TD]
[TD="width: 64, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.68[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5/23/2018[/TD]
[TD="bgcolor: transparent, align: right"]0.6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.35[/TD]
[TD="bgcolor: red, align: right"]3.7[/TD]
[TD="bgcolor: red, align: right"]3.7[/TD]
[TD="bgcolor: transparent, align: right"]0.42[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.0018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.61[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.66
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7/18/2018[/TD]
[TD="bgcolor: transparent, align: right"]0.6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]0.4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]2.7[/TD]
[TD="bgcolor: transparent, align: right"]2.7[/TD]
[TD="bgcolor: transparent, align: right"]0.42[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.6[/TD]
[TD="bgcolor: transparent, align: right"]0.0038[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.61[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.66
[/TD]
[/TR]
</tbody>[/TABLE]
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
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
 
Upvote 0
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.
duggie33 said:
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
Click to expand...
 
Upvote 0
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.
 
Upvote 0
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.
[TABLE="width: 182"]
<colgroup><col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="width: 114, bgcolor: transparent"]column b[/TD]
[TD="width: 64, bgcolor: transparent"]Col c[/TD]
[TD="width: 64, bgcolor: transparent"]Col k
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/1/19 12:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]1.357214
[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/2/19 12:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]1.375169[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/3/19 12:00 AM
[/TD]
[TD="bgcolor: transparent, align: right"]1.507671[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/4/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]1.610174[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/5/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]1.52188[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/6/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]1.584772[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/7/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]1.514785[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/8/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]0.978128[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/9/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]0.007764[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/10/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]-0.00146[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/11/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]0.007004[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/12/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]0.645313[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/13/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]1.568746[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/14/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]1.568138[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/15/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]1.480979[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/16/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]1.354777[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/17/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]1.374714[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/18/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]0.553958[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/19/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]0.54649[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/20/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]0.933055[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/21/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]0.736771[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/22/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]0.840233[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/23/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]1.864769[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/24/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]2.680357[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/25/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]2.936516[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/26/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]3.025225[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/27/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]2.63325[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/28/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]2.631113[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/29/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]2.657347[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/30/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]2.140504[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/31/19 12:00 AM[/TD]
[TD="bgcolor: transparent, align: right"]0.340401[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

On a different sheet, I have this
[TABLE="width: 536"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="64" style="width: 48pt;" span="10"> <tbody>[TR]
[TD="width: 75, bgcolor: silver"]Date[/TD]
[TD="width: 64, bgcolor: silver"]CO
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]NOX (Shutdown only)
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]PMfilt
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]PM10
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]PM2.5
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]SO2
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]VOC
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]Acrylonitrile
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]HCN
(lb/hr)[/TD]
[TD="width: 64, bgcolor: silver"]NH3
 (lb/hr)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10/12/2017[/TD]
[TD="bgcolor: transparent, align: right"]0.59[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1.22[/TD]
[TD="bgcolor: transparent, align: right"]2.13[/TD]
[TD="bgcolor: transparent, align: right"]2.1[/TD]
[TD="bgcolor: transparent, align: right"]0.06[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.44[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.59[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]0.86[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11/14/2017[/TD]
[TD="bgcolor: transparent, align: right"]0.59[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]1.22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]2.13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , align: right"]2.1[/TD]
[TD="bgcolor: transparent, align: right"]0.06[/TD]
[TD="bgcolor: transparent, align: right"]0.44[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]0.59[/TD]
[TD="bgcolor: transparent, align: right"]0.86[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]02/13/2019
[/TD]
[TD="bgcolor: transparent, align: right"]0.59
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1.22[/TD]
[TD="bgcolor: transparent, align: right"]2.13[/TD]
[TD="bgcolor: transparent, align: right"]2.1[/TD]
[TD="bgcolor: transparent, align: right"]0.06[/TD]
[TD="bgcolor: transparent, align: right"]0.1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]0.59[/TD]
[TD="bgcolor: transparent, align: right"]0.28[/TD]
[/TR]
</tbody>[/TABLE]
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.
 
Upvote 0
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).
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Conditional Formating Required for changing Dates based on 2 different cell dates
Replies
1
Views
793
Toadstool
Toadstool
J
  • Question Question
pulling data from one chart to another
Replies
1
Views
814
jb00976
J
A
  • Question Question
Add Sequence numbers Based on Dates
Replies
1
Views
3K
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Question Question
Multiple Condition IF/OR Statement with TEXT
Replies
1
Views
993
AlanY
A
H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff

Forum statistics

Threads
1,224,747
Messages
6,180,719
Members
452,995
Latest member
isldboy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top