• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 470
  • Last Modified:

Array formula result will not display

Hi.

 i hae this formula

=PERCENTILE.EXC(IF(OR(Testing=1,Testing=3,Testing=4,Testing=5,Testing=6),CashFormula),'S7 - Product Risk Scenario'!D28)

The result in the formula bar is 4,511,758,955

in the cell, 0 appears.

There is also the error message "Excel ran out of resources while attempting to calculate one or more formulas. As a result these formulas cannot be evaluated"

But if i was to put in a vlookup, it would return a result.

Can someone share some thoughts?
#
Thanks
0
Seamus2626
Asked:
Seamus2626
4 Solutions
 
Saqib Husain, SyedEngineerCommented:
Did you press Shift-ctrl-enter?

If not then
select the formula cell
press F2
press Shift-ctrl-enter
0
 
Seamus2626Author Commented:
I have ssaqibh,

Im not sure why its displaying as zero
0
 
NBVCCommented:
What's the format of the cell?

What do you mean by: "The result in the formula bar is 4,511,758,955".  Where exactly do you mean?  Is that the expected result being displayed?
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Seamus2626Author Commented:
If i go up to the formula bar and click Fx, the Function Arguments show me the results, and the result is valid and returns "4,511,758,955"

Thanks
0
 
NBVCCommented:
I guess the error message is "stopping" the calculation from displaying in the cell.

I just used your formula in a workbook I created and filled over 1000 rows in the CashFormula range, and had no issues.  I am using Excel 2013.  I also copied down a bit and had no errors.   So I am not sure how big your ranges are, how many formulas you have, and what other formulas are in your workbook, and if maybe they are volatile or not....
0
 
barry houdiniCommented:
OR function doesn't normally work as required in these sort of formulas

Assuming that Testing and CashFormula are ranges I don't think your formula will do what you want - OR only returns a single result (TRUE/FALSE) not an array of TRUE/FALSE values, so you'll either get all of CashFormula....or none of it, not specific rows as I think you want. Try this version

=PERCENTILE.EXC(IF(ISNUMBER(MATCH(Testing,{1,3,4,5,6},0)),CashFormula),'S7 - Product Risk Scenario'!D28)

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 
Seamus2626Author Commented:
Hey guys, it was a case of saving down my excel ss somewhere else, one of those excel things, all good now

thanks
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now