CODEWITHSUNDEEP
arraysexcelindexingmatch
sqlbg
sqlbg

Reputation: 73

Ten Nearest Records

Currently, I am using the following formula to capture the Forecast Close Date (read as “Deal Date”) for the 10 most recent closed deals. 

{=LARGE(IF(All_ JV_Forecast__2[Stage]="Closed Affirmative - Contract Signed",All_ JV_Forecast__2[Forecast Close Date],""),{1;2;3;4;5;6;7;8;9;10})}

I would like to adapt this formula to capture the ten closest deals—i.e. the ten deals that have a Forecast Close Date nearest to today.

Note that some deals have Forecast Close Dates in the past. So I’m focused on the 10 that have Forecast Close Dates greater than today but are the nearest. And I need these ordered according to proximity to today.

I’m stumped here, any thoughts?

Upvotes: 1

Views: 26

Answers (1)

user4039065
user4039065

Reputation:

The AGGREGATE function should be able to handle this.

=AGGREGATE(15, 6, ajvf2[stage]/(ajvf2[stage]>B$1), ROW(1:1))

I've renamed your table to ajvf2 to reduce obfuscation.

    minif_aggregate+dates

Upvotes: 2

Related Questions