Microsoft Excel 2010 - windows

I am having major trouble finding how to create a formula. I need to create an average percentage to define a "strike rate" of an attachment to a product. I need to create 2 and cannot work it out. The 2 I need to create are as follows:-

for column C i need to create a percentage where yes = 100 and no = 0 so that for example if I have 2 yes and 3 no it shows at 40% and likewise if yes has 3 and no has 2 it is 60%. to make it harder I also have an n/a in the column, which needs to be disregarded from the final result.

for column D I need exactly the same as above but where yes = 100, no = 0, 2 = 200, 3=300 and 4=400 to create a percentage

somebody PLEASE help. I need the formulae so I can copy and paste them into the spreadsheet as I have a deadline to get this done and it needs to be a complete working document.

(also if anybody can tell me how to make it so that the percentage value for C is above 50% it is green and below 50% it is red and for column D if the value is above 30% then it is green and if it's below it is red. Also if the word "yes" is selected in any relevant dropdown box it goes green and if the word "no" is selected it goes red would be greatly appreciated. I have blatently bitten off more than I can chew!)

Thanks in advance

For the first part of your question, try this: =COUNTIF(C:C,"=yes")/(COUNTA(C:C)-COUNTIF(C:C,"=n/a"))

This will divide the number of "yes" entries in Column C by [the total number of entries in Column C minus the number of "n/a" entries].

re: "

for column D I need exactly the same as above but where yes = 100, no = 0, 2 = 200, 3=300 and 4=400 to create a percentage"I'm confused. A percentage of what? Based on the results you requested for the first part of your question, you basically wanted the percentage of "yes" entries based on the total all the "yes" and "no" entries. (2 "yes" out of 5 entries is 40%)

For the second part, which of those values (yes, no, 2, 3, or 4) do you want to know the percentage of?

For the last part of your question, look up Conditional Formatting in the Excel help files. It will all be explained there.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

It's a little difficult to understand exactly what your looking for here, given the questons wording, but i'll make a couple of suggestions. Please note, i'm more into vba code and do less with formulas. Sometimes you can take a big equation and work with one small piece at a time, to make it easy. Excel has many columns so why not make use of those not being used? Lets use columns E & F or any other un-used columns.

formulas for Column E

=IF(D1="no",1,0) -> now copy this formula down for each column C value

formulas for Column F

=IF(D1="Yes",1,0) -. do the same with this formulaNow you can do simple sums, and divide by to get percentages:

=SUM(E1:E100)

use CountA to get a total count:

=COUNTA(D1:D100)

Thanks guys, after a bit of playing around I have worked out a way of doing it in a totally different manner, to answer the question above, I needed to do it for PC hardware sales with attachments such as cover etc to create a percentage of ones with attach vs ones without, Many thanks anyway guys

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History