Solved

Excel: formula to determine # to meet goal

Posted on 2016-11-28
5
21 Views
Last Modified: 2016-11-28
Hi Experts,

I have a requirement to provide the amount tickets need to reach a goal (85%).  To get a better understanding I am doing this in excel but will end up being converted to Tableau's custom formula.  I would expect to see a result of 5 as it should be rounded to a whole number.  Any idea how I would go about writing this?

Here are the data inputs:

Vendor	Closed	Total tickets	Current %	Target %	Tickets need to hit Target
Joe's shop	22	31	70.97%	85.00%	?

Open in new window

See attached

Thanks
Tickets-needed.xlsx
0
Comment
Question by:Maliki Hassani
  • 2
  • 2
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 41904683
Assuming you mean, "how many existing tickets do I need to close to hit my target", try this:

=MAX(0,ROUNDUP(C2*E2,0)-B2)
1
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 41904689
Note that what I provide above is an Excel formula. I presume Tableau has similar functions that would allow you to do this
0
 

Author Comment

by:Maliki Hassani
ID: 41904690
Great... That works now let me see if I can code it in Tableau.
0
 

Author Closing Comment

by:Maliki Hassani
ID: 41904692
Thanks
0
 
LVL 6

Expert Comment

by:Mike in IT
ID: 41904699
You will need to use a formula like this:

=ROUNDUP((E2*C2)-B2,0)

Open in new window


I have attached a modified version of your spreadsheet.

For the example that you have in the spreadsheet the ROUND() number would actually be 4 since the result is 4.35. By using ROUNDUP() it will always round up to the next int.
Tickets-needed.xlsx
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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

920 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

15 Experts available now in Live!

Get 1:1 Help Now