Hi, I'm trying to create a bit of a spreadsheet whereby users can select a different consequence rating (ranging from critical to none) for a number of impacts, then the highest rated one is selected as an overall consequence rating. I have attached a copy of the spreadsheet and would appreciate any tips or advice.
Last edited by Ex0dus; 12-05-2011 at 09:25 PM. Reason: solved
pl see attached file.
Try this array formula in E2
Confirm with Ctrl+Shift+Enter not just Enter.=SUMPRODUCT(COUNTA(ImpactsList)-INDEX(MATCH(IF(B2:B6="","None",B2:B6),ImpactsList,0),,))
If you dont use Ctrl+Shift+Enter then the formula will return 0 until all cells in Column B ("Consequence") are assigned a value.
[EDIT]
I misread this thread due to site failures and could not check it until now.
Here is another suggestion
Confirm with Ctrl+Shift+Enter not just Enter.=INDEX(ImpactsList,MIN(MATCH(IF(B2:B6="","None",B2:B6),ImpactsList,0)),1)
Last edited by Marcol; 12-05-2011 at 05:33 AM. Reason: Changed attachment
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
That is perfect Marcol. Thank you for the assistance.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks