Formula not working on testing

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

3 is tab "S7 - Product Risk Scenario"

That should have the same results as my control sheet

Many thanks

ASKER

To make it clearer, i moved my table from tab "S7 - Product Risk Scenario" to tab "control sheet" and you can see that the numbers are good, but the formula in my table in "S7 - Product Risk Scenario" should be doing the same thing and returning the same numbers

Thanks
Test-Formula.xlsx

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hi Barry,

It must be over 12 columns as when the data is fully populated there will be 12 months of data.

But why would that make a difference, if theres no data, it should not affect the calculation?

Thanks

It seems that your formula converts the blanks in CashFormula to 0's so it takes those into account. So we need to correct for that.

Also, your column B:B in Control Sheet doesn't correspond to the data in S7 sheet....

And you use Percentile.Inc in one sheet and Percentile.EXC in the other...

try:

=PERCENTILE.EXC(IF(ISNUMBER(MATCH(InputRange,{2},0)),IF(CashFormula="","",CashFormula)),D28)

....but the other columns are not blank, you have values in columns X and Y. Even if you delete the values in X and Y the array formula treats blanks as zeroes and so that distorts the Percentile Calculation - if you want to ignore blanks in the CashFormula range you need to amend the formula like this

=PERCENTILE.EXC(IF(InputRange=2,IF(CashFormula<>"",CashFormula)),'S7 - Product Risk Scenario'!D28)

That will work across a 12 column range, ignoring any blanks

regards, barry

..which is what I said too... :)

Sorry NB_VC, I didn't see your last reply when I posted....but I could say the same - I already pointed out the INC and EXC discrepancy in my earlier answer and that the 2 ranges were not equal!

regards, barry

Sorry Barry, please don't take it the wrong way.... I was just confirming that we both said the same thing to re-enforce that the reason for the problem....

Hey! No problem,

....and I've been sitting here all this time wondering what this calculation does

'S7 - Product Risk Scenario'!D28

The value in S7 minus another value......Doh! it's all the sheet name.........

regards, barry