Hello, I need to create a separate sheet that displays the top 4 deals in each state based on their contract value. I am not exactly how to go about this - but the data that this tab will be pulling from will constantly be added to.
I basically need to say, if state equals "Alaska" then display the row with highest contract value, the row with 2nd highest contract value, 3rd, 4th
Here is my example, thank you in advance
Try this array formulaArray formulas are confirmed with Ctrl+Shift+Enter, not just Enter.Formula:
Please Login or Register to view this content.
Please add reputation by clicking on the * if I have helped.
Please mark the thread SOLVED if your issue has been resolved.
Thanks, Glenn.
try below for contract value
copy paste below in E3 then hold control and shift together then hit enter to make it array formula
=IFERROR(LARGE(IF(Data!$D$10:$D$100=INDIRECT("A"&MAX(IF(MOD(ROW($A$1:A1)-1,4)+1=1,ROW($A$3:A3)))),Data!$F$10:$F$100),MOD(ROW(A1:A1)-1,4)+1),"")
change the red part to meet your original data range
then drag down
Last edited by hemesh; 08-04-2014 at 02:55 PM.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
A slight change. This will work better in cell E3Formula:Please Login or Register to view this content.
Neither of these are working for me. So to explain a little better, I have a spreadsheet with hundreds of potential contracts. They all have a date, status, description, value and URL. On a separate sheet, I want to have a table with all 50 states. I want this table to show the top 4 deals (highest contract values) so I was thinking some sort of lookup that returns multiple values
@Hemesh.. could you explain the meaning of this for me so I can try to follow?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks