Capacity division-based thing

[pippin]

Morning All,

Im having more of a mind block I think on doing this today. I'm calculating simple capacity ie we have

• 180 hours in a month
• Hours spent on each prority

Critical	2
High	4
Medium	6

• Expected amount per month

Critical	10
High	30
Medium	40

I want an output which would show how many tickets we could work on before running out of time

Critical	10 (ie 2*10 = 20)
High	30 (30*4=120 running total 140)
Medium	1 (1*40 = 40) running total 180)

Im using a fairly long if statement, and then adjusted down the priorities - there must be a simpler function !?

Thanks in advance, sorry for being slow on a monday morning!

 

[Georgiboy]

Do you need a running total or just a total at the bottom?

What is the version of Excel you are using?

For example you could just use a small lookup table:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Critical	10	2	20							Critical	10
3	High	30	4	120							High	30
4	Medium	40	1	40							Medium	40
5				180
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=VLOOKUP(A2,$K$2:$L$4,2,0)
D2:D4	D2	=B2*C2
D5	D5	=SUM(D2:D4)

 

[pippin]

Morning,

Excel for Mac, but can run a windows set up.

Probably didn't do a great job of explaining, just taken a screen shot of the example, with nested if statement.

Column I in the formula relates to number of hours expected to be spent at each level.

Current if statement works, but just feels clunky

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)