Solved

Array formula result will not display

Posted on 2013-11-13
7
448 Views
Last Modified: 2013-11-14
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
Comment
Question by:Seamus2626
7 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 125 total points
ID: 39645851
Did you press Shift-ctrl-enter?

If not then
select the formula cell
press F2
press Shift-ctrl-enter
0
 

Author Comment

by:Seamus2626
ID: 39645862
I have ssaqibh,

Im not sure why its displaying as zero
0
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 250 total points
ID: 39645866
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Seamus2626
ID: 39645882
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
 
LVL 23

Accepted Solution

by:
NBVC earned 250 total points
ID: 39645909
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
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 125 total points
ID: 39645980
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
 

Author Closing Comment

by:Seamus2626
ID: 39647417
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now