A weighted average based on cell values? 1

[Maillme]

Hi there,

I am struggling to come up with a formula to use within Excel 2010.

I would like to work out a weighted average based on the content of another cell... so, to try and illustrate, see image below:

I would like to know how to calculate:

- the % of employees who said yes
- the % of employees who said no

In some cases the n/a answers would need to be excluded (which would be a different calculation i assume). The trouble I am having is working out a function to use to perform this within Excel. I can (just about) calculate this manually on paper......

thanks,
neil

 

[SkipVought]

Hi,

Percent yes: #yes/(#yes+#no)

Also, at your company do you use EmployeeID values in mathematical formulas?

When is a NUMBER not a NUMBER? faq68-6659

 

[kjv1611]

As for your picture, some of us (including me) cannot see the picture while at work, so it'd be great if you could simply provide some sample/fake data to illustrate what you want... so put what your data looks like now, and then what you want the outcome to be.

If you have say 5 values that you want to average, for instance, you can use the AVERAGE formula:

=AVERAGE(F2:F13)

(No particular reasoning on the cells range given in example - just totally selected at random)

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Maillme]

Hi All,

thanks for your response, but I'm maybe not being very clear. Here I will try my best to illustrate:

I have countries in my columns, e.g.: UK, NL, Spain, Italy

Each country has a number of employees
Each country also answers a question, e.g. do you have a health and safety policy

So (and apologies as I can nto find a way to format this correctly)

UK, NL, Spain, Ital
Yes, No, No, Yes
1000, 1000, 500, 500

Total Number of employees is 3000


What I want to do is know is the weighted average of employees who say yes (as an example). So, it looks something like, if A1 is yes, take the employee number, move to the next yes, take this employee number, move to the next yes etc..... then take the total number of employees form the columns who have yes - and work out what percentage this is of the total number of employees.....

I hope this is a little bit more clear.

I woudl like to know how to create a formulat to do this in excel either on the yes/no's (text) or 1,0 (number).


many thanks for all help.

@skip, no, there will be no employee ID.

 

[kjv1611]

ok, sounds like you need to use the SUMIF function for the "yes" answers, and then divide that by the total. That shoudl get what you need. so...

[b]Country	        UK	NL	Spain	Ital	 Total	        % Healthcare[/b]
Healthcare	Yes	No	No	Yes		
Employees	 1,000 	 1,000 	 500 	 500 	 [blue]=SUM(B3:E3) 	=SUMIF(B2:E2,"Yes",B3:E3)/SUM(B3:E3)[/blue]

I used code tags to line things up easier. Still not perfect, but hopefully you get the idea.

"Country" is in cell A1, and the others would be relative from that.

Does that look like the general idea of what you're after? It sounds like it to me.

Note that SUMIF won't work if you have multiple criteria. For multiple critieria, you'll need either the SUMIFS function or an array formula. There may be yet another solution, but those are 2 I know I've used.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Maillme]

KJV,

That worked perfectly. thank you.....

now, the next step of my problem (I thought I would build up to it so as it would be easier to explain) - I need to somehow exclude some answers from the total weighted average..... So, it's all about weighting an average based on Yes, No, N/A

Where Yes / No are included in the weighted calculation (e.g. their employee number) and the N/A's are excluded. In the following example:

I need to ensure if counting the Yes, and working out % - I Can exclude N/A from the calculation altogether (so the employee number from this column is not included in the total which is used to work out the weighted average)

thanks again, very helpful as always this forum.
Neil

 

[SkipVought]

This assumes that this is only for ONE question.

You need a row for Country.

You need a row for the number or Yes responses for each country.

You need a row for the number of No responses for each country.

You need a row for the number of employees for each country. BTW, I believe that many would consider "employee number" and "number of employees" as two entirely different entities.

 

[kjv1611]

Yes, Mailme, as Skip pointed out, you really should use "Number of Employees" or something other than "Employee Number" as that is very often used to be an ID, for instance.

The only change you'd need to make tot he formula is on the bottom part of the division. Instead of a straight SUM, an easy way would be to use 2 SUMIF functions - one for "Yes", and one for "No", assuming that Yes, No, and N/A are the only possibilities. And I'm certain there are yet more ways to do that, but that's just the one I'd use. So you'd just add the 2 SUMIF functions on the bottom together, inside a set of parentheses.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Maillme]

hi All,

So my end result was: =SUMIF(B44,"Yes",B55)/(SUMIF(B44,"Yes",B55)+SUMIF(B44,"No",B55))

This works great... but, to further help me, would it be possible to adapt this formula to perform based on the value of another cell.

So, What I want to do is:

If Cell Q4 = "Employees" then perform this calculation:

=SUMIF(B44,"Yes",B55)/(SUMIF(B44,"Yes",B55)+SUMIF(B44,"No",B55))

But if Cell Q4 = "Revenue" then perform this calculation:

=SUMIF(B44,"Yes",B66)/(SUMIF(B44,"Yes",B66)+SUMIF(B44,"No",B66))



I'm sure I just need to some how nest my formulas within an IF statement, but I am just a bit unsure exactly how to construct it.

Many thanks for any help,
Neil

 

[SkipVought]

Will Q4 contain anything other than Employees or Revenue?

If just the two then...

=IF(Q4="Employees",PUT FIRST FORMULA HERE,PUT SECOND FORMULA HERE)

 

[Maillme]

Don't think so.

thanks, ill try that.

Neil

 

[Maillme]

worked great. My final formula looks like:

=IF(Q3="Employees",SUMIF(B33,"Yes",$B$18:$P$18)/(SUMIF(B33,"Yes",$B$18:$P$18)+SUMIF(B33,"No",$B$18:$P$18)),SUMIF(B33,"Yes",$B$19:$P$19)/(SUMIF(B33,"Yes",$B$19:$P$19)+SUMIF(B33,"No",$B$19:$P$19)))

This works out weighted % of Yes answers based on revenue and/or employees

thank you everyone.

 

[zelgar]

you can probably make your formula a little shorted with the following:

=IF(Q3="Employees",SUMIF(B33,"Yes",$B$18:$P$18)/SUM(SUMIFS(B33,$B$18:$P$18,{"Yes","No"})),SUMIF(B33,"Yes",$B$19:$P$19)/SUM(SUMIFS(B33,$B$19:$P$19,{"Yes","No"})))