[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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