How can I multiply an array by a percentage

[lcorcoran]

Hi All,

I have this formula that works grand, but my boss has asked me to add a column to my forecast Table for probability percentage, and I need to multiply an array of numbers by a percentage by before I to a sumifs.

I have two formulas in one and I using a bit of VBA code to switch between the two formulas, before I do the Sumifs I’m using the Offset to get my array of numbers, I need to multiply each number in the array by the probability column and then do the Sumifs, for info I’m also doing this in power query which is feeding in to a pivot table on a different sheet, I'm doing this for a quick summary report at the top of my sheet/report. I haven’t been able to get it to multiply the array I keep getting an #Value! Error, any help would be much appreciated





=SWITCH(Settings!$AE$1,

TRUE,

LET(

totalHours, SUMIFS(OFFSET(Forecast[Total Hours], 0, SEQUENCE(1, ((U2-M2)/7)+2)),

Forecast[Employee Name], OFFSET(B3, 1, 6)#,

Forecast[Status], "<>On Hold",

Forecast[Status], "<>Lost"),

lookupValue, Q4#,



IF(totalHours = lookupValue,

"All Good",

IF(totalHours = 0,

"H",

totalHours - lookupValue

)

)

),

FALSE,

SUMIFS(OFFSET(Forecast[Total Hours], 0, SEQUENCE(1, ((U2-M2)/7)+2)),

Forecast[Employee Name], OFFSET(B3, 1, 6)#,

Forecast[Status], "<>On Hold",

Forecast[Status], "<>Lost"))

 

[Skyybot]

I need to multiply an array of numbers by a percentage

Do you mean that you need to multiply SUM of an array of numbers by a percentage? Or do you need to get the percentage of the SUM of the array of numbers?

 

[lcorcoran]

It’s complicated, we are bidding for number of projects and we have a employees planned out to work on the projects(Week 1, Week 2, Week 3 ect…), so I need to sum up the hours per employee each week, but if the probability of getting the project reduces to say 50%, and if the employee was planned to work Week 1 * 20hrs, Week 2 * 20hrs, Week 3 * 10hrs ect.. now they are Week 1 * 10hrs, Week 2 * 10hrs, Week 3 * 5hrs. And if project two goes to 25% we need to weight the hours by that percentage, so If we change the percentage on a project we need all the hours to change. I can and will be doing this in power query as well but I want to be able to do it with a formula, so that when the manager change the percentages they can see the changes without have to refresh the powerquery.

 

[Skyybot]

Something like this?

Book1
	A	B	C
1	Hours if 100% Guaranteed	Percent of Getting Job	New Hours Total
2	Job #1
3	90	25%	22
4	Job#2
5	20	50%	40
6	20
7	20
8	20
9	Job#3
10	10	80%	20
11	5
12	5
13	5
Sheet1

Cell Formulas
Range	Formula
C3	C3	=PercentHours(A3,B3)
C5,C10	C5	=PercentHours(A5:A8,B5)