How to calculate percentage if we have different rows for same resource

[Davesh Garg]

Hi,

Can someone please help in identifying the percentage formula. Table is below:

1st Criteria
Here if we are taking out the percentage of different resources then they are the sum of their unique (Actuals / NAH)
For Eg: If we require the percentage, then it should be the sum of
A. Actual of resource "A"+Actual of resource "B" + Actual of resource "C" + Actual of resource "D" + Actual of resource "E" = 32
B. NAH of resource "A" + NAH of resource "B" + NAH of resource "C" + NAH of resource "D" + NAH of resource "E" = 52

Percentage = A / B = 32/52 = 61.54%

And if we add up the columns then our percentage is not same. It is Sum of Actuals (61) / Sum of NAH (108) = 56.48%

2nd Criteria

If we need to calculate the percentage for VP whose value is X, then the correct percentage for unique resource is =32 / 52 = 61.54%
however, if we add the rows whose VP's value is X, then our calc is showing = 42/71 = 59.15%

Request if anyone can provide a formula which can be evaluated as per the criteria selected in column G2:I2 from data A1:E12

Let me know if anyone require any other information.

Thanks in advance.

	A	B	C	D	E		G	H	I	J
1	VP	Client	Name of resource	Actual	NAH		VP	Client	Name of resource	%
2	X	1	A	6	9		X			Formula
3	Y	2	A	6	9
4	X	3	A	6	9
5	X	1	B	5	8
6	Y	2	B	5	8
7	X	3	C	4	10
8	Z	4	C	4	10
9	X	5	C	4	10
10	Y	6	C	4	10
11	X	3	D	7	10
12	X	1	E	10	15

<tbody>
</tbody>

 

[oldbrewer]

what is NAH and VP ?

if fred scores 80% and nancy scores 90% and fred got 8 out of 10 right and nancy got 18 out of 20 right then the total rights = 26 and total questions = 30
then average rights is 86.67 NOT 85 UNLESS you choose to average percentages as they stand...

It is not clear to me what you want.

 

[Davesh Garg]

Hi,

Thanks for looking into this. For your questions above, NAH stands for Net Available Hours and we require to calculate: Utilization = Actual / NAH

However, one resource can touch different clients and these clients can be owned by different VP's. So in order to calculate the correct utilization of all the unique resources we require some formula so that what we choose from column G2:I2, Utilization will be calculated as per the above formula for unique resources.

Let me know if you require any further information.

Thanks,
Davesh

 

[Davesh Garg]

Hi,

Can anyone please help on this, as I am still stucking in excel and not able to complete my work due to this. I don't want macro, what i require to suffice my result as per the criteria with the help of formula.

Thanks for the help.

Regards,
Dave

 

[oldbrewer]

I suspect you are not getting replies because it is still unclear what you want. Having said that comment on this

VP	Client	Name of resource	Actual	NAH		VP	Client	Name of resource	%
X	1	A	6	9		X			Formula
Y	2	A	6	9
X	3	A	6	9
X	1	B	5	8
Y	2	B	5	8
X	3	C	4	10
Z	4	C	4	10
X	5	C	4	10
Y	6	C	4	10
X	3	D	7	10
X	1	E	10	15
				select a VP	X
				select a client	1
				select name of resource	B
				percentage utilisation
				62.5
				formula
	=100*(SUMPRODUCT((A2:A12=$G$15)*(B2:B12=$G$16)*(C2:C12=$G$17)*(D2:D12))/SUMPRODUCT((A2:A12=$G$15)*(B2:B12=$G$16)*(C2:C12=$G$17)*(E2:E12)))
		you have 3 variables - vp, client, resource so you need more than one table to analyse for all 3

<colgroup><col span="2"><col><col span="2"><col><col span="10"></colgroup><tbody>
</tbody>

 

[Davesh Garg]

Hi Sir,

Thanks for the formula. This certainly helps me to find out the correct utilization, in case I am updating all the criteria vp, client, resource.

Now my requirement is like if I am not updating any criteria, then also my formula should work. Like, suppose i am not updating resource and client, in this case the formula should work for the given requirements.

For eg:

Above VP: X
Client : (blank)
Resource: (blank)

Then all the Actual and NAH for unique resources whose VP is X should be calculated.

Thanks for the support once again.

Regards,
Dave

 

[oldbrewer]

I think I have a better idea will post in 10 minutes

 

[oldbrewer]

VP	Client	Name of resource	Actual	NAH
X	1	A	6	9
Y	2	A	6	9
X	3	A	6	9
X	1	B	5	8
Y	2	B	5	8
X	3	C	4	10			VP	X
Z	4	C	4	10
X	5	C	4	10			Sum of Actual	Name of resource
Y	6	C	4	10			Client	A	B	C	D	E	Grand Total
X	3	D	7	10			1	6	5			10	21
X	1	E	10	15			3	6		4	7		17
							5			4			4
							Grand Total	12	5	8	7	10	42
						by using a pivot table you can do the analysis by VP
						I have selected VP "X"
						then for every combination of client and resource name
						the above chart gives actual hours
						easy to put in NAH as well
						then use a calculated field to do percentages
						ALTERNATIVELY
						have a different sheet for each VP
						and use sumproduct approach

<colgroup><col span="2"><col><col span="4"><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Davesh Garg]

Hello Sir,

I can create the pivot. I know that we can also update the same by creating the Pivot. However, in my excel sheet i want to use some formula for this.

This formula can update my graph value, otherwise, i need to use macro for updating the pivot every-time i require the data as per the selected criteria.

Is there any formula that can be driven and after using it, we can update the utilization value for every unique resource.

Thanks,
Dave

 

[oldbrewer]

What do you want to plot on your graph ?

I think you need a separate table for each VP, and using sumproduct approach means the tables AUTOMATICALLY update whenever new data is added. Charts of course can be created by using pivot charts.