Finding percentage when a condition is met

[Adamlee]

Hi All,

I am looking to find the percentage of when something is predicted and comes true over when it only comes true.

I have attached an excel sheet which might help.

To make things slightly harder. i would like to have the percentage only calculated over a range which is varied by filters on other columns.

Thanks for your help,

Adam

[djapigo]

Hi Adam,

Your question and spreadsheet are kind of confusing.

Please let us know what cells do you need a percentage. What do you mean by percentage? What are the filters on the other columns?

Please specific on a sample answer... for example, what is the percentage of that pass vs. fail? The result that I want to see is...

It will help us better help you.

Thanks.

[Adamlee]

Hey Dj,

I actually posted the wrong excel graph. I hope this helps clarify.

Thanks,

Adam

[djapigo]

Hi Adam,

Thanks for the clarifiction.

Try this for Prediction = SUMPRODUCT((Table1[Pass/Fail]="Pass")*(Table1[Pass/Fail2]="Pass"))/COUNTIF(Table1[Pass/Fail2],"Pass")

And for False Prediction = SUMPRODUCT((Table1[Pass/Fail]="Pass")*(Table1[Pass/Fail2]="Fail"))/COUNTIF(Table1[Pass/Fail2],"Pass")

I think SUMPRODUCT works best for your question since you want to compare 2 columns. This is an array formula that compares whole arrays (range of data).

So, let’s break down for the Prediction:
(Table1[Pass/Fail]="Pass") is a condition that returns {FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}

[quick trick… highlight the condition, then press F9 – should temporarily show you want this computes, press ESC to return to the formula]

(Table1[Pass/Fail2]="Pass") is a condition that returns {FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE}

Now when we multiply these 2 conditions, the two produces {0;0;0;0;1;0;1}.

And since we are using SUMPRODUCT, we sum all the items in {0;0;0;0;1;0;1} and we get 2.

Lastly, the COUNTIF simply takes the L column and counts the number of “Pass”, and this is 4. I might be reading this denominator wrong. Please correct this if needed.

So, the whole PREDICTION formula = 2/4 = 50%

If this is not what you are looking for, please let me know.

Hope this helps.