LinkBack URL
About LinkBacks
Temps.JPGTemps.xlsx
In the image/spreadsheet, I am wanting to be able to return Jan.05 and 2012. Once I have the formula for one, I can manage the second.
Thanks
Temps.JPGTemps.xlsx
In the image/spreadsheet, I am wanting to be able to return Jan.05 and 2012. Once I have the formula for one, I can manage the second.
Thanks
post deleted, wrong comment
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Try this ARRAY formula...
=INDEX($I$2:$I$32,MATCH(MAX(IF(Day_Weather>=Entry_Date,IF(Day_Weather<=Exit_Date,Temp_RecH))),Temp_RecH,0))
...confirmed 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. Press F2 on that cell and try again.
Temps AW 17-2-15.xlsx
This should work. Let me know if you need help
Adam
=EDATE(MIN(IF(Day_Weather>=Entry_Date, IF(Day_Weather<=Exit_Date,IF(Temp_RecH=LEFT(B6,LEN(B6)-3)+0,Day_Weather)))),(MIN(IF(Day_Weather>=Entry_Date, IF(Day_Weather<=Exit_Date,IF(Temp_RecH=LEFT(B6,LEN(B6)-3)+0,$I$2:$I$32))))-YEAR(MIN(IF(Day_Weather>=Entry_Date, IF(Day_Weather<=Exit_Date,IF(Temp_RecH=LEFT(B6,LEN(B6)-3)+0,Day_Weather))))))*12)
Try this array formula
i think this is very long
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Hi Smit.
I think your data might do well using a Pivot Table. I've constructed a bunch of fake data and done a Pivot with it. See if Pivots make your work easier. Time to learn Pivots?
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Not really sure in what format you would like it to be presented but here is my take on it
array formula
=INDEX(TEXT(D2:D32,"MMM-dd")&" "&I2:I32&" "&H2:H32&" ᵒF",MATCH(MAX(IF((D2:D32>=B1)*(D2:D32<=B2),H2:H32)),H2:H32,0))
***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER
Row\Col Entry Date Exit Date Jan-05 2012 42.8 ᵒF
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thank you everyone for your reply. I with a combo of FDibbins and AlKey.
Thanks! Again!
You're welcome and thank you for the feedback!
@ alkeyOriginally Posted by AlKey
Nice one but
i think there is a flaw
if the start date is 8 Jan 2015 and End Date is 9 Jan 2015 it will give 7 Jan 2015
=INDEX(TEXT(D2:D32,"MMM-dd")&" "&I2:I32&" "&H2:H32&" ᵒF",MATCH(MAX(IF((D2:D32>=B1)*(D2:D32<=B2),H2:H32)),IF((D2:D32>=B1)*(D2:D32<=B2),H2:H32),0))
Happy to help, thanks for the feedback
Nice catch, AlKey. I updated it with your code.
Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks