Solved

Excel formula issues

Posted on 2013-11-21
12
194 Views
Last Modified: 2013-11-22
I'm having an issue with the attached excel file. I want to put a top 5 bid graph onto the dashboard, but the matrix I am using has a number of bids in different progressions. On the attached, the tab Matrix is a master table with all bids we have had com in in the last month (theoretical). I want to pull the top 5-10 bids from the categories "Initial RFP" and "Revised RFP" and exclude all other opportunities from the chart.

Can this be done?
Sales-Tracking-pipeline.xlsm
0
Comment
Question by:chrismark176
  • 8
  • 4
12 Comments
 
LVL 4

Expert Comment

by:yuppydu
ID: 39666749
Assuming you are considering Emily write this formula for highest:

{=+LARGE(IF(($B$2:$B$33)="initial rfp";$F$2:$F$33);1)}

and this for second highest

{=+LARGE(IF(($B$2:$B$33)="initial rfp";$F$2:$F$33);2)}

from lowest substitute LARGE with SMALL

Find the formulas at the bottom of Emily tab

You have to save the formulas as array Ctrl+Shift+Enter then you can copy them down.
To make it easier you can substitute the "1" and "2" with values in another column
Sales-Tracking-pipeline.xlsm
0
 

Author Comment

by:chrismark176
ID: 39666803
ok, I can make this work, but the value you posted in the formula is for just Initial RFP. How would I write it needing both "Initial RFP" AND "Revised RFP"? these 2 categories make up the open bids we have.

Further to that, how want the formulas to be on the MGR overview page in the top 10 sales.

Could you do it using the Matrix tab and MGR overview tab?

Thank you so much for your help so far!
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39666824
If I understand correctly you need:

Highest to lowest among initial and revised rfp, found on the MGR overview sheet and the ranking has to be on the MGR overview

Is it correct?
0
 

Author Comment

by:chrismark176
ID: 39666842
Ideally, I want to see the top 10 open bids (Defined by Initial RFP and Revised RFP in the category column) amongst all 3 sales reps. I want to see the estimated cost, the bid title and client name.

The Matrix tab is a table I created that combined all 3 sales reps' tabs into 1, so I have been working off of that.

Does that answer your question more clearly?

The MRG overview tab is where this information will be displayed.
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39666897
Ok, I think I got it. Just got home from work. Talk to you in a bit
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39667110
This is pretty complicated. Before putting more time could you check if this is what you are looking for?
If it is I'll do some clean-up.
Sales-Tracking-pipeline.xlsm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:chrismark176
ID: 39667191
Getting closer, and I'm thinking I'm asking for something that is too much now that I see all the formulas.

I would like to write a formula that says:

IF MATRIX!B20:B619 contains the value Initial RFP OR Revised RFP, then use that row, AND THEN find the largest 10 values of that selection (To be shown on the MGR Dashboard)

I went a head and filtered and sorted the Matrix table to show what I was hoping to accomplish and took a screen shot to help.

Ideally, I'd like the attached grid to be on the manager's dashboard so they can see in real time the top bids out there from my whole team. Worst case scenario is that it cannot be done.
Screen-shot-of-grid-desired.docx
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39667331
Here I lost you! If matrix contains either ... or... use what? I think that making an organized sorting is more efficient and clearer.
You have multiple values in the estimate column which are identical. Whatever search you do it is going to return the first value found. I have the feeling that what you are tring to obtain needs VBA coding, and not an easy one
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39668174
Hi Chris, yesterday it was pretty late (I'm GMT+1) I'll give it a try this morning. I have the feeling it could be beyond my knowledge.  I'm sure it can be done in a formula. Let you know...
0
 
LVL 4

Accepted Solution

by:
yuppydu earned 500 total points
ID: 39668442
If this is not it I think I'll pass :)
Anyway I have learned something useful. Good time investment.
Sales-Tracking-pipeline.xlsm
0
 

Author Closing Comment

by:chrismark176
ID: 39668975
This was probably the most challenging thing I have ever had to do in excel and was WAY beyond my knowledge base. Yuppydu was helpful beyond words. Thank you!
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39669105
Well Chris, between the two of us, and all the people in EE which will read this :), it was way beyond my knowledge base as well but... I am stubborn and curious.
Glad it helped
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now