Array function? Or Perhaps Sumproduct??

loosenut

New Member
Joined
Feb 20, 2004
Messages
41
Let's talk oil rigs. I've got a stumper here. I'm trying to create two columns which for each unique rigname that shows 1) the Day Rate furthest in the future and 2) the current Day Rate. For instance the Deepwater Discovery has a furthest in the future day rate of 364,000 per day and a current day rate of 201,800. These would be populated in columns AI and AJ. Apologies for the bad html format - ignore the @nbsp (it signifies a space).

Thanks! Loosenut :huh:
Regional Rig Charts 11.7.05.xls
AEAFAGAH
2Rig NameDay RateActivity Start DateEnd Date
3Belford Dolphin420,0001/18/20071/17/2010
4Belford Dolphin-11/29/20061/18/2007
5Belford Dolphin176,2568/28/200411/29/2006
6Deepwater Discovery-7/31/20089/31/2008
7Deepwater Discovery364,0008/1/20067/31/2008
8Deepwater Discovery285,00011/4/200512/18/2005
9Deepwater Discovery201,8009/10/200511/4/2005
10Deepwater Discovery150,0007/10/20059/10/2005
11Deepwater Expedition-9/1/20089/2/2008
12Deepwater Expedition-9/3/20069/1/2008
13Deepwater Expedition-8/2/20069/3/2006
14Deepwater Expedition320,0006/23/20068/2/2006
15Deepwater Expedition-6/18/20066/23/2006
16Deepwater Expedition240,0002/3/20066/18/2006
17Deepwater Expedition-12/31/20052/3/2006
18Deepwater Expedition-11/15/200511/29/2005
Fleet List Input UDW


Edited by Von Pookie
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi,

Try:

AJ2: =INDEX(AE3:AE18,MATCH(1,($AD$3:$AD$18=AI3)*(AE3:AE18>0),0))

AK2: =MAX(($AD$3:$AD$18=AI3)*($AF$3:$AF$18<=TODAY())*($AG$3:$AG$18>=TODAY())*$AE$3:$AE$18)

Both confirmed with Ctrl + shift + enter before being dragged down.
Book1
ADAEAFAGAHAIAJAK
1
2Rig NameDay RateActivity Start DateEnd Datefurthest in the future day ratecurrent day rate
3Belford Dolphin4200002007-01-182010-01-17Belford Dolphin420000176256
4Belford Dolphin2006-11-022007-01-18Deepwater Discovery364000285000
5Belford Dolphin1762562004-08-282006-11-02Deepwater Expedition3200000
6Deepwater Discovery2008-07-312008-10-01
7Deepwater Discovery3640002006-08-012008-07-31
8Deepwater Discovery2850002005-11-042005-12-01
9Deepwater Discovery2018002005-09-102005-11-04
10Deepwater Discovery1500002005-07-102005-09-10
11Deepwater Expedition2008-09-012008-09-02
12Deepwater Expedition2006-09-032008-09-01
13Deepwater Expedition2006-08-022006-09-03
14Deepwater Expedition3200002006-06-232006-08-02
15Deepwater Expedition2006-06-182006-06-23
16Deepwater Expedition2400002006-02-032006-06-18
17Deepwater Expedition2005-12-032006-02-03
18Deepwater Expedition2005-11-012005-11-02
Sheet1
 
Upvote 0
Thanks - Almost there!

Fairwinds - Thanks for your thoughts they work great with one exception. The second formula that includes MAX is limited to a list of 30 rows and thus reports "value" when I try to expand it to the full selection (about 1700 lines of data). Any thoughts on how I can rewrite this to make it work?

Thanks!! :-?

Loosenut

P.S. Love the picture of the boat. I was in Oslo & Stockholm early this year and at somepoint would love to sail the archipelago.....
 
Upvote 0
Re: Thanks - Almost there!

loosenut said:
Fairwinds - Thanks for your thoughts they work great with one exception. The second formula that includes MAX is limited to a list of 30 rows and thus reports "value" when I try to expand it to the full selection (about 1700 lines of data). Any thoughts on how I can rewrite this to make it work?

Thanks!! :-?

What's the formula look like exactly? How many rows of data do you have?
 
Upvote 0
Re: Thanks - Almost there!

loosenut said:
The second formula that includes MAX is limited to a list of 30 rows and thus reports "value" when I try to expand it to the full selection ...


Not sure what you mean.

=MAX(($AD$3:$AD$1800=AI3)*($AF$3:$AF$1800<=TODAY())*($AG$3:$AG$1800>=TODAY())*$AE$3:$AE$1800)

Works for me for 1800 lines however the formula might slow down your sheet.
 
Upvote 0
Success

It worked - my fault. What had happened was that I had a row with text headings halfway through the data - understandably, excel didn't know how to compute max for text. I suppose that's why they call me 'loosenut'.

Tak!

Loosenut :-D
 
Upvote 0

Forum statistics

Threads
1,223,498
Messages
6,172,645
Members
452,467
Latest member
colelkay

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