I am wondering if there is a way to auto-generate the cells that I have highlighted in pink. Currently we enter the data we collect in the field (Highlighted in purple) and have to manually copy and paste the values we are after to the fields in pink.
The yellow highlighted box has instructions on how the fields are generated.
Any info if you think it can be done or if you need more details please let me know!
Thanks
Tyler
There are a lot data on that sheet and I am not familiar with those items.
I think this could be done, but need the know the criteria (68F RANGE).
Maybe you can delete all irrelevant data, so it will be easier to understand.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
I have attached a simplified worksheet. On sheet one 1 have placed the blank worksheet. On sheet 2 I placed an example to help explain what we are after. The columns Temp (Temperature (F)) and DO (Dissolved Oxygen) are the columns where we will enter the data we collect on our natural lakes.
For the temp ranges we are looking for the Depth and Temp that include the range we are after.
Example:
Raw Data:
Depth Temp D0
16 75.8 8.40
18 74.7 8.00
20 69.8 4.50
22 61.9 1.00
24 57.1 1.00
73F RANGE
Depth ft Temp F
18 74.7
20 69.8
We take the Depth and temp cells that fall above and below the 73F Range we are after. This is the same for all the of the Temp Ranges. The 86F we sometimes do not have data that meets that criteria. If that is the case we take top 2.
Example:
Raw Data:
Depth ft Temp F DO ppm
0 77.0 9.00
2 77.0 9.00
4 77.0 9.00
5
6 77.0 8.90
86F RANGE
Depth ft Temp F
0 77.0
2 77.0
Same goes for the DO5 (Dissolved Oxygen at 5.00ppm). We want the cells that fall above and below it.
Let me know if you have any further questions!
Thanks for the help!!
Column A, B and C are inputcells?
Where do you need a calculation, and what is the result of the calculation
What are the conditions for the calculations?
This solution addresses the depth and temperature ranges, the same general principals should apply to the depth and dissolved oxygen also.
The array entered formula* for D5,D9 etc. is:Formula:
=INDEX(A$1:A$34,SMALL(IFERROR(ROW(A$1:A$34)/(B$1:B$34=E5),FALSE),1))
The array entered formula* for D6,D10 etc. is:Formula:=IF(INDEX(A$1:A$34,SMALL(IFERROR(ROW(A$1:A$34)/(B$1:B$34=E6),FALSE),1))=D5,INDEX(A$1:A$34,SMALL(IFERROR(ROW(A$1:A$34)/(B$1:B$34=E6),FALSE),2)),INDEX(A$1:A$34,SMALL(IFERROR(ROW(A$1:A$34)/(B$1:B$34=E6),FALSE),1)))
The array entered formula* for E5, E9 etc. is:Formula:=IFERROR(SMALL(IFERROR((B$4:B$34)/(B$4:B$34>=VALUE(LEFT(D3,2))),FALSE),1),LARGE(IFERROR((B$4:B$34)/(B$4:B$34<=VALUE(LEFT(D3,2))),FALSE),1))
The array entered formula* for E6, E10 etc. is:Formula:=IF(LARGE(IFERROR((B$4:B$34)/(B$4:B$34<=VALUE(LEFT(D3,2))),FALSE),1)=E5,LARGE(IFERROR((B$4:B$34)/(B$4:B$34<=VALUE(LEFT(D3,2))),FALSE),2),LARGE(IFERROR((B$4:B$34)/(B$4:B$34<=VALUE(LEFT(D3,2))),FALSE),1))
*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Note: In the data the temperature at 8 ft was 77.2 F
Note: this solution assumes the version is 2007, as listed, and will open .xlsx file
Let me know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
The depth and temperature ranges work great!! I do have a couple questions though:
1) We deal with lakes that are around 120 feet deep. Can I just drop/expand the formula down to include all depths?
2) You said the "same general principals should apply to the depth and dissolved oxygen". How do I go about doing that?
Thanks again for all your help!!
Tyler
1) continuing with the current numbering system for depth in feet 120 should be in A75 so the formulas need to be modified to account for that such as the one in E5 which would read:Formula:=IFERROR(SMALL(IFERROR((B$4:B$75)/(B$4:B$75>=VALUE(LEFT(D3,2))),FALSE),1),LARGE(IFERROR((B$4:B$75)/(B$4:B$75<=VALUE(LEFT(D3,2))),FALSE),1))
2) By changing the column in which we are looking for the value to C and the part of the cell from where we are extracting the value to the third character. An example is the *array entered formula which could populate E21 with 8.00 :*see note in post #5Formula:=IFERROR(SMALL(IFERROR((C$4:C$75)/(C$4:C$75>=VALUE(MID(D19,3,1))),FALSE),1),LARGE(IFERROR((C$4:C$75)/(C$4:C$75<=VALUE(MID(D19,3,1))),FALSE),1))
Let me know if you have any questions.
Edit: attached is the worksheet with the rest of the formulas.
Last edited by JeteMc; 08-30-2016 at 09:51 AM. Reason: Added file
It works great! Thanks for all of you help!
Tyler
You're Welcome, thank you for the feedback and for marking the thread 'Solved'. I hope that you have a blessed day.
i am really needing help i am super stuck with this sheet i cant get the drop-down to work on the item number for the invoice sheet can anyone please help me !!!
i've fixed it 3 times and it still open the drop-down for the item number and then i need to do vlookup foe the item description and unite of measure
if anyone could help me i would greatly appreciate it..
@ goodhatlate1234, You'll need to open a new thread in the Excel Formulas & Functions forum.
Oh k well I think that's what I tried already could you add what your talking about to the attachment so I can see how I should do it ?
I have it all set with offset and countif for The formula and it all works on the category drop down but I need only the items numbers that relate to that category name to show in the drop down from the data tab becs the data tab is going to be a on going list of categories and item numbers as we keep building the database.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Never use Merged Cells in Excel
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks